Locks: Shared, Exclusive, Row and Table Level

Concept

A lock reserves a resource for one transaction so another cannot change it concurrently. Locks are acquired automatically by the engine, and they are released when the transaction commits or rolls back - never earlier.

Lock modes

Locking reads

-- Plain SELECT: no lock in InnoDB, reads the MVCC snapshot
SELECT balance FROM accounts WHERE id = 1;

-- Exclusive: nobody else may read-for-update or write this row until you commit
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;

-- Shared: others may also read it, but nobody may change it
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;          -- MySQL 8
SELECT balance FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- MySQL 5.7, MariaDB

-- Do not wait, or wait no longer than n seconds (MySQL 8, PostgreSQL)
SELECT ... FOR UPDATE NOWAIT;
SELECT ... FOR UPDATE SKIP LOCKED;

SKIP LOCKED is how a work queue is built correctly: each worker takes the first unlocked job rather than queueing behind another worker.

-- A safe read-modify-write
START TRANSACTION;
    SELECT balance INTO @b FROM accounts WHERE id = 1 FOR UPDATE;
    -- no other transaction can change row 1 now
    UPDATE accounts SET balance = @b - 5000 WHERE id = 1;
COMMIT;

-- Better still, when the logic allows it: one atomic statement, no read needed
UPDATE accounts SET balance = balance - 5000 WHERE id = 1 AND balance >= 5000;

Granularity

The rule that surprises everyone

-- No index on `status`: InnoDB must examine every row,
-- so it locks every row it examines - effectively the whole table.
UPDATE orders SET status = 'review' WHERE status = 'pending';

-- With an index on status, only the matching rows are locked.
CREATE INDEX idx_orders_status ON orders (status);

InnoDB locks rows through the index it uses. If the query has no usable index, it scans and locks everything it touches - including rows that do not match. An index is not only a performance feature; it is a concurrency feature.

Inspecting locks

-- MySQL 8
SELECT * FROM performance_schema.data_locks;
SELECT * FROM sys.innodb_lock_waits;

-- Any version: the engine status dump includes current locks and the last deadlock
SHOW ENGINE INNODB STATUS;

-- PostgreSQL
SELECT * FROM pg_locks;

Important rules

• Locks are held until the transaction ends, not until the statement ends.
• In InnoDB, plain SELECT takes no locks - readers do not block writers.
• Every UPDATE, DELETE and INSERT takes exclusive row locks.
• Rows are locked via the index; without a usable index, the scan locks far more than it should.
• Gap locks under REPEATABLE READ can block inserts into ranges you only read.
• innodb_lock_wait_timeout (50 seconds by default) decides how long a statement waits before failing.

Common mistakes

• Read-modify-write without FOR UPDATE, losing one of two concurrent updates.
• Updating on an unindexed column and locking the whole table under load.
• Holding a transaction open across an external API call.
• Using LOCK TABLES where a row level lock would do.
• Assuming a plain SELECT guarantees the row will not change before your UPDATE.

Best practices

• Prefer one atomic statement to a read-then-write pair.
• Index every column used in the WHERE clause of an UPDATE or DELETE.
• Keep transactions short, and do no I/O inside them.
• Use SKIP LOCKED for queue tables and NOWAIT where waiting is worse than failing.
• Access tables in a consistent order across the codebase - see the deadlock note.

Practice

1. Demonstrate two sessions competing for the same row with and without FOR UPDATE.
2. Explain why UPDATE orders SET status = 'x' WHERE customer_id = 1 locks fewer rows once customer_id is indexed.
3. Write the atomic version of "deduct 5000 only if the balance allows it".