Locks & Deadlocks

Locks are the mechanism used by databases to manage simultaneous access to data. They prevent multiple transactions from changing the same piece of data at the same time.

Types of Locks

1. Shared (S) Lock: Allows multiple transactions to read a resource but prevents any from writing (common during SELECT).
2. Exclusive (X) Lock: Prevents all other transactions from accessing the resource (common during UPDATE, DELETE).
3. Intent Locks: Used to indicate that a transaction plans to acquire a lock at a lower level (e.g., row-level) to prevent table-level changes.

Lock Granularity

Level	Scope	Overhead
Row	Single row	Low (most concurrency)
Page	Disk page (multiple rows)	Medium
Table	Entire table	High (least concurrency)
Database	Entire database	Very high

Databases start at row-level and may escalate to page or table locks when too many row locks are held.

MVCC — Multi-Version Concurrency Control

MVCC is NOT a locking mechanism — it is an alternative to locking for reads. Instead of acquiring a shared lock, readers see a “snapshot” of the data as of a point in time.

How it works:

• When a row is updated, the old version is preserved.
• Each transaction sees the version that was current when it started (or when the statement started, depending on isolation level).
• Writers still need exclusive locks to prevent conflicting writes.

Deadlocks

A deadlock occurs when two transactions are each waiting for the other to release a lock.

Transaction A: UPDATE accounts SET bal = 100 WHERE id = 1;
               -- now needs id = 2
Transaction B: UPDATE accounts SET bal = 200 WHERE id = 2;
               -- now needs id = 1

Result: A waits for B, B waits for A. Stalemate.

Deadlock Detection

Most databases have a deadlock detector that runs periodically (e.g., every second). When a deadlock is found:

1. The engine chooses a “victim” (usually the transaction with the least work done).
2. The victim’s transaction is terminated and rolled back.
3. The other transaction proceeds normally.
4. The application receives a deadlock error and must retry.

How to Avoid Deadlocks

1. Access tables in the same order in all transactions. If all transactions update A before B, cycles cannot form.
2. Keep transactions short. The shorter the transaction, the fewer locks held simultaneously.
3. Avoid user interaction inside a transaction. Don’t wait for user input while holding locks.
4. Use lower isolation levels. Serializable increases lock contention significantly.
5. Use index-based lookups. Without an index, a row-level UPDATE might lock the entire table.

Optimistic vs Pessimistic Locking

Approach	How It Works	Best For
Pessimistic (SELECT FOR UPDATE)	Locks rows immediately	High contention, short transactions
Optimistic (version column)	Checks for conflicts at commit time	Low contention, long transactions

Explicit Locking

-- Lock a specific row for update (pessimistic)
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Lock a table
LOCK TABLE accounts IN EXCLUSIVE MODE;