Database Transactions

When to use

• The user hits InnoDB deadlocks or deadlock errors under load, lost updates, or inconsistent reads, and wants to handle and retry them when multiple requests write the same rows concurrently.
• The user needs to pick or reason about an isolation level (READ COMMITTED, REPEATABLE READ, SERIALIZABLE), use a savepoint for partial rollback, or fix a phantom/non-repeatable read.
• The user performs multiple related writes (transfer funds, create order plus line items) that must commit together or not at all.

When NOT to use

• Do not assume transactional semantics on MyISAM tables, DDL statements, or stores without ACID support — BEGIN/ROLLBACK will silently do nothing there.
• Do not wrap one standalone INSERT/UPDATE in BEGIN/COMMIT — a single statement is already atomic under autocommit.

Core guidance

• Keep transactions short and do no I/O inside them — Open the transaction as late as possible, do only the required DB writes, and commit immediately. Never make HTTP calls, send email, or sleep while a transaction holds locks.
• Retry deadlock victims with backoff, do not swallow them — Catch the deadlock/lock-wait error (MySQL 1213/1205), roll back, and retry the whole transaction a bounded number of times with small backoff. Only give up after the retry budget is exhausted.
• Guarantee rollback on every failure path — Run the transaction inside try/catch (or a framework closure): commit on success, roll back in the catch, and rethrow. Never leave a path where an exception escapes with the transaction still open.

Quick recipe

1. Start the transaction, then SELECT ... FOR UPDATE the exact rows you will change so concurrent writers block instead of racing. (verify: A second connection's write to the same rows waits until this transaction commits.)
2. Perform the DML, then commit. Wrap the block in try/catch so any exception triggers a rollback and rethrow, leaving no open transaction behind. (verify: On a thrown error the row values are unchanged and no locks remain held.)
3. Enclose steps 1–2 in a retry loop that catches deadlock/lock-wait errors, rolls back, backs off briefly, and retries up to a small fixed limit before failing. (verify: A forced deadlock is retried and eventually succeeds, or fails cleanly after the retry budget.)

Best practices

• When a transaction touches several rows or tables, always lock them in the same deterministic order (for example, ascending primary key) across all code paths
• Default to READ COMMITTED or REPEATABLE READ for OLTP; raise to SERIALIZABLE only for invariants that must never see phantoms, and use `SELECT ..
• Wrap an optional sub-step in `SAVEPOINT sp; ..

Anti-patterns

See references/anti-patterns.md.

Common mistakes

1. DDL inside a transaction triggers an implicit commit — Running a CREATE TABLE, ALTER TABLE, or TRUNCATE inside what looks like a transaction silently commits everything before it, so a later ROLLBACK cannot undo the earlier writes. Fix: Keep schema changes out of data transactions; run migrations separately, and never mix DDL with the DML you intend to roll back.
2. External calls inside a transaction hold locks for seconds — Making an HTTP request, calling a payment API, or dispatching a job while a transaction is open keeps its row locks held for the entire network round-trip. Fix: Do the DB writes and commit first, then perform external work; if the external result must persist, use an outbox row committed in the same transaction and processed afterward.
3. A second BEGIN silently commits the first transaction — Issuing START TRANSACTION while one is already open commits the outer transaction instead of nesting, so the two blocks are not atomic together. Fix: Use SAVEPOINT for inner units of work, or rely on a framework's transaction helper that tracks depth and emits savepoints instead of raw BEGIN.
4. Catching a deadlock and continuing corrupts state — Catching a deadlock exception and proceeding as if the write succeeded leaves the transaction already rolled back by the engine, so later statements run outside any transaction. Fix: On a deadlock, roll back explicitly, then retry the whole unit of work from the start with backoff, or fail loudly after the retry budget.

Version notes

• MySQL 8 adds atomic DDL, but DML still cannot span it (PHP >=7.4) — MySQL 8.0 makes individual DDL statements crash-safe (atomic DDL), but DDL still causes an implicit commit of your open transaction; the InnoDB default isolation level remains REPEATABLE READ across 5.7 and 8.0.

Security

• Guard money-moving transactions against replay — For financial or state-changing operations, lock the row (FOR UPDATE) and enforce an idempotency key or a unique constraint inside the same transaction so a retried or replayed request cannot double-apply.

Testing

• Activation and decision behavior are verified in eval/scenarios/databases/transactions.yaml.

Additional resources

• references/anti-patterns.md
• references/core.md
• references/examples.md
• references/pitfalls.md
• references/recipes.md
• references/version-differences.md

Knowledge graph

Related: databases/connection-pooling, databases/mysql-indexing