Concurrency & Locking

Whenever more than one transaction can touch the same data at the same time, the database needs a way to stop them from stepping on each other. It does this with locks — internal markers that say "this piece of data is being used a certain way, wait your turn." Understanding locks is what lets you reason about why one transaction sometimes has to wait for another, and how to avoid two transactions permanently waiting on each other.

Row-level vs table-level locks

Lock granularity	What it protects	Concurrency impact
Row-level	A single row being read or modified.	High — other transactions can freely work with different rows in the same table.
Table-level	The entire table, e.g. during `ALTER TABLE` or an explicit `LOCK TABLE`.	Low — every other transaction touching the table has to wait.

Modern relational databases default to row-level locking for ordinary INSERT/UPDATE/DELETE statements, which is why thousands of transactions can update different rows of the same table simultaneously. Table-level locks are reserved for operations that genuinely need exclusive control of the whole table, such as schema changes.

Shared vs exclusive locks

Lock type	Who else can acquire it	Typical trigger
Shared (read) lock	Other shared locks — many readers can hold one at once.	A plain `SELECT` in some isolation levels, or `SELECT ... FOR SHARE`.
Exclusive (write) lock	Nobody — any other lock request has to wait.	`UPDATE`, `DELETE`, `INSERT`, or `SELECT ... FOR UPDATE`.

The rule of thumb is: readers don't block readers, but a writer blocks everyone else from touching that same row until it commits or rolls back.

Deadlocks

A deadlock happens when two transactions each hold a lock the other one needs, and each is waiting for the other to release it — neither can ever proceed. A classic example: transaction A locks row 1 and then tries to lock row 2, while transaction B has already locked row 2 and is now trying to lock row 1. Both wait forever, unless something intervenes.

Two transactions that can deadlock

SQL

-- Transaction A
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- ... pauses ...
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Transaction B, running at the same time
BEGIN;
UPDATE accounts SET balance = balance - 20 WHERE id = 2;
-- ... pauses ...
UPDATE accounts SET balance = balance + 20 WHERE id = 1;
COMMIT;

If A locks row 1 and B locks row 2 before either reaches its second statement, each is now waiting on a lock the other holds. Databases detect this situation automatically: one of the two transactions is picked as the "victim," its statement fails with a deadlock error, and its transaction is rolled back so the other can proceed. The application is expected to catch that error and retry.

The best defense is consistent ordering

Most deadlocks disappear entirely if every transaction that needs to touch multiple rows always locks them in the same order — for example, always updating the lower account ID first. If both transactions above always touched account 1 before account 2, the second transaction would simply wait for the first to finish instead of deadlocking.

Preventing race conditions with SELECT ... FOR UPDATE

A common bug is a "read, then write" race: a transaction reads a value, decides what to do based on it, and then writes a new value — but another transaction changed the row in between the read and the write. SELECT ... FOR UPDATE closes this gap by taking an exclusive lock on the rows as you read them, so no other transaction can modify (or also lock) them until you commit or roll back.

Safely decrementing stock

SQL

BEGIN;

-- lock the row for the duration of this transaction
SELECT quantity FROM inventory WHERE product_id = 7 FOR UPDATE;

-- application checks quantity here; suppose it is 3 and the order needs 2
UPDATE inventory SET quantity = quantity - 2 WHERE product_id = 7;

COMMIT;

Any other transaction that also tries to SELECT ... FOR UPDATE the same row will simply wait until this one commits, at which point it sees the updated quantity — guaranteeing two concurrent orders can never both oversell the same stock.

Locks held too long hurt everyone

A transaction that takes a row lock and then sits idle — waiting on a slow network call, user input, or an external API — blocks every other transaction that needs that row for as long as it stays open. Keep transactions that hold locks as short as possible: do the locking work, commit, and only then perform slow unrelated operations.