PostgreSQL Lock Conflicts

UPDATE (KEYS)

The UPDATE (KEYS) command acquires RowExclusiveLock table lock and a FORUPDATE row lock. Following are the locks UPDATE (KEYS) acquires, the commands that are allowed to run concurrently with its lock RowExclusiveLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by UPDATE (KEYS)

1. RowExclusiveLock (table)
2. FORUPDATE (row)

Commands concurrently allowed on the table with UPDATE (KEYS)

e.g. If tx1 does a UPDATE (KEYS) on the table then tx2 is allowed to do any of the following commands concurrently on the same table without being blocked. Some DMLs executed on the same rows may block, read more below.

1. SELECT
2. SELECT FOR UPDATE
3. SELECT FOR SHARE
4. SELECT FOR NO KEY UPDATE
5. SELECT FOR KEY SHARE
6. COPY TO
7. INSERT
8. UPDATE (NO KEYS)
9. UPDATE (KEYS)
10. DELETE
11. COPY FROM
12. MERGE
13. VACUUM
14. REINDEX CONCURRENTLY
15. DROP INDEX CONCURRENTLY
16. CREATE STATISTICS
17. CREATE INDEX CONCURRENTLY
18. COMMENT ON
19. ANALYZE
20. ALTER TABLE VALIDATE CONSTRAINT
21. ALTER TABLE SET WITHOUT CLUSTER
22. ALTER TABLE SET TOAST
23. ALTER TABLE SET STATISTICS
24. ALTER TABLE SET N_DISTINCT
25. ALTER TABLE SET FILLFACTOR
26. ALTER TABLE SET AUTOVACUUUM
27. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
28. ALTER TABLE CLUSTER ON
29. ALTER TABLE ATTACH PARTITION (PARENT)
30. ALTER TABLE ADD FOREIGN KEY (PARENT)
31. ALTER INDEX (RENAME)

The following will conflict with UPDATE (KEYS) when executed on a row

e.g. If tx1 executes a UPDATE (KEYS) on a row then concurrently tx2 does any of the following commands on the same row, tx2 will block.

1. SELECT FOR UPDATE
2. SELECT FOR SHARE
3. SELECT FOR NO KEY UPDATE
4. SELECT FOR KEY SHARE
5. UPDATE (NO KEYS)
6. UPDATE (KEYS)
7. DELETE
8. COPY FROM
9. MERGE

Commands conflicting with UPDATE (KEYS) on the table

e.g. If tx1 does a UPDATE (KEYS) on the table then concurrently tx2 tries to do any of the following commands on the same table, tx2 will be blocked. Conversely, if tx1 executes any of the following commands and then tx2 concurrently tries to execute UPDATE (KEYS), tx2 will block.

1. VACUUM FULL
2. TRUNCATE
3. REINDEX
4. REFRESH MATERIALIZED VIEW
5. REFRESH MATERIALIZED VIEW CONCURRENTLY
6. DROP TABLE
7. DROP INDEX
8. CREATE TRIGGER
9. CREATE INDEX
10. CLUSTER
11. ALTER TABLE SET/DROP DEFAULT
12. ALTER TABLE SET TABLESPACE
13. ALTER TABLE SET STORAGE
14. ALTER TABLE SET SEQUENCE
15. ALTER TABLE SET DATA TYPE
16. ALTER TABLE SET COMPRESSION
17. ALTER TABLE RESET STORAGE
18. ALTER TABLE RENAME
19. ALTER TABLE INHERIT PARENT
20. ALTER TABLE ENABLE/DISABLE TRIGGER
21. ALTER TABLE ENABLE/DISABLE RULE
22. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
23. ALTER TABLE DROP EXPRESSION
24. ALTER TABLE DROP CONSTRAINT
25. ALTER TABLE DROP COLUMN
26. ALTER TABLE DETACH PARTITION (PARENT)
27. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
28. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
29. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
30. ALTER TABLE ALTER CONSTRAINT
31. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
32. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
33. ALTER TABLE ADD FOREIGN KEY (CHILD)
34. ALTER TABLE ADD COLUMN
35. ALTER TABLE ADD CONSTRAINT
36. ALTER INDEX SET TABLESPACE
37. ALTER INDEX SET FILLFACTOR
38. ALTER INDEX ATTACH PARTITION