Transactions & Concurrency
Application Concurrency and Transaction Boundaries
Applications preserve invariants by combining short database transactions with atomic DML, pessimistic locks or optimistic version checks. Savepoints scope partial database rollback; idempotency and outbox-style boundaries address retries and remote effects, while 2PC has availability and operability costs.
- Optimistic checks turn stale writes into zero-row outcomes.
- Pessimistic locks protect a decision window.
- Savepoints are partial database rollback markers.
- Remote calls do not belong inside ordinary DB transactions.
- Idempotency keys name one logical command.
- 2PC coordinates atomic commit but can block and amplify failure.
UPDATE inventory
SET reserved = reserved + :qty, version = version + 1
WHERE product_id = :id
AND version = :seen_version
AND on_hand - reserved >= :qty;
-- row_count = 1: committed candidate; 0: stale version or insufficient stock—reread.Start: product 42 on_hand=10, reserved=0, version=7; invariant reserved<=on_hand.
T1 and T2 read version 7. T1 reserves 6: update succeeds, version=8.
T2 tries to reserve 6 with version=7: row_count=0.
T2 rereads available=4 and rejects; invariant preserved.| Tool | Protects | Does not provide |
|---|---|---|
| Savepoint | Partial rollback within one transaction | Independent durability or remote undo |
| Version column | One object from stale overwrite | Cross-row predicate serialization |
| Idempotency key | Duplicate logical command | Isolation from concurrent different commands |
| Outbox | Atomic business state + message intent | Instant delivery or exactly-once consumer effect |
| 2PC/XA | Atomic decision across enlisted participants | Nonblocking operation or simple recovery |