Reference / Lock Modes

PostgreSQL Lock Modes

Eight table lock modes and four row lock modes, their compatibility, and the SQL commands that acquire them. Select a version to read its pinned source definitions.

Show a command in the matrices

Selecting a command highlights its associated modes. Actual locks depend on the named object, options and execution phase.

Table lock compatibility 8 × 8

Rows show requested locks and columns show locks held by another transaction, on the same object. ROW in a table lock's name does not make it a row lock.

× Conflict
· Compatible

Table lock conflict matrix. Rows are requested locks; columns are held locks. × means table conflict; · means compatible.
Held →Requested ↓	AS	RS	RX	SUE	S	SRX	X	AX
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

Select a cell to compare requested and held modes. Use the arrow keys to move through the matrix.

Combined table and row locks 14 × 14

Four SELECT FOR combinations appear under ROW SHARE; two writing combinations appear under ROW EXCLUSIVE. These are eight table-only entries plus six table-and-row combinations, still representing 12 lock modes.

× Table conflict, even on different rows
● Conflict on the same row only
· Compatible modes

Combined table and row lock matrix. Rows are requested locks; columns are held locks. × means table conflict; · means compatible; ● means conflict on the same row only. Indented entries acquire both the parent table mode and the row mode.
Held →Requested ↓	AS	ROW SHARE					ROW EXCLUSIVE			SUE	S	SRX	X	AX
Held →Requested ↓	Table only	Table only	FKS	FS	FNKU	FU	Table only	FNKU	FU	Table only	Table only	Table only	Table only	Table only
ACCESS SHARETable only
ROW SHARETable only
FOR KEY SHARE
FOR SHARE
FOR NO KEY UPDATE
FOR UPDATE
ROW EXCLUSIVETable only
FOR NO KEY UPDATE
FOR UPDATE
SHARE UPDATE EXCLUSIVETable only
SHARETable only
SHARE ROW EXCLUSIVETable only
EXCLUSIVETable only
ACCESS EXCLUSIVETable only

Select a cell to compare requested and held modes. Use the arrow keys to move through the matrix.

Both operations are assumed to use the same table. Indented entries acquire both the parent table mode and the listed row mode. Groups describe commonly combined modes, not a linear ordering of lock strength.

Abbreviations: AS ACCESS SHARE · RS ROW SHARE · RX ROW EXCLUSIVE · SUE SHARE UPDATE EXCLUSIVE · S SHARE · SRX SHARE ROW EXCLUSIVE · X EXCLUSIVE · AX ACCESS EXCLUSIVE · FKS FOR KEY SHARE · FS FOR SHARE · FNKU FOR NO KEY UPDATE · FU FOR UPDATE

12 lock modes

Table lock

ROW EXCLUSIVE

Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes.

INSERT · UPDATE · DELETE · COPY … FROM · MERGE · LOCK TABLE … IN ROW EXCLUSIVE MODE · UPDATE (non-key update) · UPDATE (key update) · MERGE … WHEN MATCHED THEN UPDATE (non-key) · MERGE … WHEN MATCHED THEN DELETE / UPDATE (key) · INSERT … ON CONFLICT DO UPDATE (non-key) · INSERT … ON CONFLICT DO UPDATE (key) · INSERT (foreign-key check) · UPDATE (foreign-key check) · COPY … FROM (foreign-key check) · MERGE (foreign-key check)

Details →

Table lock

EXCLUSIVE

Conflicts with the ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode allows only concurrent ACCESS SHARE locks, i.e., only reads from the table can proceed in parallel with a transaction holding this lock mode.

REFRESH MATERIALIZED VIEW CONCURRENTLY · LOCK TABLE … IN EXCLUSIVE MODE

Details →

Table lock

ACCESS EXCLUSIVE

Conflicts with locks of all modes (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE). This mode guarantees that the holder is the only transaction accessing the table in any way.

Details →

Row lock

FOR NO KEY UPDATE

Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

SELECT FOR NO KEY UPDATE · UPDATE (non-key update) · MERGE … WHEN MATCHED THEN UPDATE (non-key) · INSERT … ON CONFLICT DO UPDATE (non-key)

Details →

Row lock

FOR UPDATE

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. For further discussion see Data Consistency Checks at the Application Level.

SELECT FOR UPDATE · UPDATE (key update) · DELETE · MERGE … WHEN MATCHED THEN DELETE / UPDATE (key) · INSERT … ON CONFLICT DO UPDATE (key)

Details →

Commands and lock modes

A command can acquire different modes on different objects and at different stages. Links preserve the selected PostgreSQL 18 version.

SQL command	Associated modes	Object, phase and conditions
SELECT	ACCESS SHARE	Read referenced tables without a FOR locking clause. Source
COPY … TO	ACCESS SHARE	Read the source table. COPY (query) TO follows the locks required by that query. Source
LOCK TABLE … IN ACCESS SHARE MODE	ACCESS SHARE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
ALTER TABLE … NO INHERIT (parent table)	ACCESS SHARE	Take ACCESS SHARE on the parent while removing inheritance. The altered child separately takes ACCESS EXCLUSIVE. Source
SELECT FOR KEY SHARE	ROW SHARE	Lock the named table, in addition to the corresponding locks on selected rows. Source
SELECT FOR SHARE	ROW SHARE	Lock the named table, in addition to the corresponding locks on selected rows. Source
SELECT FOR NO KEY UPDATE	ROW SHARE	Lock the named table, in addition to the corresponding locks on selected rows. Source
SELECT FOR UPDATE	ROW SHARE	Lock the named table, in addition to the corresponding locks on selected rows. Source
INSERT (foreign-key check)	ROW SHARE	The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. Source
UPDATE (foreign-key check)	ROW SHARE	The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. Source
COPY … FROM (foreign-key check)	ROW SHARE	The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. Source
MERGE (foreign-key check)	ROW SHARE	The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. Source
LOCK TABLE … IN ROW SHARE MODE	ROW SHARE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
ALTER TABLE … VALIDATE CONSTRAINT (referenced table)	ROW SHARE	Foreign-key validation takes ROW SHARE on the referenced table and SHARE UPDATE EXCLUSIVE on the table being validated. Source
INSERT	ROW EXCLUSIVE	Lock the insertion target. Inserting a new row is not equivalent to locking an existing row FOR UPDATE. Source
UPDATE	ROW EXCLUSIVE	Lock the target table; affected existing rows also receive row locks. Source
DELETE	ROW EXCLUSIVE	Lock the target table; deleted rows also receive FOR UPDATE locks. Source
COPY … FROM	ROW EXCLUSIVE	Lock the target and insert new rows. This does not generally lock existing rows FOR UPDATE. Source
MERGE	ROW EXCLUSIVE	Lock the target table. Row locking depends on the INSERT, UPDATE or DELETE action actually taken. Source
LOCK TABLE … IN ROW EXCLUSIVE MODE	ROW EXCLUSIVE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
UPDATE (non-key update)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
UPDATE (key update)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
MERGE … WHEN MATCHED THEN UPDATE (non-key)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
MERGE … WHEN MATCHED THEN DELETE / UPDATE (key)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
INSERT … ON CONFLICT DO UPDATE (non-key)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
INSERT … ON CONFLICT DO UPDATE (key)	ROW EXCLUSIVE	This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. Source
INSERT (foreign-key check)	ROW EXCLUSIVE	The write target takes ROW EXCLUSIVE. The foreign-key check takes ROW SHARE and FOR KEY SHARE on a different object: the referenced table and its matching rows. Source
UPDATE (foreign-key check)	ROW EXCLUSIVE	The write target takes ROW EXCLUSIVE. The foreign-key check takes ROW SHARE and FOR KEY SHARE on a different object: the referenced table and its matching rows. Source
COPY … FROM (foreign-key check)	ROW EXCLUSIVE	The write target takes ROW EXCLUSIVE. The foreign-key check takes ROW SHARE and FOR KEY SHARE on a different object: the referenced table and its matching rows. Source
MERGE (foreign-key check)	ROW EXCLUSIVE	The write target takes ROW EXCLUSIVE. The foreign-key check takes ROW SHARE and FOR KEY SHARE on a different object: the referenced table and its matching rows. Source
VACUUM (without FULL)	SHARE UPDATE EXCLUSIVE	Ordinary vacuum holds this mode; truncating empty tail pages can briefly require ACCESS EXCLUSIVE. Source
ANALYZE	SHARE UPDATE EXCLUSIVE	Lock the table being analyzed. Source
CREATE INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	Lock the indexed table. Building proceeds across transactions and waits for old transactions where necessary. Source
CREATE STATISTICS	SHARE UPDATE EXCLUSIVE	Lock the table whose extended statistics are being defined. Source
COMMENT ON (relation object)	SHARE UPDATE EXCLUSIVE	Lock the object being commented on. Identical modes on different objects do not conflict. Source
DROP INDEX CONCURRENTLY	SHARE UPDATE EXCLUSIVE	Lock the parent table while removing the index across phases; unlike ordinary DROP INDEX this does not hold ACCESS EXCLUSIVE on the table. Source
REINDEX … CONCURRENTLY	SHARE UPDATE EXCLUSIVE	From PostgreSQL 12, session-level SHARE UPDATE EXCLUSIVE locks cover the rebuilt index and its table through the build, validation, swap and cleanup transactions. Source
LOCK TABLE … IN SHARE UPDATE EXCLUSIVE MODE	SHARE UPDATE EXCLUSIVE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
ALTER TABLE … ALTER COLUMN … SET STATISTICS	SHARE UPDATE EXCLUSIVE	Change the column statistics target. Source
ALTER TABLE … ALTER COLUMN … SET (n_distinct = …)	SHARE UPDATE EXCLUSIVE	Change column statistics options; RESET has the same requirement. Source
ALTER TABLE … CLUSTER ON	SHARE UPDATE EXCLUSIVE	Choose the index for future clustering; this command does not rewrite the table. Source
ALTER TABLE … SET WITHOUT CLUSTER	SHARE UPDATE EXCLUSIVE	Clear the clustering-index designation. Source
ALTER TABLE … VALIDATE CONSTRAINT	SHARE UPDATE EXCLUSIVE	Validate a constraint on this table. Foreign-key validation also accesses the referenced table. Source
ALTER TABLE … ATTACH PARTITION (parent table)	SHARE UPDATE EXCLUSIVE	The parent lock is extracted separately for every version. The table being attached additionally requires ACCESS EXCLUSIVE. From PostgreSQL 11 a default partition, if any, is also locked. Source
ALTER TABLE … DETACH PARTITION … FINALIZE (parent table)	SHARE UPDATE EXCLUSIVE	Complete an interrupted concurrent detach. The detached partition additionally requires ACCESS EXCLUSIVE. Source
ALTER TABLE … SET / RESET (fillfactor = …)	SHARE UPDATE EXCLUSIVE	Each storage parameter specifies its required mode. fillfactor can use SHARE UPDATE EXCLUSIVE. Source
ALTER TABLE … SET / RESET (autovacuum_* = …)	SHARE UPDATE EXCLUSIVE	Table autovacuum storage options use SHARE UPDATE EXCLUSIVE. Source
ALTER TABLE … SET / RESET (toast.autovacuum_* = …)	SHARE UPDATE EXCLUSIVE	These are TOAST autovacuum options. There is no generic SET TOAST subcommand; other options follow their own lock requirements. Source
ALTER TABLE … DETACH PARTITION … CONCURRENTLY (parent table)	SHARE UPDATE EXCLUSIVE	From PostgreSQL 14, concurrent detach uses two transactions. The parent holds SHARE UPDATE EXCLUSIVE and waits for transactions using the partition before finalizing. Source
ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, first phase)	SHARE UPDATE EXCLUSIVE	In the first phase, parent and partition hold SHARE UPDATE EXCLUSIVE. The partition lock is upgraded after committing and waiting. Source
ALTER INDEX … RENAME	SHARE UPDATE EXCLUSIVE	Lock the index being renamed. Source
ALTER TABLE … INHERIT (parent table)	SHARE UPDATE EXCLUSIVE	Take SHARE UPDATE EXCLUSIVE on the newly designated parent. The altered child separately takes ACCESS EXCLUSIVE. Source
CREATE INDEX (without CONCURRENTLY)	SHARE	Lock the indexed table, preventing concurrent writes. Source
REINDEX (parent table)	SHARE	Ordinary REINDEX takes SHARE on the table and ACCESS EXCLUSIVE on the index itself. The index lock is not an ACCESS EXCLUSIVE lock on the whole table. Source
LOCK TABLE … IN SHARE MODE	SHARE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
CREATE TRIGGER	SHARE ROW EXCLUSIVE	Lock the table that owns the trigger. Source
LOCK TABLE … IN SHARE ROW EXCLUSIVE MODE	SHARE ROW EXCLUSIVE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
ALTER TABLE … ENABLE / DISABLE TRIGGER	SHARE ROW EXCLUSIVE	Enable or disable triggers, including the applicable USER, ALL, REPLICA and ALWAYS forms. Source
ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referencing table)	SHARE ROW EXCLUSIVE	Both referencing and referenced tables take SHARE ROW EXCLUSIVE. NOT VALID skips the initial data check, not this lock. Source
ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referenced table)	SHARE ROW EXCLUSIVE	The referenced table also takes SHARE ROW EXCLUSIVE, not merely ACCESS SHARE. Source
REFRESH MATERIALIZED VIEW CONCURRENTLY	EXCLUSIVE	Lock the materialized view. Ordinary reads continue; only one refresh can run on that view at a time. Source
LOCK TABLE … IN EXCLUSIVE MODE	EXCLUSIVE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
REFRESH MATERIALIZED VIEW (without CONCURRENTLY)	ACCESS EXCLUSIVE	Lock the materialized view, blocking ordinary reads as well. Source
DROP TABLE	ACCESS EXCLUSIVE	Lock the table being dropped. Dependencies and CASCADE can require additional locks. Source
DROP INDEX (without CONCURRENTLY)	ACCESS EXCLUSIVE	Take ACCESS EXCLUSIVE on the parent table, blocking reads and writes; the index is locked too. Source
TRUNCATE	ACCESS EXCLUSIVE	Lock each table being truncated. Options and dependencies determine the affected table set. Source
CLUSTER	ACCESS EXCLUSIVE	Block concurrent reads and writes while rewriting the table. Source
VACUUM FULL	ACCESS EXCLUSIVE	Block concurrent reads and writes while rewriting the table. Source
VACUUM (tail truncation)	ACCESS EXCLUSIVE	Ordinary VACUUM briefly attempts ACCESS EXCLUSIVE when returning empty tail pages to the operating system. Where supported, disabling truncation avoids this phase. Source
REINDEX (index being rebuilt)	ACCESS EXCLUSIVE	Lock the index itself. Planning also accesses indexes, so ordinary REINDEX can still block queries. Source
LOCK TABLE … IN ACCESS EXCLUSIVE MODE	ACCESS EXCLUSIVE	Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. Source
ALTER TABLE … ADD COLUMN	ACCESS EXCLUSIVE	A rewrite depends on version and default expression. Avoiding the rewrite does not reduce the listed lock mode. Source
ALTER TABLE … DROP COLUMN	ACCESS EXCLUSIVE	Drop a column and change the table definition. Source
ALTER TABLE … ALTER COLUMN … SET / DROP DEFAULT	ACCESS EXCLUSIVE	Change the column default. Source
ALTER TABLE … ALTER COLUMN … TYPE	ACCESS EXCLUSIVE	Change the column type. Whether a rewrite is needed does not change this lock requirement. Source
ALTER TABLE … ALTER COLUMN … SET STORAGE	ACCESS EXCLUSIVE	Change the column storage strategy. Source
ALTER TABLE … ALTER COLUMN … SET COMPRESSION	ACCESS EXCLUSIVE	Change the column compression method. Source
ALTER TABLE … SET TABLESPACE	ACCESS EXCLUSIVE	Move the table to another tablespace. Source
ALTER TABLE … SET LOGGED / UNLOGGED	ACCESS EXCLUSIVE	Change table persistence. Source
ALTER TABLE … SET ACCESS METHOD	ACCESS EXCLUSIVE	Change the table access method. Source
ALTER TABLE … DROP CONSTRAINT	ACCESS EXCLUSIVE	Drop the constraint and any dependent index as applicable. Source
ALTER TABLE … ALTER CONSTRAINT	ACCESS EXCLUSIVE	Change constraint properties. Source
ALTER TABLE … ALTER COLUMN … SET NOT NULL	ACCESS EXCLUSIVE	Set a not-null constraint. Source
ALTER TABLE … ALTER COLUMN … DROP NOT NULL	ACCESS EXCLUSIVE	Drop a not-null constraint. Source
ALTER TABLE … ENABLE / DISABLE RULE	ACCESS EXCLUSIVE	Enable or disable rules. Source
ALTER TABLE … ENABLE / DISABLE ROW LEVEL SECURITY	ACCESS EXCLUSIVE	Enable or disable row-level security. Source
ALTER TABLE … FORCE / NO FORCE ROW LEVEL SECURITY	ACCESS EXCLUSIVE	Change whether the table owner is subject to row-level security. Source
ALTER TABLE … ALTER COLUMN … DROP EXPRESSION	ACCESS EXCLUSIVE	Drop a generated-column expression. Source
ALTER TABLE … ALTER COLUMN … SET EXPRESSION	ACCESS EXCLUSIVE	Set a generated-column expression. Source
ALTER TABLE … ALTER COLUMN … SET GENERATED / RESTART / SET sequence_option	ACCESS EXCLUSIVE	Change identity-column properties or its implicit sequence options. This is not a standalone SET SEQUENCE subcommand. Source
ALTER TABLE … ALTER COLUMN … ADD GENERATED … AS IDENTITY	ACCESS EXCLUSIVE	Add identity properties to a column. Source
ALTER TABLE … ALTER COLUMN … DROP IDENTITY	ACCESS EXCLUSIVE	Drop identity properties. Source
ALTER TABLE … INHERIT / NO INHERIT (child table)	ACCESS EXCLUSIVE	This entry describes the altered child table; the inheritance parent is locked separately. Source
ALTER TABLE … OWNER TO	ACCESS EXCLUSIVE	Change table ownership. Source
ALTER TABLE … REPLICA IDENTITY	ACCESS EXCLUSIVE	Change replica identity. Source
ALTER TABLE … RENAME	ACCESS EXCLUSIVE	Rename the table, column or constraint. Source
ALTER TABLE … ADD CONSTRAINT (CHECK / UNIQUE / PRIMARY KEY / EXCLUDE)	ACCESS EXCLUSIVE	Ordinary constraint creation. Adding a foreign key instead uses SHARE ROW EXCLUSIVE. Source
ALTER TABLE … ATTACH PARTITION (partition being attached)	ACCESS EXCLUSIVE	The table being attached requires ACCESS EXCLUSIVE. An appropriate CHECK constraint can avoid a validation scan but not this lock. Source
ALTER TABLE … ATTACH PARTITION (default partition)	ACCESS EXCLUSIVE	From PostgreSQL 11, an existing default partition requires ACCESS EXCLUSIVE. An appropriate CHECK constraint can avoid a scan but not the lock on the default partition. Descendant scanning follows validation needs. Source
ALTER TABLE … DETACH PARTITION (parent table)	ACCESS EXCLUSIVE	Ordinary detach without CONCURRENTLY. Source
ALTER TABLE … DETACH PARTITION (partition being detached)	ACCESS EXCLUSIVE	Ordinary detach takes ACCESS EXCLUSIVE on the partition being detached. Source
ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, final phase)	ACCESS EXCLUSIVE	The second phase takes ACCESS EXCLUSIVE on the detached partition. CONCURRENTLY is not allowed when a default partition exists. Source
ALTER INDEX … SET TABLESPACE	ACCESS EXCLUSIVE	Lock the index being moved, not the similarly named table. Source
ALTER INDEX … SET / RESET (fillfactor = …)	ACCESS EXCLUSIVE	Lock the index being modified. Source
ALTER INDEX … ATTACH PARTITION	ACCESS EXCLUSIVE	From PostgreSQL 11, modify the partitioned parent index and the child index being attached. Source
SELECT FOR KEY SHARE	FOR KEY SHARE	Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. Source
INSERT (foreign-key check)	FOR KEY SHARE	Checking a non-null foreign key locks matching rows in the referenced table FOR KEY SHARE. The check can run immediately or be deferred. Cascading actions require their own UPDATE or DELETE locks. Source
UPDATE (foreign-key check)	FOR KEY SHARE	Checking a non-null foreign key locks matching rows in the referenced table FOR KEY SHARE. The check can run immediately or be deferred. Cascading actions require their own UPDATE or DELETE locks. Source
COPY … FROM (foreign-key check)	FOR KEY SHARE	Checking a non-null foreign key locks matching rows in the referenced table FOR KEY SHARE. The check can run immediately or be deferred. Cascading actions require their own UPDATE or DELETE locks. Source
MERGE (foreign-key check)	FOR KEY SHARE	Checking a non-null foreign key locks matching rows in the referenced table FOR KEY SHARE. The check can run immediately or be deferred. Cascading actions require their own UPDATE or DELETE locks. Source
SELECT FOR SHARE	FOR SHARE	Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. Source
SELECT FOR NO KEY UPDATE	FOR NO KEY UPDATE	Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. Source
UPDATE (non-key update)	FOR NO KEY UPDATE	UPDATE uses this mode when it does not require FOR UPDATE. Key columns are columns of a unique index usable by a foreign key; partial and expression indexes do not qualify. Source
MERGE … WHEN MATCHED THEN UPDATE (non-key)	FOR NO KEY UPDATE	The UPDATE branch actually runs and does not need FOR UPDATE. Other MERGE actions can require different row locks. Source
INSERT … ON CONFLICT DO UPDATE (non-key)	FOR NO KEY UPDATE	Lock the existing conflicting row for UPDATE, even when the DO UPDATE WHERE condition excludes the update. Key-column changes determine the mode. Source
SELECT FOR UPDATE	FOR UPDATE	Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. Source
UPDATE (key update)	FOR UPDATE	Used when updating key columns of a unique index usable by a foreign key, excluding partial and expression indexes. Source
DELETE	FOR UPDATE	Lock rows being deleted, with ROW EXCLUSIVE on their table. Source
MERGE … WHEN MATCHED THEN DELETE / UPDATE (key)	FOR UPDATE	The matched action deletes a row or updates key columns. An insert-only action does not use this row lock. Source
INSERT … ON CONFLICT DO UPDATE (key)	FOR UPDATE	The conflict action updates key columns of an existing row. This is not a fixed row lock for every INSERT. Source

From compatibility to actual waits

Ordinary SELECT uses MVCC and takes ACCESS SHARE on referenced tables, without taking these row locks.
SELECT FOR also takes ROW SHARE; UPDATE and DELETE also take ROW EXCLUSIVE. A row operation can therefore wait on a table lock.
The matrix compares different transactions. Locks are normally held until transaction end; rolling back to a savepoint releases locks acquired since that savepoint.
Foreign keys, triggers, additional objects, lock queues and transaction snapshots can introduce waits beyond the listed modes. NOWAIT and SKIP LOCKED change waiting behavior.
Advisory locks, predicate locks and internal lightweight locks are separate mechanisms. See Explicit Locking and pg_locks.

Find blocking sessions

pg_blocking_pids() considers both holders of conflicting locks and conflicting requests ahead in the queue.

SELECT pid, usename, wait_event_type, wait_event,
       pg_blocking_pids(pid) AS blocking_pids, query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY pid;

Visibility of sessions and query text depends on privileges. Individual row locks normally do not appear in pg_locks; a row-lock wait often appears as waiting for the holding transaction.

Sources

PostgreSQL 18 · Sampled build REL_18_STABLE · 753057e340da. Conflicts, definitions and command associations refer to this fixed source build. SQL links retain the selected version.

Local English manual · Online manual

Pinned source files

Manual source: explicit locking 753057e340da
Table lock conflict definitions 753057e340da
Row lock mode definitions 753057e340da

Source collection date: 2026-09-27.