When thousands of users are reading and writing the same data at the same moment, how does the database keep everything correct? If two people try to buy the last item in stock at the same time, only one should succeed. This topic explains the strategies databases use to manage that safely — from simple locks to multi-version snapshots.
Two-Phase Locking is a set of rules that prevents concurrent transactions from producing incorrect results. Phase one (growing): the transaction acquires all the locks it needs — on every row it reads or writes. It never releases any locks during this phase. Phase two (shrinking): when the transaction is done with all its work, it releases all locks at once (on commit or rollback). This two-phase discipline guarantees that no two concurrent transactions can produce results that would be impossible in a sequential world. The version used in real databases holds all locks until commit to avoid a problem where one transaction's rollback forces another to also rollback.
Multi-Version Concurrency Control (MVCC) is the reason you can read data quickly on a busy database without waiting for writers to finish. Instead of locking a row when a writer updates it, the database keeps the old version for readers and creates a new version for the writer. A reader sees a consistent snapshot of the database from the moment their transaction started — even if other transactions are updating rows at the same time. Readers never block writers. Writers never block readers. PostgreSQL and Oracle use MVCC for all reads — no read locks needed.
Imagine Transaction A is holding the lock on Row 1 and waiting for Row 2. At the same time, Transaction B is holding the lock on Row 2 and waiting for Row 1. Both are stuck waiting for each other — forever. This is a deadlock. The database detects it by checking for these circular waits. When found, it picks one transaction as the "victim," rolls it back (freeing its locks), and lets the other proceed. This is a normal occurrence in a busy database — your application code needs to catch the deadlock error and retry the transaction.
Optimistic concurrency is based on the assumption that conflicts are rare. Instead of locking rows when you read them, you just read freely. When you are ready to save your changes, you check whether anyone else changed the same data while you were working. If no conflict — great, commit. If someone else changed it — abort and try again. This works very well when conflicts are unlikely (most reads), but is inefficient if many transactions are competing for the same rows (they all keep retrying). A common implementation is a "version number" column — you read the version, and when writing, check that the version is still the same.
Different lock types have different compatibility rules. The matrix determines whether a lock request can be granted immediately or must wait. InnoDB adds intent locks (IS, IX) at the table level to enable efficient table-level lock checks without scanning all row locks.
| Requester ↓ / Holder → | IS | IX | S | SIX | X |
|---|---|---|---|---|---|
| Intent Shared (IS) | Yes | Yes | Yes | Yes | No |
| Intent Exclusive (IX) | Yes | Yes | No | No | No |
| Shared (S) | Yes | No | Yes | No | No |
| Shared + IX (SIX) | Yes | No | No | No | No |
| Exclusive (X) | No | No | No | No | No |
2PL divides a transaction's lifetime into two strictly separated phases: a growing phase where locks are only acquired (never released), and a shrinking phase where locks are only released (never acquired). This ordering constraint is sufficient to guarantee that any concurrent execution schedule is serializable.
In the first phase, the transaction accumulates all the locks it needs. Reading a row requires a shared lock (others can also read, but no one can write). Writing requires an exclusive lock (no one else can read or write). If a transaction needs to upgrade from read to write on the same row, it waits until all other readers release their locks first. During this entire phase, no locks are ever released — only acquired.
The moment a transaction releases its first lock is called the lock point. After this moment, the transaction cannot acquire any new locks. The order in which transactions reach their lock points effectively defines the order in which they "happened." Two transactions that never overlap in their lock sets can run truly in parallel. Two that do overlap must take turns, and their lock points determine which one went first in the equivalent sequential execution.
In theory, a transaction could release some locks early while still running. But this creates a danger: another transaction could read the partially-released data, and if the first transaction later rolls back, that second transaction made decisions based on data that never officially existed. This chain of failures is called a cascading abort. To prevent it, real databases use Strict 2PL: all locks are held until the very end (commit or rollback), then released all at once. This is slightly more conservative but prevents cascading failures entirely.
Locks can be applied at different scales: the whole table, a page of rows, or a single row. Locking an entire table is simple but heavy — it blocks everyone while one transaction is running. Locking individual rows is more precise and allows multiple transactions to work on different rows at the same time. To avoid scanning every row-level lock when someone wants to lock the whole table, databases use intent locks — a quick signal at the table level that says "there are row locks in here," allowing fast compatibility checks without looking at every row.
MySQL InnoDB at REPEATABLE READ uses gap locks to prevent phantom reads without requiring SERIALIZABLE isolation. A gap lock locks the gap between two index values — preventing any INSERT that would fall into that gap.
A record lock targets exactly one row. When you run SELECT * FROM t WHERE id = 10 FOR UPDATE, the database places a lock on the row with id=10. No other transaction can update or delete that specific row while your transaction holds the lock. It does not block inserts of unrelated rows — only this one record is protected.
A gap lock protects the empty space between existing index values. Imagine you are reading all orders with id between 10 and 20. A gap lock on that range prevents any other transaction from inserting a new order that would fall inside it. This stops phantom rows — rows that would appear if you re-ran the same query. Importantly, two transactions can both hold gap locks on the same range at the same time without conflicting, because they are both just preventing inserts, not modifying anything.
A next-key lock is a record lock and a gap lock combined into one. It protects a specific existing row AND the empty space just before it. When InnoDB scans a range of rows, it places next-key locks on each row it touches, plus the gap after the last one. The result: no other transaction can modify any of those rows, and no transaction can insert a new row that would appear in that range. It is InnoDB's default strategy for range scans.
Gap locks can create surprising deadlocks. Imagine two transactions each trying to insert a row next to the other's gap lock. Transaction A holds a gap lock that blocks B's insert, and transaction B holds a gap lock that blocks A's insert. Neither can move forward — a deadlock. InnoDB detects this circular standoff automatically, picks the younger transaction as the victim, rolls it back, and lets the other proceed. Your application should catch error 1213 (ER_LOCK_DEADLOCK) and retry the transaction.
| Strategy | How It Works | Pros | Cons |
|---|---|---|---|
| Deadlock detection | Maintain wait-for graph; detect cycles periodically; abort victim | No unnecessary aborts; maximum concurrency | Cycles can exist briefly; detection adds overhead |
| Timeout | Abort any transaction waiting for a lock longer than N ms | Simple; no graph maintenance | False positives (slow transactions aborted unnecessarily) |
| Wait-Die | Older Tx waits for younger; younger Tx dies instead of waiting | No cycles possible; deterministic | Extra aborts of young transactions under high load |
| Wound-Wait | Older Tx forces younger Tx to abort (wound); younger Tx waits for older | Older (more important) transactions rarely abort | Younger transactions are aggressively killed |
| Lock ordering | Application always acquires locks in a fixed global order | Eliminates deadlocks entirely; no overhead | Requires disciplined coding across all code paths |
-- Explicit row lock: SELECT FOR UPDATE (pessimistic) BEGIN; SELECT balance FROM accounts WHERE id = 42 FOR UPDATE; -- X lock on row 42 UPDATE accounts SET balance = balance - 100 WHERE id = 42; COMMIT; -- Shared lock: FOR SHARE (read-with-lock, no exclusive needed) SELECT * FROM orders WHERE id = 99 FOR SHARE; -- Other transactions can also take FOR SHARE; only FOR UPDATE blocks -- Skip locked rows: non-blocking job queue pattern BEGIN; SELECT id, payload FROM job_queue WHERE status = 'pending' ORDER BY priority DESC, created_at LIMIT 1 FOR UPDATE SKIP LOCKED; -- skip rows locked by other workers UPDATE job_queue SET status = 'processing', worker_id = :wid WHERE id = :id; COMMIT; -- Lock timeout: abort if lock not acquired within 2 seconds SET lock_timeout = '2s'; -- PostgreSQL SET innodb_lock_wait_timeout = 2; -- MySQL -- Advisory lock: application-level mutex (PostgreSQL) SELECT pg_advisory_xact_lock(12345); -- acquired until COMMIT/ROLLBACK SELECT pg_try_advisory_xact_lock(12345); -- non-blocking; returns false if busy -- Optimistic locking with version column BEGIN; SELECT balance, version FROM accounts WHERE id = 42; -- application computes new_balance, reads version=7 UPDATE accounts SET balance = :new_balance, version = version + 1 WHERE id = 42 AND version = 7; -- if 0 rows updated, someone else modified it — retry COMMIT;
| Property | 2PL (Strict) | MVCC (Snapshot Isolation) | SSI (Serializable SI) |
|---|---|---|---|
| Reads block writers? | Yes (shared lock) | No | No |
| Writers block readers? | Yes (excl. lock) | No | No |
| Prevents write skew? | Yes (X lock) | No | Yes (anti-dep tracking) |
| Prevents phantom reads? | Yes (predicate lock) | Not always | Yes |
| Deadlocks possible? | Yes | No (read-only paths) | Serialization aborts |
| Garbage collection | N/A | VACUUM needed | VACUUM needed |
| Used by | InnoDB (for writes), DB2 | PostgreSQL, Oracle, SQL Server RCSI | PostgreSQL SERIALIZABLE, CockroachDB |
Step through a 3-transaction, 5-resource locking scenario. Watch Shared and Exclusive locks fill the table. A deadlock cycle forms and triggers rollback of one victim.
2PL prevents non-serializable schedules by ensuring all locks are acquired before any are released. The deadlock is a cycle in the wait-for graph — T1 holds Row A and waits for Row B; T2 holds Row B and waits for Row A. The DBMS picks a victim and rolls back.
If T1 locks row A then row B, and T2 locks row B then row A, a deadlock is guaranteed under concurrent execution. Always acquire locks in a consistent global order across all code paths — ascending primary key, alphabetical table name, or any deterministic ordering. This is the single most effective deadlock prevention technique.
LOCK TABLE orders IN EXCLUSIVE MODE when a row-level FOR UPDATE would suffice blocks every reader and writer on the table. Row-level locking has dramatically higher concurrency. Use table-level locks only for DDL operations or bulk imports that genuinely need exclusive table access.
Deadlocks are a normal outcome of concurrent transactions, not a bug. The DBMS will abort one transaction with SQLSTATE 40P01 (PostgreSQL) or error 1213 (MySQL). Application code must catch this specific error and retry the entire transaction — not crash, not surface a 500 error to the user. Implement exponential backoff and a retry limit.
SELECT FOR UPDATE locks the row. If application code runs long logic (API call, file I/O) between the SELECT and the UPDATE, the lock is held for the entire duration. Every other transaction waiting for that row queues up. Keep the locked read-modify-write operation in a single short transaction. Prepare all inputs before BEGIN.
Advisory locks (pg_advisory_lock) are application-level mutual exclusion that the database does not understand in terms of data correctness. Relying on advisory locks instead of proper row locking for data mutations means the DBMS cannot help with deadlock detection or automatic abort. Use row-level locks for data protection; use advisory locks only for coarse-grained application-level serialization (e.g., "only one migration runs at a time").
lock_timeout to fail fast rather than wait indefinitely.SELECT FOR UPDATE is pessimistic; version-column optimistic locking (UPDATE … WHERE version = :v, fail if 0 rows updated) is optimistic. MVCC snapshot isolation is conceptually optimistic for reads (free reads), but pessimistic for write-write conflicts (still uses row locks). SSI is fully optimistic — even write conflicts are validated at commit.SELECT … FOR UPDATE SKIP LOCKED acquires an exclusive lock on the selected rows but, instead of blocking on rows already locked by other transactions, silently skips them. Use case: database-backed job queues. Multiple workers each run: SELECT id, payload FROM jobs WHERE status='pending' LIMIT 1 FOR UPDATE SKIP LOCKED. Each worker atomically claims a different job — no two workers claim the same job, and they don't block each other. Without SKIP LOCKED, all workers would queue waiting for the first pending job, creating a thundering herd when that job is released. Supports throughput of roughly 1K–5K jobs/sec from PostgreSQL. For higher throughput, use a dedicated queue (Redis, Kafka, SQS).pg_advisory_lock(key)) — held until explicitly released or session ends; transaction-level (pg_advisory_xact_lock(key)) — automatically released at commit/rollback. Use cases: distributed mutex for background jobs ("only one instance of the nightly job runs at a time"); application-level resource locking when DB row locking is too granular; preventing concurrent schema migrations. Non-blocking variant: pg_try_advisory_xact_lock(key) returns false if already locked. Do NOT use advisory locks for row-level data protection — the DBMS won't enforce it and you bypass all deadlock detection and ACID guarantees for that data.SET lock_timeout = '2s'). The waiting transaction is aborted with error code 55P03 (lock_timeout, PostgreSQL) — not the lock holder. The lock holder continues normally. Lock timeout is a blunt instrument — it aborts potentially non-deadlocked transactions. Deadlock: two or more transactions are mutually waiting with no way to proceed. The DBMS detects the cycle and aborts one transaction (the "victim") with 40P01. Application response to both: catch the error and retry the transaction. The distinction matters for which transaction to retry: on timeout, only the waiting transaction needs retry; on deadlock, only the victim transaction needs retry (the winner continues). Both are transient failures — retry with exponential backoff is the correct pattern.