Locks in PostgreSQL | PostgreSQL Tutorials and Solutions

LOCK TABLE obtains a table-level lock, waiting if necessary for any conflicting locks to be released. There are 8 types of locks in PostgreSQL.

PostgreSQL Lock Syntax:

LOCK [ TABLE ] [ ONLY ] name [ * ] [, ...] [ IN lockmode MODE ] [ NOWAIT ]

where lockmode is one of:

Types of Locks in PostgreSQL:

ACCESS SHARE: This type of lock is taken by reads and conflicts only with
ACCESS EXCLUSIVE, which is set by DROP TABLE and the like. Practically, this
means that SELECT cannot start if a table is about to be dropped. This also
implies that DROP TABLE has to wait until a reading transaction is complete.
ROW SHARE: PostgreSQL takes this kind of lock in the case of SELECT FOR
UPDATE/SELECT FOR SHARE. It conflicts with EXCLUSIVE and ACCESS
EXCLUSIVE.
ROW EXCLUSIVE: This lock is taken by INSERT, UPDATE, and DELETE. It conflicts
with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE.
SHARE UPDATE EXCLUSIVE: This kind of lock is taken by CREATE INDEX
CONCURRENTLY, ANALYZE, ALTER TABLE, VALIDATE, and some other flavors of
ALTER TABLE as well as by VACUUM (not VACUUM FULL). It conflicts with the
SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, and
ACCESS EXCLUSIVE lock modes.
SHARE: When an index is created, SHARE locks will be set. It conflicts with ROW
EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE,
and ACCESS EXCLUSIVE.
SHARE ROW EXCLUSIVE: This one is set by CREATE TRIGGER and some forms of
ALTER TABLE and conflicts with everything but ACCESS SHARE.

EXCLUSIVE: This type of lock is by far the most restrictive one. It protects against
reads and writes alike. If this lock is taken by a transaction, nobody else can read
or write to the table affected.
ACCESS EXCLUSIVE: This lock prevents concurrent transactions from reading
and writing.