PostgreSQL Lock Conflicts

Database engineering course | @hnasr | ☕

INSERT

The INSERT command acquires RowExclusiveLock table lock . Following are the locks INSERT 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 INSERT

  1. RowExclusiveLock (table)

Commands concurrently allowed on the table with INSERT

e.g. If tx1 does a INSERT 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)

      Commands conflicting with INSERT on the table

      e.g. If tx1 does a INSERT 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 INSERT, 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