Lock modes: names that describe conflicts

Every statement takes a lock on each table it touches, in one of eight modes. A mode carries no meaning of its own: it exists only as a row in a conflict table saying which other modes may be held on the same relation at the same time. That is why the names mislead. ROW EXCLUSIVE is a lock on the whole table, taken by anything that writes rows to it, and it locks no rows. ACCESS SHARE is what an ordinary read takes.

Two words that do not mean what they look like

Read the names as pairs. The first word says whether plain reads are affected: only the two ACCESS modes interact with them, and ACCESS EXCLUSIVE is the single mode that conflicts with everything, which is why most forms of ALTER TABLE stop all traffic to a table rather than just its writers. The second word says how the mode behaves among writers. ROW in a name is a historical hint about which statements take it, not a statement about granularity.

Row locks are a different mechanism with different names. FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE and FOR KEY SHARE mark the tuple itself rather than registering an entry against the relation, so a session blocked behind another session’s uncommitted row change is not waiting on a table lock at all. It shows up as a wait on a transaction id, and the resolution is that the other transaction ends rather than that a lock is released early.

Modes also accumulate and are held until the transaction finishes. There is no releasing a table lock at the end of a statement, which is the reason a long transaction that briefly wrote one row keeps a ROW EXCLUSIVE lock on that table for its whole life.

Seeing what one transaction is holding

Two ordinary statements against the same table, and both locks are still there afterwards.

CREATE TABLE person (id int PRIMARY KEY, country text);
INSERT INTO person SELECT g, 'c' || (g % 20) FROM generate_series(1, 1000) g;
BEGIN;
SELECT count(*) FROM person;
UPDATE person SET country = country WHERE id = 1;
SELECT mode, granted FROM pg_locks WHERE relation = 'person'::regclass ORDER BY mode;
COMMIT;

CREATE TABLE
INSERT 0 1000
BEGIN
 count 
-------
  1000
(1 row)

UPDATE 1
       mode       | granted 
------------------+---------
 AccessShareLock  | t
 RowExclusiveLock | t
(2 rows)

COMMIT

The update touched a single row out of a thousand and its table lock covers the relation. Nothing in that output records which row was changed, because that information is written into the row itself. Note also that the read’s lock was not dropped when the read finished.

When this stops being trivia

It stops being trivia the moment a schema change queues behind a long read, because the queue is ordered and the statements arriving after it inherit the wait even when they would not have conflicted. That mechanism, the query that walks the queue to the transaction at its root, and the timeout worth setting before any migration are in lock contention and blocking trees. Where the cycle closes instead of queueing, see deadlock.