Concurrency Control

How databases let many transactions run at once without corrupting each other — locks vs MVCC

Why

Databases serve thousands of concurrent transactions. Without concurrency control, two transactions reading and writing the same row simultaneously corrupt data. The mechanism the database uses — locking or MVCC — determines how much contention your application experiences, whether reads block writes, and how deadlocks arise and are resolved.

Intuition

Pessimistic locking: assume conflict will happen — acquire a lock before reading or writing, hold it until commit. Prevents conflicts but limits concurrency. MVCC (Multi-Version Concurrency Control): keep multiple versions of each row. Readers see a consistent snapshot from the moment their transaction started; writers create a new version. Readers never block writers and writers never block readers.

PostgreSQL and MySQL InnoDB use MVCC. It's why a long-running read transaction can see a consistent view of data even while other transactions are committing changes.

Explanation

Locks — shared, exclusive, and deadlocks

Shared lock (S): held during reads — multiple transactions can hold S locks Exclusive lock (X): held during writes — no other lock allowed simultaneously -- Explicit row-level locking in SQL SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- exclusive lock SELECT * FROM accounts WHERE id = 1 FOR SHARE; -- shared lock -- Deadlock: T1 holds lock on A, waits for B -- T2 holds lock on B, waits for A → cycle → neither can proceed -- Database detects the cycle and aborts one transaction (the "victim") -- Prevention: always acquire locks in the same order across transactions

Deadlocks are not bugs — they're an expected outcome of concurrent locking and the database handles them. The aborted transaction should be retried by the application. To minimise deadlocks, acquire locks in a consistent order and keep transactions short.

MVCC — how Postgres implements it

Each row version (tuple) has two system columns: xmin: transaction ID that created this version xmax: transaction ID that deleted/updated this version (0 if current) When T2 updates a row: 1. Mark old version's xmax = T2's transaction ID 2. Insert a new row version with xmin = T2's transaction ID When T1 reads (started before T2): - Sees old version (xmin < T1's snapshot, xmax not yet committed) - Does NOT see T2's new version (xmin > T1's snapshot start) Result: T1 and T2 operate concurrently without blocking each other Dead tuple cleanup: old row versions accumulate → VACUUM reclaims space autovacuum runs automatically; tables with heavy updates need tuning

MVCC's downside is table bloat — old versions accumulate until VACUUM cleans them up. A long-running transaction holds back the "horizon" — preventing VACUUM from reclaiming old versions needed by that transaction. This is the primary cause of table bloat in Postgres.

← Previous Next →