Database Transactions and Concurrency Control Cheat Sheet

Updated 2026-05-15

🧠Study flashcards on this topic71 cards · spaced repetition→

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, 71 flashcards. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: ACID PropertiesTable 2: Transaction Isolation LevelsTable 3: Concurrency AnomaliesTable 4: Multi-Version Concurrency Control (MVCC)Table 5: Two-Phase Locking (2PL) MechanismsTable 6: Two-Phase Locking VariantsTable 7: Deadlock Detection and PreventionTable 8: Optimistic vs Pessimistic Concurrency ControlTable 9: PostgreSQL-Specific Concurrency FeaturesTable 10: Explicit Locking ClausesTable 11: Distributed Transaction ProtocolsTable 12: Transaction Control CommandsTable 13: Lock Escalation and GranularityTable 14: Isolation Level Implementation DifferencesTable 15: Best Practices and Performance Considerations

Quick IndexSubscribe to unlock

A jump-to index of every table row in this cheat sheet.

Mind MapSubscribe to unlock

An interactive map of every table and concept in this topic.

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
Atomicity	`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
Consistency	Database constraint: `balance >= 0` Transaction 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
Isolation	Two concurrent transactions updating different rows do not interfere with each other's intermediate states	• Concurrent transactions execute as if they were serialized • each transaction's intermediate changes remain invisible to others until commit
Durability	After `COMMIT`, data survives system crash and is written to persistent storage (disk/WAL)	Once committed, changes are permanently stored and survive system failures, power outages, or crashes through write-ahead logging

Table 2: Transaction Isolation Levels

The four standard isolation levels form a ladder trading throughput for correctness: each step up blocks one more class of anomaly but costs more in contention or aborts. READ UNCOMMITTED is the permissive floor, SERIALIZABLE the strict ceiling that behaves as if transactions ran one at a time, and the two middle levels are where most real databases default. Note the defaults differ by engine—PostgreSQL ships READ COMMITTED, MySQL ships REPEATABLE READ—which is a frequent source of surprise when porting code.

Level	Example	Description
READ UNCOMMITTED	`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;` `SELECT * FROM orders;`	• Weakest isolation • allows dirty reads (reading uncommitted data from other transactions) • rarely used in production due to data integrity risks

The rest of this section is part of the full cheat sheet

Table 3: Concurrency Anomalies

These are the specific things that go wrong when transactions overlap, and they're the reason isolation levels exist in the first place. They escalate in subtlety from dirty reads (seeing data that was never committed) up through lost updates and write skew, where two transactions each behave correctly in isolation yet together corrupt the data. Knowing which anomaly a given isolation level still permits is the practical key to choosing the right level.

Anomaly	Example	Description
Dirty Read	T1 updates row A; T2 reads row A before T1 commits; T1 rolls back	• Reading uncommitted changes from another transaction • if the writing transaction aborts, the read data never actually existed in the database

The rest of this section is part of the full cheat sheet

Table 4: Multi-Version Concurrency Control (MVCC)

MVCC is the trick that lets modern databases avoid making readers and writers wait on each other: instead of locking a row, the engine keeps multiple versions of it and shows each transaction the version valid for its own snapshot. The rows here unpack how that works in PostgreSQL—snapshots, per-row version tags, monotonic transaction IDs—and the maintenance cost it incurs. That last point, VACUUM, is the part teams most often neglect, since dead row versions and XID wraparound will eventually bite a busy database.

Concept	Example	Description
Snapshot Isolation	Each transaction sees a consistent snapshot of data as it existed at transaction start	• Transactions read from a consistent point-in-time snapshot • readers never block writers and writers never block readers • implemented via row versioning

The rest of this section is part of the full cheat sheet

Table 1: ACID Properties

Property	Example	Description
Atomicity	`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
Consistency	Database constraint: `balance >= 0` Transaction 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
Isolation	Two concurrent transactions updating different rows do not interfere with each other's intermediate states	• Concurrent transactions execute as if they were serialized • each transaction's intermediate changes remain invisible to others until commit
Durability	After `COMMIT`, data survives system crash and is written to persistent storage (disk/WAL)	Once committed, changes are permanently stored and survive system failures, power outages, or crashes through write-ahead logging

Table 2: Transaction Isolation Levels

Level	Example	Description
READ UNCOMMITTED	`SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;` `SELECT * FROM orders;`	• Weakest isolation • allows dirty reads (reading uncommitted data from other transactions) • rarely used in production due to data integrity risks

Table 3: Concurrency Anomalies

Anomaly	Example	Description
Dirty Read	T1 updates row A; T2 reads row A before T1 commits; T1 rolls back	• Reading uncommitted changes from another transaction • if the writing transaction aborts, the read data never actually existed in the database

Table 4: Multi-Version Concurrency Control (MVCC)

Concept	Example	Description
Snapshot Isolation	Each transaction sees a consistent snapshot of data as it existed at transaction start	• Transactions read from a consistent point-in-time snapshot • readers never block writers and writers never block readers • implemented via row versioning