1. Statement families #
Read, write, define, commit.
Overview #
| Family | Statements | Effect on data |
|---|---|---|
| Read | SELECT | none: returns rows |
| Modification (DML) | INSERT, UPDATE, DELETE, MERGE | adds, changes or deletes rows |
| Stored procedure | CALL, EXEC | depends on its code: can read and write |
| Schema definition (DDL) | CREATE, ALTER, DROP | changes the structure (tables, indexes) |
| Transaction control | BEGIN, COMMIT, ROLLBACK | commits or cancels a group of operations |
2. Reads and modifications #
SELECT and DML.
Reads #
SELECT returns rows without changing the table. Its performance mainly depends on indexes, the volume read and the number of calls: a hundred identical small queries on one page (the N+1 problem) often cost more than one well-written query.
Modifications #
INSERT adds rows, UPDATE changes them, DELETE removes them and MERGE combines insert and update based on a matching condition. These statements take locks and write to the transaction log: they are sensitive to contention.
3. Stored procedures #
Code in the database.
What they do #
A stored procedure is a program stored in the database and called with CALL or EXEC. It can contain SELECT statements, but also INSERT, UPDATE or DELETE, loops and conditions.
Common mistake: Assuming a stored procedure only reads: many procedures change data. On the application side you only see the call; its code tells you what it does.
4. Transactions #
All or nothing.
ACID properties #
A transaction groups several statements into one unit: atomicity (all or nothing), consistency (constraints stay satisfied), isolation (concurrent transactions do not interfere, depending on the isolation level) and durability (once committed, the change survives a failure).
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK; to cancel everythingGood practice: Keep transactions short: a long transaction holds its locks and blocks others. Conversely, committing each row separately multiplies COMMIT calls and slows down bulk processing.How Dynatrace classifies and measures these statements: Database statements in Dynatrace.
