Database transactions and concurrency control form the foundation of data integrity and consistency in multi-user environments. Transactions ensure that a series of database operations either complete successfully as a single unit (commit) or fail entirely (rollback), while concurrency control mechanisms prevent conflicts when multiple transactions access shared data simultaneously. Understanding isolation levels, locking strategies, and anomaly prevention is essential for building reliable, high-performance database applications—particularly when balancing the trade-off between strict consistency and system throughput.
What This Cheat Sheet Covers
This topic spans 15 focused tables and 75 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: ACID Properties
ACID is the four-part contract that distinguishes a real transaction from a mere batch of statements. Atomicity makes the whole unit all-or-nothing, Consistency keeps every constraint satisfied across the change, Isolation hides one transaction's half-finished work from others, and Durability guarantees a committed result survives a crash. Almost every concept later in this sheet exists to uphold one of these four guarantees, so they're worth internalizing first.
| Property | Example | Description |
|---|---|---|
BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2;COMMIT; | • All operations complete successfully or none take effect • if any operation fails, the entire transaction rolls back to its starting state | |
Database constraint: balance >= 0Transaction ensures constraint holds before and after execution | • Transactions transition the database from one valid state to another • all integrity constraints (primary keys, foreign keys, check constraints) remain satisfied |