Database Transactions
debt(d5/e3/b5/t5)
Closest to 'specialist tool catches' (d5). The detection_hints specify semgrep and phpstan can detect 'multiple related DB writes without PDO transaction wrapping' and 'no rollback in catch block'. These are specialist static analysis tools, not default linters, and the pattern requires semantic understanding of related writes.
Closest to 'simple parameterised fix' (e3). The quick_fix describes a clear pattern: wrap in try/catch with beginTransaction, rollback in catch, commit after success. This is a small refactor within one component — adding transaction boundaries around existing code — but may touch multiple methods/files if operations are spread across service classes.
Closest to 'persistent productivity tax' (b5). Transactions apply across web, cli, and queue-worker contexts per applies_to. Once a codebase establishes transaction patterns (or fails to), it affects every data-modifying operation. Poor transaction hygiene compounds: lock contention, deadlocks, timeout cascades ripple through the system. Not architecture-defining, but a persistent concern in every DB operation.
Closest to 'notable trap' (t5). The misconception explicitly states developers believe 'wrapping everything in a transaction guarantees data integrity' when in fact long transactions cause locks, deadlocks, and timeout cascades. This is a documented gotcha — transactions should be short, business logic outside. Most experienced PHP devs eventually learn this the hard way through production lock contention.
Also Known As
TL;DR
Explanation
A transaction wraps multiple SQL statements so they either all commit or all roll back on error. In PHP with PDO: $pdo->beginTransaction(); ... $pdo->commit(); with $pdo->rollBack() in a catch block. Key behaviours: implicit vs explicit transactions (autocommit mode), savepoints for nested rollback to a known point, and isolation levels controlling visibility of concurrent changes. Transaction scope should be as short as possible — open transactions hold locks. Avoid transactions spanning HTTP requests (leave a transaction open between user steps). Doctrine's EntityManager::flush() wraps all pending changes in one transaction. Eloquent's DB::transaction(fn) automatically retries on deadlock up to a configurable count.
Diagram
sequenceDiagram
participant APP as Application
participant DB as Database
APP->>DB: BEGIN TRANSACTION
APP->>DB: INSERT INTO orders values
APP->>DB: UPDATE inventory SET stock=stock-1
APP->>DB: INSERT INTO payments values
Note over APP,DB: All or nothing
APP->>DB: COMMIT
DB-->>APP: Success - all 3 rows written
Note over APP,DB: On failure
APP->>DB: BEGIN
APP->>DB: INSERT order
APP->>DB: UPDATE inventory - fails!
APP->>DB: ROLLBACK
DB-->>APP: Nothing written - consistent state
Common Misconception
Why It Matters
Common Mistakes
- Multi-step operations not wrapped in a transaction — a crash between steps corrupts data.
- Transactions that span user interactions (long transactions) — lock contention degrades performance.
- Catching exceptions inside a transaction and continuing instead of rolling back.
- Not using transactions for read-modify-write sequences — race conditions occur without locking.
Avoid When
- Long-running transactions that hold locks for seconds — other queries queue behind and throughput drops.
- Transactions spanning multiple microservices — use saga patterns or two-phase commit instead.
- Read-only operations that need no consistency guarantees — transactions add overhead with no benefit.
- Transactions containing external HTTP calls — the network call can hang while locks are held indefinitely.
When To Use
- Any operation that modifies multiple rows or tables that must succeed or fail together.
- Financial operations — debiting one account and crediting another must be atomic.
- Preventing partial writes that leave data in an inconsistent state.
- Operations that check a condition and act on it — wrap the check and write together to prevent TOCTOU races.
Code Examples
// No transaction — if second query fails, first already committed
$pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
$pdo->exec('UPDATE accounts SET balance = balance + 100 WHERE id = 2');try {
$pdo->beginTransaction();
$pdo->exec('UPDATE accounts SET balance = balance - 100 WHERE id = 1');
$pdo->exec('UPDATE accounts SET balance = balance + 100 WHERE id = 2');
$pdo->commit();
} catch (\Throwable $e) {
$pdo->rollBack();
throw $e;
}