Practical PostgreSQL

Lock

Locks a table within a transaction.

Synopsis

LOCK [ TABLE ] name
LOCK [ TABLE ] name IN lock_mode

lock_mode ::= { [ ROW | ACCESS ] { SHARE | EXCLUSIVE } |
              SHARE ROW EXCLUSIVE } MODE

Parameters

name

The name of the table you intend to lock.

lock_mode

There are seven valid lock modes that may be combined from the available keywords. Here they are, in order from least restrictive to most restrictive, along with the commands and modes they block:

ACCESS SHARE MODE

The ACCESS SHARE MODE lock is acquired automatically by a SELECT statement on the table or tables it retrieves from. This mode blocks ALTER TABLE, DROP TABLE, and VACUUM commands on the table on which it is placed.

This mode also blocks concurrent ACCESS EXCLUSIVE MODE locks from being acquired on the same table.

ROW SHARE MODE

The ROW SHARE MODE lock is acquired automatically by a SELECT statement that has a FOR UPDATE clause. It blocks ALTER TABLE, DROP TABLE, and VACUUM commands on the table on which it is acquired.

This mode also blocks concurrent EXCLUSIVE MODE and ACCESS EXCLUSIVE MODE locks from being acquired on the same table.

ROW EXCLUSIVE MODE

The ROW EXCLUSIVE MODE lock is acquired automatically by an UPDATE, INSERT, or DELETE command. This mode blocks ALTER TABLE, DROP TABLE, VACUUM, and CREATE INDEX commands.

This mode also blocks concurrent SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE, and ACCESS EXCLUSIVE MODE locks from being acquired on the same table.

SHARE MODE

The SHARE MODE lock is acquired automatically by ...