PostgreSQL Lock Conflicts

SELECT FOR NO KEY UPDATE

The SELECT FOR NO KEY UPDATE command acquires RowShareLock table lock and a FORNOKEYUPDATE row lock. Following are the locks SELECT FOR NO KEY UPDATE acquires, the commands that are allowed to run concurrently with its lock RowShareLock and the commands that conflict with it. The list also includes the conflicting row locks if applicable

Locks acquired by SELECT FOR NO KEY UPDATE

1. RowShareLock (table)
2. FORNOKEYUPDATE (row)

Commands concurrently allowed on the table with SELECT FOR NO KEY UPDATE

e.g. If tx1 does a SELECT FOR NO KEY UPDATE 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 TRIGGER
17. CREATE STATISTICS
18. CREATE INDEX
19. CREATE INDEX CONCURRENTLY
20. COMMENT ON
21. ANALYZE
22. ALTER TABLE VALIDATE CONSTRAINT
23. ALTER TABLE SET WITHOUT CLUSTER
24. ALTER TABLE SET TOAST
25. ALTER TABLE SET STATISTICS
26. ALTER TABLE SET N_DISTINCT
27. ALTER TABLE SET FILLFACTOR
28. ALTER TABLE SET AUTOVACUUUM
29. ALTER TABLE ENABLE/DISABLE TRIGGER
30. ALTER TABLE DETACH PARTITION CONCURRENTLY (PARENT)
31. ALTER TABLE CLUSTER ON
32. ALTER TABLE ATTACH PARTITION (PARENT)
33. ALTER TABLE ADD FOREIGN KEY NOT VALID (PARENT)
34. ALTER TABLE ADD FOREIGN KEY NOT VALID (CHILD)
35. ALTER TABLE ADD FOREIGN KEY (PARENT)
36. ALTER TABLE ADD FOREIGN KEY (CHILD)
37. ALTER INDEX (RENAME)

From the above commands, only the following are allowed to execute concurrently on the same row with SELECT FOR NO KEY UPDATE without conflicting.

e.g. If tx1 executes a SELECT FOR NO KEY UPDATE on a row then concurrently tx2 is allowed to execute any of the following commands even on the same row .

1. SELECT FOR KEY SHARE

The following will conflict with SELECT FOR NO KEY UPDATE when executed on a row

e.g. If tx1 executes a SELECT FOR NO KEY UPDATE 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. UPDATE (NO KEYS)
5. UPDATE (KEYS)
6. DELETE
7. COPY FROM
8. MERGE

Commands conflicting with SELECT FOR NO KEY UPDATE on the table

e.g. If tx1 does a SELECT FOR NO KEY UPDATE 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 SELECT FOR NO KEY UPDATE, 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. CLUSTER
9. ALTER TABLE SET/DROP DEFAULT
10. ALTER TABLE SET TABLESPACE
11. ALTER TABLE SET STORAGE
12. ALTER TABLE SET SEQUENCE
13. ALTER TABLE SET DATA TYPE
14. ALTER TABLE SET COMPRESSION
15. ALTER TABLE RESET STORAGE
16. ALTER TABLE RENAME
17. ALTER TABLE INHERIT PARENT
18. ALTER TABLE ENABLE/DISABLE RULE
19. ALTER TABLE ENABLE/DISABLE ROW LEVEL SECURITY
20. ALTER TABLE DROP EXPRESSION
21. ALTER TABLE DROP CONSTRAINT
22. ALTER TABLE DROP COLUMN
23. ALTER TABLE DETACH PARTITION (PARENT)
24. ALTER TABLE DETACH PARTITION (TARGET/DEFAULT)
25. ALTER TABLE DETACH PARTITION CONCURRENTLY (TARGET/DEFAULT)
26. ALTER TABLE ATTACH PARTITION (TARGET/DEFAULT)
27. ALTER TABLE ALTER CONSTRAINT
28. ALTER TABLE ADD COLUMN
29. ALTER TABLE ADD CONSTRAINT
30. ALTER INDEX SET TABLESPACE
31. ALTER INDEX SET FILLFACTOR
32. ALTER INDEX ATTACH PARTITION