PostgreSQL 版本定义：18
当前阅读 PG 18·选择有来源记录的版本
选择命令可突出显示关联锁模式；实际锁取决于对象、选项和执行阶段。
行表示请求的锁，列表示其他事务已持有的同对象锁。表锁名称中的 ROW 不代表行锁。
| 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.
缩进项同时取得上级表锁及所列行锁。这些显示组合保留原始 12 种锁模式，不代表锁强度排序。
Select a cell to compare requested and held modes. Use the arrow keys to move through the matrix.
Conflicts with the ACCESS EXCLUSIVE lock mode only. The SELECT command acquires a lock of this mode on referenced tables. In general, any query that only reads a table and does not modify it will acquire this lock mode.
SELECT · COPY … TO · LOCK TABLE … IN ACCESS SHARE MODE · ALTER TABLE … NO INHERIT (parent table) ·
Conflicts with the EXCLUSIVE and ACCESS EXCLUSIVE lock modes. The SELECT command acquires a lock of this mode on all tables on which one of the FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, or FOR KEY SHARE options is specified (in addition to ACCESS SHARE locks on any other tables that are referenced without any explicit FOR ... locking option).
SELECT FOR KEY SHARE · SELECT FOR SHARE · SELECT FOR NO KEY UPDATE · SELECT FOR UPDATE · INSERT (foreign-key check) · UPDATE (foreign-key check) · COPY … FROM (foreign-key check) · MERGE (foreign-key check) · LOCK TABLE … IN ROW SHARE MODE · ALTER TABLE … VALIDATE CONSTRAINT (referenced table) ·
Conflicts with the SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. The commands UPDATE, DELETE, INSERT, and MERGE acquire this lock mode on the target table (in addition to ACCESS SHARE locks on any other referenced tables). In general, this lock mode will be acquired by any command that modifies data in a table.
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) ·
Conflicts with the SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent schema changes and VACUUM runs. Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, REINDEX CONCURRENTLY, and certain ALTER INDEX and ALTER TABLE variants (for full details see the documentation of these commands).
VACUUM (without FULL) · ANALYZE · CREATE INDEX CONCURRENTLY · CREATE STATISTICS · COMMENT ON (relation object) · DROP INDEX CONCURRENTLY · REINDEX … CONCURRENTLY · LOCK TABLE … IN SHARE UPDATE EXCLUSIVE MODE · ALTER TABLE … ALTER COLUMN … SET STATISTICS · ALTER TABLE … ALTER COLUMN … SET (n_distinct = …) · ALTER TABLE … CLUSTER ON · ALTER TABLE … SET WITHOUT CLUSTER · ALTER TABLE … VALIDATE CONSTRAINT · ALTER TABLE … ATTACH PARTITION (parent table) · ALTER TABLE … DETACH PARTITION … FINALIZE (parent table) · ALTER TABLE … SET / RESET (fillfactor = …) · ALTER TABLE … SET / RESET (autovacuum_* = …) · ALTER TABLE … SET / RESET (toast.autovacuum_* = …) · ALTER TABLE … DETACH PARTITION … CONCURRENTLY (parent table) · ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, first phase) · ALTER INDEX … RENAME · ALTER TABLE … INHERIT (parent table) ·
Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes. Acquired by CREATE INDEX (without CONCURRENTLY).
CREATE INDEX (without CONCURRENTLY) · REINDEX (parent table) · LOCK TABLE … IN SHARE MODE ·
Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This mode protects a table against concurrent data changes, and is self-exclusive so that only one session can hold it at a time. Acquired by CREATE TRIGGER and some forms of ALTER TABLE.
CREATE TRIGGER · LOCK TABLE … IN SHARE ROW EXCLUSIVE MODE · ALTER TABLE … ENABLE / DISABLE TRIGGER · ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referencing table) · ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referenced table) ·
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. Acquired by REFRESH MATERIALIZED VIEW CONCURRENTLY.
REFRESH MATERIALIZED VIEW CONCURRENTLY · LOCK TABLE … IN EXCLUSIVE MODE ·
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. Acquired by the DROP TABLE, TRUNCATE, REINDEX, CLUSTER, VACUUM FULL, and REFRESH MATERIALIZED VIEW (without CONCURRENTLY) commands. Many forms of ALTER INDEX and ALTER TABLE also acquire a lock at this level. This is also the default lock mode for LOCK TABLE statements that do not specify a mode explicitly.
REFRESH MATERIALIZED VIEW (without CONCURRENTLY) · DROP TABLE · DROP INDEX (without CONCURRENTLY) · TRUNCATE · CLUSTER · VACUUM FULL · VACUUM (tail truncation) · REINDEX (index being rebuilt) · LOCK TABLE … IN ACCESS EXCLUSIVE MODE · ALTER TABLE … ADD COLUMN · ALTER TABLE … DROP COLUMN · ALTER TABLE … ALTER COLUMN … SET / DROP DEFAULT · ALTER TABLE … ALTER COLUMN … TYPE · ALTER TABLE … ALTER COLUMN … SET STORAGE · ALTER TABLE … ALTER COLUMN … SET COMPRESSION · ALTER TABLE … SET TABLESPACE · ALTER TABLE … SET LOGGED / UNLOGGED · ALTER TABLE … SET ACCESS METHOD · ALTER TABLE … DROP CONSTRAINT · ALTER TABLE … ALTER CONSTRAINT · ALTER TABLE … ALTER COLUMN … SET NOT NULL · ALTER TABLE … ALTER COLUMN … DROP NOT NULL · ALTER TABLE … ENABLE / DISABLE RULE · ALTER TABLE … ENABLE / DISABLE ROW LEVEL SECURITY · ALTER TABLE … FORCE / NO FORCE ROW LEVEL SECURITY · ALTER TABLE … ALTER COLUMN … DROP EXPRESSION · ALTER TABLE … ALTER COLUMN … SET EXPRESSION · ALTER TABLE … ALTER COLUMN … SET GENERATED / RESTART / SET sequence_option · ALTER TABLE … ALTER COLUMN … ADD GENERATED … AS IDENTITY · ALTER TABLE … ALTER COLUMN … DROP IDENTITY · ALTER TABLE … INHERIT / NO INHERIT (child table) · ALTER TABLE … OWNER TO · ALTER TABLE … REPLICA IDENTITY · ALTER TABLE … RENAME · ALTER TABLE … ADD CONSTRAINT (CHECK / UNIQUE / PRIMARY KEY / EXCLUDE) · ALTER TABLE … ATTACH PARTITION (partition being attached) · ALTER TABLE … ATTACH PARTITION (default partition) · ALTER TABLE … DETACH PARTITION (parent table) · ALTER TABLE … DETACH PARTITION (partition being detached) · ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, final phase) · ALTER INDEX … SET TABLESPACE · ALTER INDEX … SET / RESET (fillfactor = …) · ALTER INDEX … ATTACH PARTITION ·
Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.
SELECT FOR KEY SHARE · INSERT (foreign-key check) · UPDATE (foreign-key check) · COPY … FROM (foreign-key check) · MERGE (foreign-key check) ·
Behaves similarly to FOR NO KEY UPDATE, except that it acquires a shared lock rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.
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) ·
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. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values of certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.
SELECT FOR UPDATE · UPDATE (key update) · DELETE · MERGE … WHEN MATCHED THEN DELETE / UPDATE (key) · INSERT … ON CONFLICT DO UPDATE (key) ·
命令可在不同对象和执行阶段取得不同锁；链接保留所选 PostgreSQL 版本。
120 条命令关联
| SQL 命令 | 关联模式 | 对象、阶段与条件 |
|---|---|---|
| SELECT | Read referenced tables without a FOR locking clause. 来源 | |
| COPY … TO | Read the source table. COPY (query) TO follows the locks required by that query. 来源 | |
| LOCK TABLE … IN ACCESS SHARE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … NO INHERIT (parent table) | Take ACCESS SHARE on the parent while removing inheritance. The altered child separately takes ACCESS EXCLUSIVE. 来源 | |
| SELECT FOR KEY SHARE | Lock the named table, in addition to the corresponding locks on selected rows. 来源 | |
| SELECT FOR SHARE | Lock the named table, in addition to the corresponding locks on selected rows. 来源 | |
| SELECT FOR NO KEY UPDATE | Lock the named table, in addition to the corresponding locks on selected rows. 来源 | |
| SELECT FOR UPDATE | Lock the named table, in addition to the corresponding locks on selected rows. 来源 | |
| INSERT (foreign-key check) | The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. 来源 | |
| UPDATE (foreign-key check) | The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. 来源 | |
| COPY … FROM (foreign-key check) | The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. 来源 | |
| MERGE (foreign-key check) | The foreign-key check runs SELECT FOR KEY SHARE on the referenced table, taking ROW SHARE there. The writing target separately holds ROW EXCLUSIVE. 来源 | |
| LOCK TABLE … IN ROW SHARE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … VALIDATE CONSTRAINT (referenced table) | Foreign-key validation takes ROW SHARE on the referenced table and SHARE UPDATE EXCLUSIVE on the table being validated. 来源 | |
| INSERT | Lock the insertion target. Inserting a new row is not equivalent to locking an existing row FOR UPDATE. 来源 | |
| UPDATE | Lock the target table; affected existing rows also receive row locks. 来源 | |
| DELETE | Lock the target table; deleted rows also receive FOR UPDATE locks. 来源 | |
| COPY … FROM | Lock the target and insert new rows. This does not generally lock existing rows FOR UPDATE. 来源 | |
| MERGE | Lock the target table. Row locking depends on the INSERT, UPDATE or DELETE action actually taken. 来源 | |
| LOCK TABLE … IN ROW EXCLUSIVE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| UPDATE (non-key update) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| UPDATE (key update) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| MERGE … WHEN MATCHED THEN UPDATE (non-key) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| MERGE … WHEN MATCHED THEN DELETE / UPDATE (key) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| INSERT … ON CONFLICT DO UPDATE (non-key) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| INSERT … ON CONFLICT DO UPDATE (key) | This write variant takes ROW EXCLUSIVE on its target table. Existing-row locks depend on the action actually performed. 来源 | |
| INSERT (foreign-key check) | 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. 来源 | |
| UPDATE (foreign-key check) | 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. 来源 | |
| COPY … FROM (foreign-key check) | 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. 来源 | |
| MERGE (foreign-key check) | 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. 来源 | |
| VACUUM (without FULL) | Ordinary vacuum holds this mode; truncating empty tail pages can briefly require ACCESS EXCLUSIVE. 来源 | |
| ANALYZE | Lock the table being analyzed. 来源 | |
| CREATE INDEX CONCURRENTLY | Lock the indexed table. Building proceeds across transactions and waits for old transactions where necessary. 来源 | |
| CREATE STATISTICS | Lock the table whose extended statistics are being defined. 来源 | |
| COMMENT ON (relation object) | Lock the object being commented on. Identical modes on different objects do not conflict. 来源 | |
| DROP INDEX CONCURRENTLY | Lock the parent table while removing the index across phases; unlike ordinary DROP INDEX this does not hold ACCESS EXCLUSIVE on the table. 来源 | |
| REINDEX … CONCURRENTLY | From PostgreSQL 12, session-level SHARE UPDATE EXCLUSIVE locks cover the rebuilt index and its table through the build, validation, swap and cleanup transactions. 来源 | |
| LOCK TABLE … IN SHARE UPDATE EXCLUSIVE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET STATISTICS | Change the column statistics target. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET (n_distinct = …) | Change column statistics options; RESET has the same requirement. 来源 | |
| ALTER TABLE … CLUSTER ON | Choose the index for future clustering; this command does not rewrite the table. 来源 | |
| ALTER TABLE … SET WITHOUT CLUSTER | Clear the clustering-index designation. 来源 | |
| ALTER TABLE … VALIDATE CONSTRAINT | Validate a constraint on this table. Foreign-key validation also accesses the referenced table. 来源 | |
| ALTER TABLE … ATTACH PARTITION (parent table) | 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. 来源 | |
| ALTER TABLE … DETACH PARTITION … FINALIZE (parent table) | Complete an interrupted concurrent detach. The detached partition additionally requires ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … SET / RESET (fillfactor = …) | Each storage parameter specifies its required mode. fillfactor can use SHARE UPDATE EXCLUSIVE. 来源 | |
| ALTER TABLE … SET / RESET (autovacuum_* = …) | Table autovacuum storage options use SHARE UPDATE EXCLUSIVE. 来源 | |
| ALTER TABLE … SET / RESET (toast.autovacuum_* = …) | These are TOAST autovacuum options. There is no generic SET TOAST subcommand; other options follow their own lock requirements. 来源 | |
| ALTER TABLE … DETACH PARTITION … CONCURRENTLY (parent table) | From PostgreSQL 14, concurrent detach uses two transactions. The parent holds SHARE UPDATE EXCLUSIVE and waits for transactions using the partition before finalizing. 来源 | |
| ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, first phase) | In the first phase, parent and partition hold SHARE UPDATE EXCLUSIVE. The partition lock is upgraded after committing and waiting. 来源 | |
| ALTER INDEX … RENAME | Lock the index being renamed. 来源 | |
| ALTER TABLE … INHERIT (parent table) | Take SHARE UPDATE EXCLUSIVE on the newly designated parent. The altered child separately takes ACCESS EXCLUSIVE. 来源 | |
| CREATE INDEX (without CONCURRENTLY) | Lock the indexed table, preventing concurrent writes. 来源 | |
| REINDEX (parent table) | 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. 来源 | |
| LOCK TABLE … IN SHARE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| CREATE TRIGGER | Lock the table that owns the trigger. 来源 | |
| LOCK TABLE … IN SHARE ROW EXCLUSIVE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … ENABLE / DISABLE TRIGGER | Enable or disable triggers, including the applicable USER, ALL, REPLICA and ALWAYS forms. 来源 | |
| ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referencing table) | Both referencing and referenced tables take SHARE ROW EXCLUSIVE. NOT VALID skips the initial data check, not this lock. 来源 | |
| ALTER TABLE … ADD FOREIGN KEY [NOT VALID] (referenced table) | The referenced table also takes SHARE ROW EXCLUSIVE, not merely ACCESS SHARE. 来源 | |
| REFRESH MATERIALIZED VIEW CONCURRENTLY | Lock the materialized view. Ordinary reads continue; only one refresh can run on that view at a time. 来源 | |
| LOCK TABLE … IN EXCLUSIVE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| REFRESH MATERIALIZED VIEW (without CONCURRENTLY) | Lock the materialized view, blocking ordinary reads as well. 来源 | |
| DROP TABLE | Lock the table being dropped. Dependencies and CASCADE can require additional locks. 来源 | |
| DROP INDEX (without CONCURRENTLY) | Take ACCESS EXCLUSIVE on the parent table, blocking reads and writes; the index is locked too. 来源 | |
| TRUNCATE | Lock each table being truncated. Options and dependencies determine the affected table set. 来源 | |
| CLUSTER | Block concurrent reads and writes while rewriting the table. 来源 | |
| VACUUM FULL | Block concurrent reads and writes while rewriting the table. 来源 | |
| VACUUM (tail truncation) | Ordinary VACUUM briefly attempts ACCESS EXCLUSIVE when returning empty tail pages to the operating system. Where supported, disabling truncation avoids this phase. 来源 | |
| REINDEX (index being rebuilt) | Lock the index itself. Planning also accesses indexes, so ordinary REINDEX can still block queries. 来源 | |
| LOCK TABLE … IN ACCESS EXCLUSIVE MODE | Request this table mode explicitly. Omitting IN MODE defaults to ACCESS EXCLUSIVE. 来源 | |
| ALTER TABLE … ADD COLUMN | A rewrite depends on version and default expression. Avoiding the rewrite does not reduce the listed lock mode. 来源 | |
| ALTER TABLE … DROP COLUMN | Drop a column and change the table definition. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET / DROP DEFAULT | Change the column default. 来源 | |
| ALTER TABLE … ALTER COLUMN … TYPE | Change the column type. Whether a rewrite is needed does not change this lock requirement. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET STORAGE | Change the column storage strategy. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET COMPRESSION | Change the column compression method. 来源 | |
| ALTER TABLE … SET TABLESPACE | Move the table to another tablespace. 来源 | |
| ALTER TABLE … SET LOGGED / UNLOGGED | Change table persistence. 来源 | |
| ALTER TABLE … SET ACCESS METHOD | Change the table access method. 来源 | |
| ALTER TABLE … DROP CONSTRAINT | Drop the constraint and any dependent index as applicable. 来源 | |
| ALTER TABLE … ALTER CONSTRAINT | Change constraint properties. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET NOT NULL | Set a not-null constraint. 来源 | |
| ALTER TABLE … ALTER COLUMN … DROP NOT NULL | Drop a not-null constraint. 来源 | |
| ALTER TABLE … ENABLE / DISABLE RULE | Enable or disable rules. 来源 | |
| ALTER TABLE … ENABLE / DISABLE ROW LEVEL SECURITY | Enable or disable row-level security. 来源 | |
| ALTER TABLE … FORCE / NO FORCE ROW LEVEL SECURITY | Change whether the table owner is subject to row-level security. 来源 | |
| ALTER TABLE … ALTER COLUMN … DROP EXPRESSION | Drop a generated-column expression. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET EXPRESSION | Set a generated-column expression. 来源 | |
| ALTER TABLE … ALTER COLUMN … SET GENERATED / RESTART / SET sequence_option | Change identity-column properties or its implicit sequence options. This is not a standalone SET SEQUENCE subcommand. 来源 | |
| ALTER TABLE … ALTER COLUMN … ADD GENERATED … AS IDENTITY | Add identity properties to a column. 来源 | |
| ALTER TABLE … ALTER COLUMN … DROP IDENTITY | Drop identity properties. 来源 | |
| ALTER TABLE … INHERIT / NO INHERIT (child table) | This entry describes the altered child table; the inheritance parent is locked separately. 来源 | |
| ALTER TABLE … OWNER TO | Change table ownership. 来源 | |
| ALTER TABLE … REPLICA IDENTITY | Change replica identity. 来源 | |
| ALTER TABLE … RENAME | Rename the table, column or constraint. 来源 | |
| ALTER TABLE … ADD CONSTRAINT (CHECK / UNIQUE / PRIMARY KEY / EXCLUDE) | Ordinary constraint creation. Adding a foreign key instead uses SHARE ROW EXCLUSIVE. 来源 | |
| ALTER TABLE … ATTACH PARTITION (partition being attached) | The table being attached requires ACCESS EXCLUSIVE. An appropriate CHECK constraint can avoid a validation scan but not this lock. 来源 | |
| ALTER TABLE … ATTACH PARTITION (default partition) | 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. 来源 | |
| ALTER TABLE … DETACH PARTITION (parent table) | Ordinary detach without CONCURRENTLY. 来源 | |
| ALTER TABLE … DETACH PARTITION (partition being detached) | Ordinary detach takes ACCESS EXCLUSIVE on the partition being detached. 来源 | |
| ALTER TABLE … DETACH PARTITION … CONCURRENTLY (partition, final phase) | The second phase takes ACCESS EXCLUSIVE on the detached partition. CONCURRENTLY is not allowed when a default partition exists. 来源 | |
| ALTER INDEX … SET TABLESPACE | Lock the index being moved, not the similarly named table. 来源 | |
| ALTER INDEX … SET / RESET (fillfactor = …) | Lock the index being modified. 来源 | |
| ALTER INDEX … ATTACH PARTITION | From PostgreSQL 11, modify the partitioned parent index and the child index being attached. 来源 | |
| SELECT FOR KEY SHARE | Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. 来源 | |
| INSERT (foreign-key check) | 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. 来源 | |
| UPDATE (foreign-key check) | 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. 来源 | |
| COPY … FROM (foreign-key check) | 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. 来源 | |
| MERGE (foreign-key check) | 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. 来源 | |
| SELECT FOR SHARE | Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. 来源 | |
| SELECT FOR NO KEY UPDATE | Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. 来源 | |
| UPDATE (non-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. 来源 | |
| MERGE … WHEN MATCHED THEN UPDATE (non-key) | The UPDATE branch actually runs and does not need FOR UPDATE. Other MERGE actions can require different row locks. 来源 | |
| INSERT … ON CONFLICT DO UPDATE (non-key) | Lock the existing conflicting row for UPDATE, even when the DO UPDATE WHERE condition excludes the update. Key-column changes determine the mode. 来源 | |
| SELECT FOR UPDATE | Lock selected rows, with ROW SHARE on the target table. NOWAIT and SKIP LOCKED affect waiting, not mode compatibility. 来源 | |
| UPDATE (key update) | Used when updating key columns of a unique index usable by a foreign key, excluding partial and expression indexes. 来源 | |
| DELETE | Lock rows being deleted, with ROW EXCLUSIVE on their table. 来源 | |
| MERGE … WHEN MATCHED THEN DELETE / UPDATE (key) | The matched action deletes a row or updates key columns. An insert-only action does not use this row lock. 来源 | |
| INSERT … ON CONFLICT DO UPDATE (key) | The conflict action updates key columns of an existing row. This is not a fixed row lock for every INSERT. 来源 |
没有匹配命令，请尝试更短的关键词。
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.
PostgreSQL 18 · 753057e340da8f22e57c9a409ea7a235fd6a46bd
来源采集日期: 2026-09-27