SQL Concurrency Control: Locks, MVCC, and Deadlocks
When multiple transactions run at once, the database must keep data consistent. Here is how locking and MVCC work and how I avoid deadlocks. Concurrency control is how a database lets multiple transactions run simultaneously without corrupting data. When two transactions try to update the same row at the same time, something must decide who goes first. The two main mechanisms are locking and multi-version concurrency control (MVCC). Understanding both is essential for writing transactions that perform well and do not deadlock. Locking: The Classic Approach Locking is straightforward. When a transaction modifies a row, it acquires a lock on that row. Other transactions that want to modify the same row wait until the lock is released. This ensures that only one transaction modifies a row at a time, preventing lost updates. . Transaction A BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; . Holds a lock on row id = 1 . Transaction B tries to update the same row and waits UPDATE accounts SET balance = balance + 100 WHERE id = 1; . Blocked until Transaction A commits or rolls back COMMIT; Locking is simple to…