Data Modification Transactions

Definition

Wrapping DML statements (INSERT/UPDATE/DELETE) in an explicit transaction ensures a group of related changes succeed or fail TOGETHER — this ties directly back to TCL (Chapter 8.14: BEGIN/COMMIT/ROLLBACK), applied specifically to real-world data-modification scenarios.

How It Works

sql
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;
  UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;

If the second UPDATE failed for any reason (a constraint violation, a crash) after the first succeeded, without a transaction the first UPDATE would already be permanently applied — money debited from account 1 with nothing credited to account 2. Wrapping both in one transaction guarantees they succeed or fail as a single unit: COMMIT only happens if both succeeded; otherwise the whole thing can be ROLLBACK-ed, leaving neither change applied.

Edge Cases and Pitfalls

• Any multi-statement DML sequence where the statements depend on each other for correctness (archive-then-delete from 11.3, transfer-between-accounts above, insert-parent-then-insert-children) should be wrapped in a transaction — treating each statement as independently "fine on its own" ignores the real risk of a partial failure between them.
• Most database clients/drivers default to auto-commit mode, where each individual statement commits on its own — an explicit BEGIN/START TRANSACTION is what turns several statements into one atomic unit instead of independent ones (as covered in 8.14).
• A transaction that's opened but left uncommitted for a long time (while application code does slow, unrelated work in between statements) can hold locks and block other users' work — good practice is to keep transactions as SHORT as possible: open it right before the related statements, commit immediately after, and avoid doing slow unrelated work (like a network call) in the middle.

Key Takeaways

• Wrapping related INSERT/UPDATE/DELETE statements in a transaction guarantees they succeed or fail together — critical for any multi-statement change where partial completion would be wrong.
• Auto-commit is the default in most clients; explicit BEGIN/COMMIT is what creates a genuine multi-statement atomic unit.
• Keep transactions short — a long-held open transaction can block other work via held locks.