SQL / TRANSACTIONS AND CONCURRENCY
Locking and the readers that block writers
Predict and control when a read blocks a write: shared vs exclusive lock modes, FOR SHARE/FOR UPDATE, and why plain MVCC reads never block writers.
What you will learn
- S and X lock modes conflict, so a locking read blocks writers until it commits
- Plain SELECTs under MVCC never block writers; FOR SHARE and FOR UPDATE do
- Shrink blocking by narrowing the WHERE, weakening the lock mode, and committing sooner
- Use NOWAIT, SKIP LOCKED, or lock_timeout to fail fast instead of queueing
Understanding Locking and the readers that block writers
Every lock has a mode, and the engine decides whether two statements may proceed by looking their modes up in a compatibility matrix. Two shared (S) locks on the same row are compatible, but shared and exclusive (X) are not, so a read holding S on row 1 forces a writer that needs X on row 1 to sleep. The second half of the rule is duration: under two-phase locking a transaction releases nothing until it commits or rolls back, so the reader blocks the writer for as long as the reader's transaction lives, not for as long as the SELECT runs.
MVCC engines change the default. In PostgreSQL, Oracle, and InnoDB a plain SELECT reads from a snapshot of committed data and takes no row locks at all, which is why readers there do not block writers and writers do not block readers. You opt back into locking the moment you write FOR SHARE or FOR UPDATE, which you do when the value you just read is about to drive a write and must not change underneath you, and those row locks are held to commit like any other. Lock-based engines are the mirror image: SQL Server's default READ COMMITTED takes short shared locks as it scans, and at REPEATABLE READ it keeps them until commit, so an ordinary reporting query really can stall every writer it touches until READ_COMMITTED_SNAPSHOT or snapshot isolation is enabled.
The blocking you cause is therefore lock strength times lock duration times number of rows locked. Footprint is the factor people forget: locks land on the rows a scan visits, so a locking read on an unindexed column can lock far more rows than it returns, and SQL Server may escalate thousands of row locks into a single table lock that stops all writers at once. Cutting any of the three factors helps, so index the predicate, ask for FOR KEY SHARE instead of FOR UPDATE when you only need the row to keep existing, and commit as soon as the write is done.
CREATE TABLE accounts (id int PRIMARY KEY, balance numeric(10,2));
INSERT INTO accounts VALUES (1, 100.00), (2, 250.00);
BEGIN;
-- a plain snapshot read takes no row lock, so xmax stays 0
SELECT id, xmax::text = '0' AS row_unlocked FROM accounts WHERE id = 1;
-- the same read, now announcing an intent to write
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- the row header now carries our transaction id in xmax
SELECT id, xmax::text = '0' AS row_unlocked FROM accounts WHERE id = 1;
-- and the table itself is held in ROW SHARE mode until COMMIT
SELECT count(*) AS row_share_locks
FROM pg_locks
WHERE relation = 'accounts'::regclass
AND mode = 'RowShareLock'
AND pid = pg_backend_pid();
COMMIT;Whether a read blocks a write is decided by lock mode compatibility and how long the lock is held, not by whether the read changed anything.
Worked examples
A shared read makes the writer wait
A transaction that only reads, using FOR SHARE, holds up an UPDATE of the same row for its whole lifetime.
-- session A: reads, writes nothing, stays open
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR SHARE;
-- session B, while A is still open:
UPDATE accounts SET balance = balance - 10 WHERE id = 1;
-- session A:
COMMIT;Example explained
Line 1FOR SHARE takes a share lock on row 1 and keeps it until A ends, not until the SELECT returns.
Line 2B's UPDATE of balance requests a no-key-update lock on that row, which is incompatible with share, so B sleeps instead of erroring.
Line 3A never modified a byte, yet B's wait equals A's remaining transaction time.
Line 4While B waits, plain SELECTs of row 1 in any session still return 100.00 at once, because snapshot reads take no row lock.
Refusing to queue: NOWAIT, SKIP LOCKED, lock_timeout
Three ways for the second session to fail fast instead of joining the lock queue.
-- session A keeps account 1 locked
BEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- session B tries not to wait
SELECT id FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
SELECT id FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED;
SET lock_timeout = '50ms';
UPDATE accounts SET balance = 0 WHERE id = 1;Example explained
Line 1NOWAIT converts the wait into an immediate error, so a request fails in milliseconds instead of hanging on whatever A is doing.
Line 2SKIP LOCKED returns only the free rows and silently drops the locked one, which is how several workers pull different jobs out of one queue table.
Line 3lock_timeout bounds every lock wait in session B, row locks included, and the cancellation proves the UPDATE was queued behind A rather than failing on its own.
Line 4None of the three touches A's lock; only B's willingness to wait changed.
One reader, every writer blocked
A table-level share lock stops all writers to the table while leaving plain readers untouched.
BEGIN;
LOCK TABLE accounts IN SHARE MODE;
SELECT count(*) AS share_locks_held
FROM pg_locks
WHERE relation = 'accounts'::regclass
AND mode = 'ShareLock'
AND pid = pg_backend_pid();
SELECT count(*) AS still_readable FROM accounts;
COMMIT;Example explained
Line 1LOCK TABLE ... IN SHARE MODE is the table-wide form of a shared read lock, used when a transaction wants the table to stop changing.
Line 2ShareLock conflicts with RowExclusiveLock, and every INSERT, UPDATE, and DELETE takes RowExclusiveLock, so all writers to accounts queue until this COMMIT.
Line 3AccessShareLock, taken by plain SELECTs, is compatible with ShareLock, so ordinary reads keep working in other sessions.
Line 4A single statement can thus block every writer of a table without modifying a row.
Important notes
A row-level wait usually does not show up as a row entry in the catalogs; in PostgreSQL the waiter appears as a ShareLock wait on the holder's transactionid, so diagnose with pg_blocking_pids() or pg_stat_activity where wait_event_type = 'Lock'.
Some locking reads are implicit: inserting a child row takes a FOR KEY SHARE lock on the referenced parent row, so it blocks deletes and key updates of that parent for the rest of your transaction even though your statement never named the parent table.
Common mistakes
Using SELECT ... FOR UPDATE just to read the current value, then leaving the transaction open while the application thinks or calls another service; every writer of those rows queues behind you for that whole time, and PostgreSQL's default lock_timeout of 0 means they wait indefinitely.
Assuming a plain SELECT can never block anything: on SQL Server's default locking READ COMMITTED a long report takes shared locks as it scans, and at REPEATABLE READ it holds them to commit, so UPDATEs hit lock timeouts while nobody appears to be writing.
Running a locking read with an unindexed predicate: InnoDB locks the rows the scan examines, not just the ones that match, so WHERE on a non-indexed column can lock effectively the entire table and serialise every writer.
Try it yourself
Change, predict, then run
Recreate the accounts table, then run the same transaction three times using a plain SELECT, then FOR SHARE, then FOR UPDATE, checking xmax and the pg_locks mode each time. Write down which variants stamp the row and which two share the same table-level lock mode.
Open the SQL workspaceCheck your understanding
A report runs BEGIN; SELECT id, balance FROM accounts FOR SHARE; then spends 30 seconds formatting the rows in the application before COMMIT. An unrelated UPDATE of one of those rows waits nearly the full 30 seconds. Why?
- The reader's shared locks were dropped when the SELECT returned, so the UPDATE must be blocked by something else.
- FOR SHARE row locks live until the transaction ends, and a share lock is incompatible with the lock the UPDATE needs on that row.
- The UPDATE is waiting for the reader's snapshot to be released; removing FOR SHARE would not change the wait.
- PostgreSQL promotes any SELECT inside BEGIN into a locking read, so the BEGIN itself is what blocks the writer.
Show answer
Lock duration is tied to the transaction, not the statement: FOR SHARE keeps a share lock on every row it returned until COMMIT or ROLLBACK, and share conflicts with the no-key-update lock the UPDATE requests, so the writer sleeps for the reader's think time. Option 2 is tempting because the snapshot really does last the whole transaction, but a snapshot only controls what the reader sees; drop FOR SHARE and the identical transaction blocks nobody, which is exactly why plain reads are cheap.