Database Locking Concepts

Locking is the mechanism a database uses to control concurrent access to the same data, preventing two transactions from making conflicting changes at the same time. When a transaction needs to read or write a row, it may need to acquire a lock on that row first, and other transactions that need a conflicting lock on the same row must wait until it is released.

Locking is closely related to isolation levels — stricter isolation is typically implemented, in part, by holding locks for longer or acquiring them more broadly.

Why it matters

Locks are how a database stops two transactions from overwriting each other. They also cause the hardest production problems: queries that hang while waiting for a lock, and deadlocks in which two transactions wait for each other for ever. Understanding them is what lets you diagnose a system that "sometimes just freezes".

Shared Locks vs. Exclusive Locks

A shared lock (also called a read lock) allows a transaction to read a row, and multiple transactions can hold shared locks on the same row at the same time — reading doesn't conflict with other reading. An exclusive lock (also called a write lock) is required to modify a row (UPDATE, DELETE), and only one transaction can hold an exclusive lock on a given row at a time; it also blocks any other transaction from acquiring a shared lock on that row until it is released. This is why a long-running write can block readers, depending on the isolation level and locking strategy in use.

• Shared (S) lock — many transactions can hold it simultaneously; used for reads.
• Exclusive (X) lock — only one transaction can hold it; used for writes; blocks both shared and exclusive locks from others.

Deadlocks

A deadlock occurs when two (or more) transactions each hold a lock the other one needs, and neither can proceed. For example, Transaction A locks Row 1 and then tries to lock Row 2, while Transaction B has already locked Row 2 and is trying to lock Row 1 — each is waiting on the other forever. Databases detect this situation automatically and resolve it by choosing one transaction as the "deadlock victim," forcibly rolling it back with an error so the other can proceed. Applications should catch this error and retry the failed transaction.

Lock Timeouts

Rather than waiting forever for a lock to become available, most databases support a lock timeout: if a transaction cannot acquire a needed lock within a configured period, it fails with a timeout error instead of blocking indefinitely. This protects the system from a single stuck or slow transaction freezing every other transaction that needs the same data. Applications typically handle a lock timeout the same way as a deadlock: catch it and retry, often with a short backoff delay.

Examples

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE account_id = 1;
-- ... then tries:
UPDATE accounts SET balance = balance + 50 WHERE account_id = 2;

-- Transaction B (running concurrently)
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE account_id = 2;
-- ... then tries:
UPDATE accounts SET balance = balance + 20 WHERE account_id = 1;

ERROR: deadlock detected
DETAIL: Process A waits for ShareLock on account_id 2; blocked by Process B.
Process B waits for ShareLock on account_id 1; blocked by Process A.
HINT: One transaction was automatically rolled back; the application should retry it.

BEGIN;
SET LOCAL lock_timeout = '3s';

UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 900;

COMMIT;

ERROR: canceling statement due to lock timeout
(If the row was locked by another transaction for more than 3 seconds, this UPDATE fails immediately instead of waiting indefinitely, and the application can retry.)

Exercise

This exercise needs a real database server, so it cannot run in the playground. Write a PostgreSQL transaction that transfers 100 from account 1 to account 2 safely under concurrency: lock both rows first with a single SELECT ... FOR UPDATE that takes them in id order, then make the two updates and commit.

Expected result: two sessions running this transaction at once cannot deadlock, because both request the row locks in the same order.

BEGIN;

-- TODO: lock the rows for accounts 1 and 2, in id order

-- TODO: subtract 100 from account 1

-- TODO: add 100 to account 2

COMMIT;

BEGIN;

SELECT id, balance
FROM accounts
WHERE id IN (1, 2)
ORDER BY id
FOR UPDATE;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

Quick quiz

Interview questions