Table locking modes

Table locks are often acquired automatically, but they can also be acquired explicitly with the LOCK command. The following is the list of locking modes:

  • ACCESS SHARE: This mode is acquired by the SELECT statement.
  • ROW SHARE: The SELECT FOR UPDATE and SELECT FOR SHARE commands acquire this lock.
  • ROW EXCLUSIVE: The statements UPDATE, DELETE, and INSERT acquire this lock mode.
  • SHARE UPDATE EXCLUSIVE: This mode is used to protect a table against concurrent schema changes. Acquired by VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICSALTER TABLE VALIDATE, and other ALTER TABLE variants.
  • SHARE: This mode is used to protect a table against concurrent data changes. Acquired by CREATE INDEX (without ...

Get Learning PostgreSQL 11 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.