Locks are how Postgres coordinates concurrent access to shared resources. A shared resource might be a table one session is reading while another session tries to drop it, or a single row that two transactions try to update at the same time. In each case, Postgres has to impose an order: the table cannot disappear while it is being read, and two transactions cannot update the same row simultaneously.

Granularity and modes

Postgres takes locks at several levels, including relations such as tables, indexes, and sequences; individual rows and tuples; transaction IDs; index pages; and other internal objects. Each lock level has its own set of modes. Modes matter because compatibility is not all-or-nothing: some operations can safely proceed together, while others must block each other.

Table-level locks have eight modes, ranging from AccessShare (the weakest, taken by a plain SELECT) to AccessExclusive (the strongest, taken by DROP TABLE, most ALTER TABLE forms, and VACUUM FULL). Row-level locks have four: Key Share, Share, No Key Update, and Update.

These modes let Postgres distinguish between operations that can safely run together and operations that need to be serialised. Multiple SELECTs can all hold AccessShare on the same table because they are only reading. AccessExclusive, on the other hand, conflicts with every other table-level lock, including AccessShare; that is what prevents a DROP TABLE from running while the table is still in use. For the exact compatibility rules, see the Postgres docs’ table-level lock conflict matrix.

Where the locks live

Table locks — along with tuple, transactionid, and advisory locks — are heavyweight locks. Postgres tracks them in a shared-memory hash table managed by the lock manager, and exposes the current state through the pg_locks view.

Row locks are handled differently. A large UPDATE might lock millions of rows, but the lock table is sized at startup; storing one shared-memory entry per locked row would not scale. Instead, Postgres records the lock in the row itself, by writing the locking transaction’s ID into the tuple header’s xmax field, together with infomask bits that encode the lock mode. There is no shared-memory lock entry and no per-row lock-manager bookkeeping.

The consequence is that pg_locks knows nothing about row locks. The pgrowlocks extension fills the gap: it reads tuple headers directly and returns locked rows, their lock modes, and the locking transaction IDs.

Table locks do what their name suggests: they coordinate access to an entire relation. The other heavyweight lock types play more specialised roles:

  • A tuple lock is a lock on a single (block, offset) pair. It serialises waiters for a row, so that only one waiter at a time interacts with the row-level lock state. We’ll see this in action shortly.
  • A transactionid lock is held exclusively by every transaction on its own xid for as long as the transaction is alive. Another transaction that needs to wait for it takes a share lock on the same xid, which blocks until the exclusive lock is released at commit or rollback. This is the mechanism behind “wait for transaction X to finish.”

Example 1 — one transaction, one update

Let’s walk through what the lock state looks like. Set up a tiny table:

CREATE TABLE demo AS
SELECT i AS a, 'initial' AS b FROM generate_series(1, 3) i;

Open a first session (call it T1) and begin a transaction:

-- T1
BEGIN;
SELECT txid_current();
--  txid_current
-- --------------
--           763

Calling txid_current() forces the transaction to claim an xid, which makes it acquire the exclusive transaction ID lock on that xid. Read-only transactions don’t get one until they modify something — the call above is what pushes T1 to take one now. We can see it in pg_locks, filtered down to our database and any transaction ID lock:

SELECT locktype, relation::regclass, transactionid, pid, mode, granted
FROM pg_locks
WHERE database = (SELECT oid FROM pg_database WHERE datname = current_database())
   OR transactionid IS NOT NULL;
--  locktype      | relation | transactionid | pid | mode          | granted
-- ---------------+----------+---------------+-----+---------------+---------
--  transactionid |          |           763 | 263 | ExclusiveLock | t

Now read from the table:

-- T1
SELECT * FROM demo;

A new entry appears: an AccessShare lock on demo. That’s the lock that stops another session from running DROP TABLE demo or most forms of ALTER TABLE demo while our query is still in flight.

 locktype      | relation | transactionid | pid | mode            | granted
---------------+----------+---------------+-----+-----------------+---------
 relation      | demo     |               | 263 | AccessShareLock | t
 transactionid |          |           763 | 263 | ExclusiveLock   | t

Then update one row:

-- T1
UPDATE demo SET b = 'b' WHERE a = 1;

Two things change. First, the relation lock has been upgraded to RowExclusive, the table-level mode taken by UPDATE, INSERT, and DELETE to signal that the transaction may modify rows in the relation. Second, Postgres has recorded a row lock by writing T1’s transaction ID into the tuple’s xmax. The row lock does not appear in pg_locks, because it is stored in the tuple header rather than the lock manager; pgrowlocks can see it by inspecting the row directly:

SELECT * FROM pgrowlocks('demo');
--  locked_row | locker | multi | xids  |       modes       | pids
-- ------------+--------+-------+-------+-------------------+-------
--  (0,1)      |    763 | f     | {763} | {"No Key Update"} | {263}

The row at ctid (0,1) is locked by xid 763 in mode No Key Update. That is the row-level mode used by a plain UPDATE when it does not modify any key columns.

Example 2 — two transactions, different rows

Open a second session T2, without closing T1, and update a different row:

-- T2
BEGIN;
SELECT txid_current();  -- 764
UPDATE demo SET b = 'b' WHERE a = 2;

T2 takes an exclusive transaction ID lock on 764, a RowExclusive on demo, and records its row lock by writing its transaction ID into the xmax of row (0,2). It does not also hold AccessShare; RowExclusive is the stronger table-level lock and covers the access needed here. Both locked rows now appear in pgrowlocks:

 locked_row | locker | multi | xids  |       modes       | pids
------------+--------+-------+-------+-------------------+-------
 (0,1)      |    763 | f     | {763} | {"No Key Update"} | {263}
 (0,2)      |    764 | f     | {764} | {"No Key Update"} | {124}

There is no conflict: the two transactions are touching different rows. They can commit independently, in either order. Once they do, their heavyweight locks are released, and the xmax values left behind in the tuple headers become historical state. Future readers or writers interpret those transaction IDs through the commit log.

Example 3 — two transactions, same row

Roll back T2, start it again, and this time have it touch the same row T1 already locked:

-- T2
BEGIN;
SELECT txid_current();  -- 765
UPDATE demo SET b = 'b' WHERE a = 1;

The statement hangs. T2 reaches row (0,1), reads its xmax, and sees that transaction 763 still holds a lock on the row. Before it can proceed, T2 has to wait for T1 to finish. To arrange that wait, it does two things:

  1. It acquires a heavyweight tuple lock on (0,1), which puts T2 in the waiter path for that row.
  2. It requests a share lock on transactionid = 763. T1 holds an exclusive lock on that transaction ID, so the share lock waits until T1 releases it by committing or rolling back.

pg_locks shows both:

 locktype      | relation | transactionid | pid | mode          | granted
---------------+----------+---------------+-----+---------------+---------
 tuple         | demo     |               | 124 | ExclusiveLock | t
 transactionid |          |           763 | 124 | ShareLock     | f

Note granted = f on the share lock: that is the lock T2 is waiting for. The same wait shows up from the session side in pg_stat_activity:

 wait_event_type | wait_event    | state               | pid | query
-----------------+---------------+---------------------+-----+----------------------------------
 Lock            | transactionid | active              | 124 | UPDATE demo SET b = 'b' WHERE a = 1;
 Client          | ClientRead    | idle in transaction | 263 | UPDATE demo SET b = 'b' WHERE a = 1;

T1 is idle (just sitting in its transaction), T2 is active but waiting on a transaction ID lock. Now roll back T1:

-- T1
ROLLBACK;

T1’s exclusive transaction ID lock is released, T2’s share request is granted immediately, T2 rewrites the row’s xmax to 765, the tuple lock is released, and T2’s UPDATE returns.

Example 4 — three transactions, same row

Reset, and this time queue up three sessions. T1 holds the row lock as before. T2 arrives next and follows the path we just saw: it takes the tuple lock on (0,1), requests a share lock on T1’s transaction ID, and blocks. Then T3 arrives and tries to update the same row:

-- T3
BEGIN;
SELECT txid_current();  -- 767
UPDATE demo SET b = 'b' WHERE a = 1;

T3 also sees transaction 763 in the row’s xmax and needs to join the wait for that row. But T2 already holds the heavyweight tuple lock on (0,1), so T3 cannot yet interact with the row-level lock state. Instead, it blocks on the tuple lock without requesting a share lock on T1’s transaction ID:

 locktype      | relation | transactionid | pid | mode          | granted
---------------+----------+---------------+-----+---------------+---------
 tuple         | demo     |               | 124 | ExclusiveLock | t
 tuple         | demo     |               | 988 | ExclusiveLock | f
 transactionid |          |           763 | 124 | ShareLock     | f

This is the role of the tuple lock. Without it, every waiter would request a share lock on the same transaction ID. When T1 finished, they would all wake up together and race to inspect or modify the row. The tuple lock serialises that path. Only the session holding the tuple lock is actively waiting on the row-level state; everyone else waits behind it on the tuple lock.

Roll back T1 and watch the handoff:

-- T1
ROLLBACK;

When T1 finishes, its exclusive transaction ID lock is released, which allows T2’s share lock to be granted. T2 can then complete its UPDATE and release the tuple lock. At that point, T3 acquires the tuple lock and rechecks the row. The row is now locked by T2, so T3 requests a fresh share lock on T2’s transaction ID. When T2 eventually commits or rolls back, T3 gets its turn.

Wrap-up

To summarise, there are a handful of distinct pieces doing different jobs here:

  • Table locks are heavyweight locks, stored in shared memory and visible in pg_locks. They coordinate coarse-grained access to relations, such as reads versus drops, or DDL against concurrent activity.
  • Row locks are stored on disk in the tuple header, using xmax and infomask, and can be inspected with pgrowlocks. They coordinate concurrent writes to individual rows.
  • Tuple locks are heavyweight locks on a specific (block, offset) tuple location. They serialise the queue of sessions waiting to interact with a locked row.
  • Transaction ID locks are heavyweight locks associated with live transactions. They are the mechanism waiters actually block on: “wait for transaction X to finish” is implemented as a share-lock request on X’s transaction ID.