Lock Modes in PostgreSQL

There are several lock modes in PostgreSQL.

ACCESS SHARE — Acquired by queries that only read from a table but do not modify it. Typically, this is a select query.

ROW SHARE — Acquired by the SELECT FOR UPDATE and SELECT FOR SHARE queries.

ROW EXCLUSIVE — Acquired by queries that modify the data in a table. Typically, update, delete, and insert queries.

SHARE UPDATE EXCLUSIVE — Acquired by vacuum, concurrent indexes, statistics, and some variants of the alter table commands. This mode protects a table against concurrent schema changes and vacuum runs.

SHARE — Acquired by create index that is not executed in concurrent mode. This mode protects a table against concurrent data changes.

SHARE ROW EXCLUSIVE — 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 collation, create trigger, and many forms of alter table.

EXCLUSIVE — 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.

ACCESS EXCLUSIVE — This mode guarantees that the holder is the only transaction accessing the table in any way. Acquired by DROP TABLE, ALTER TABLE, VACUUM FULl commands.

Explicit locking

In the previous sections, we have learned that all of the typical SQL commands acquire some sort of lock implicitly. We can also acquire locks explicitly with the WITH LOCK statement.