Locks and Deadlocks

Isolation is implemented with locks. Most of the time you never think about them — until a query that normally takes a millisecond hangs for thirty seconds, or the database kills your transaction with "deadlock detected".

What gets locked

Row locks are the common case. UPDATE employees SET salary = 1 WHERE emp_id = 4 locks that row until the transaction ends. Another transaction updating the same row waits; one updating a different row does not.

Table locks are taken by schema changes — ALTER TABLE, DROP, some index builds. These block everything, which is why schema migrations are run carefully and why CREATE INDEX CONCURRENTLY (PostgreSQL) and online DDL (MySQL 5.6+) exist.

Gap locks (MySQL/InnoDB at REPEATABLE READ) lock the space between index values to prevent phantoms. They are why MySQL sometimes blocks an insert of a row that does not exist yet, which is baffling until you know the mechanism.

Shared versus exclusive

• Shared (read) locks are compatible with each other. Many readers, no problem.
• Exclusive (write) locks are compatible with nothing. One writer, and readers wait too — unless the database uses MVCC, where readers see an older version instead of waiting. That is the practical benefit of MVCC from the last lesson.

Explicit locking

BEGIN;
SELECT * FROM stock WHERE id = 1 FOR UPDATE;
-- nobody else can lock this row until COMMIT
UPDATE stock SET quantity = quantity - 1 WHERE id = 1;
COMMIT;

FOR UPDATE takes an exclusive lock as you read. FOR SHARE (PostgreSQL) or LOCK IN SHARE MODE (MySQL) takes a shared one — others may read, none may write.

Two modifiers that turn a hang into a decision:

• FOR UPDATE NOWAIT — fail immediately rather than wait.
• FOR UPDATE SKIP LOCKED — ignore locked rows and take the rest.

SKIP LOCKED is how you build a work queue in SQL: each worker claims rows nobody else has locked, with no coordination and no contention.

BEGIN;
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 10
FOR UPDATE SKIP LOCKED;
-- process, then mark done
COMMIT;

Deadlocks

Two transactions each holding a lock the other wants:

Transaction A            Transaction B
-------------            -------------
UPDATE row 1             UPDATE row 2       -- each holds one
UPDATE row 2             UPDATE row 1       -- each wants the other's
   (waits for B)            (waits for A)

Neither can proceed. The database detects the cycle, kills one transaction with a deadlock error, and lets the other finish.

The prevention is ordering. If every transaction locks rows in the same order — by primary key, always ascending — a cycle cannot form. Most deadlocks in real systems come from two code paths that happen to touch the same two tables in opposite orders.

Other measures:

• Keep transactions short. Less time holding locks, less chance of overlap.
• Touch fewer rows. A statement that locks a whole range conflicts with more.
• Take the locks you will need up front, rather than acquiring more as you go.
• Retry. A deadlock is a normal, expected condition under concurrency, not a bug. Application code that writes concurrently should catch the deadlock error and retry the transaction. This is the point most people miss.

Diagnosing blocking

When a query hangs, the question is what is holding the lock:

The usual finding is a transaction someone left open — an interactive session, or an application that opened one and then waited on something external. Which is lesson 37's warning arriving in production.

Lock timeouts are worth setting: lock_timeout (PostgreSQL), innodb_lock_wait_timeout (MySQL), SET LOCK_TIMEOUT (SQL Server). A query that fails after five seconds is far better than one that hangs until someone notices.

Common mistakes

• Inconsistent lock ordering — the direct cause of most deadlocks.
• Treating a deadlock as a bug to eliminate — it is a condition to retry.
• Long transactions — lock duration is transaction duration.
• No lock timeout — hangs instead of errors.
• Schema changes during peak load — ALTER TABLE can lock the whole table.

Interview question

What is a deadlock and how do you prevent one?

Two or more transactions each holding a lock the other needs, so neither can proceed; the database detects the cycle and aborts one. Prevent it by acquiring locks in a consistent order, keeping transactions short, and retrying on the deadlock error — because under concurrency they cannot be eliminated entirely, only made rare and survivable.

Module complete

You can now reason about what happens when several people write at once: what a transaction guarantees, what each isolation level costs, and why queries block.

Next: designing the tables in the first place — types, constraints, keys and normalisation.