TopolearnExplains why readers don't block writers in modern databases, and where blocking and deadlocks still happen.
There are two broad ways to keep concurrent transactions isolated.
Lock-based concurrency control (classic two-phase locking) makes transactions take shared locks to read and exclusive locks to write, holding them until commit. It is simple to reason about, but readers and writers block each other and deadlocks — A waits for B while B waits for A — must be detected and one victim aborted.
MVCC (multi-version concurrency control) keeps several versions of a row. A write creates a new version instead of overwriting; each transaction reads from a snapshot, seeing the versions that were committed when its snapshot was taken. Result: readers never block writers and writers never block readers. PostgreSQL, MySQL InnoDB, Oracle and SQL Server (with snapshot isolation) all use MVCC.
MVCC doesn't remove locks entirely. Two transactions updating the same row still conflict: the second waits for the first to commit or roll back. Explicit row locks (SELECT ... FOR UPDATE) and table locks taken by schema changes still exist, so deadlocks still happen — usually because transactions lock the same rows in different orders. Lock rows in a consistent order (for example by id) to avoid them.
MVCC's cost is garbage: old versions must be cleaned up once no snapshot needs them (VACUUM in PostgreSQL, purge of undo logs in InnoDB). Long-running transactions hold back that cleanup and cause bloat.
Example
-- Session A -- Session B
BEGIN;
UPDATE account SET balance = balance - 10
WHERE id = 1; -- row 1 locked by A
SELECT balance FROM account WHERE id = 1;
-- returns old value at once (MVCC snapshot)
UPDATE account SET balance = 0 WHERE id = 1;
-- waits: same row, write-write conflict
COMMIT; -- B's update now proceeds
-- Deadlock: A locks 1 then 2, B locks 2 then 1 -> one is aborted with
-- ERROR: deadlock detected. Fix: always lock rows in id order.
SELECT * FROM account WHERE id IN (1, 2) ORDER BY id FOR UPDATE;Remember
- MVCC: writes create new versions; reads use a snapshot
- Readers and writers don't block each other; two writers to one row do
- Lock rows in a consistent order to avoid deadlocks
Watch out for
- Leaving transactions open for minutes, which blocks MVCC cleanup and causes bloat.