Transactions and Isolation

A transaction groups operations under an atomic commit or rollback decision. ACID is a collection of goals and mechanisms, not a promise that application logic cannot have races.

ACID

• Atomicity: effects commit together or not at all.
• Consistency: a correct transaction preserves declared invariants.
• Isolation: concurrent execution is constrained to an advertised model.
• Durability: committed effects survive the failures covered by the system's guarantee.

Anomalies

• dirty read: observe another transaction's uncommitted data;
• non-repeatable read: a repeated item read returns changed committed data;
• phantom: a repeated predicate query returns a changed set;
• lost update: one update overwrites another without detecting it;
• write skew: transactions read overlapping state and write different items, jointly violating an invariant.

Isolation-level names do not fully describe every database's behavior. Study the vendor's actual concurrency-control model and anomaly guarantees.

Optimistic and pessimistic control

Optimistic version checking detects conflicting updates at commit/update time and works well when conflicts are rare. Pessimistic locking restricts concurrent access earlier and can increase waiting or deadlock risk. Every retry must rerun the complete transaction decision from fresh state and obey a bounded retry and deadline policy.

Boundaries

A local database transaction does not atomically include an ordinary HTTP call or message broker operation. Use patterns such as the transactional outbox, idempotent consumers, sagas, and reconciliation according to failure semantics.

Exercises

1. Construct a write-skew schedule involving two rows.
2. Explain why SELECT followed by UPDATE can race.
3. Compare optimistic conflicts with deadlock retries.