LOCK TABLE

Use the LOCK TABLE command to lock a table in the current database. The lock mode specifies the type of lock to acquire.

The LOCK TABLE command succeeds when a lock of the specified mode can be acquired on the table. The command waits as long as needed to get the lock, unless you specify the NOWAIT option, which causes the command to abort the transaction if it cannot acquire the lock immediately.

A lock is held until the transaction commits or aborts. If LOCK TABLE is issued implicitly (that is, not in a BEGIN/COMMIT pair), the command acquires the lock when permitted and then releases the lock immediately, since the command was a single-statement transaction that implicitly commits when the command completes.

When possible, LOCK TABLE commands should be issued before any queries in the transaction. Acquiring a lock after issuing a query against user tables does not necessarily protect a transaction from serialization errors.

If you plan to issue LOCK TABLE commands against several tables within a transaction, issue the commands in an order set by the application. This minimizes the possibility of deadlock. When deadlock does occur, Postgres aborts one of the transactions involved in the deadlock.

Lock mode conflicts

The following table shows the matrix of lock conflicts; that is, which locks can block other types of locks. A lock mode conflicts with another mode if a lock cannot be granted to a transaction when another transaction holds the opposing lock on the same table. Locks held by the same transaction do not conflict with each other; a transaction can hold locks of several modes on the same table. If several locks are to be acquired on the same table, request stronger locks first to minimize the possibility of deadlock. If a transaction needs two locks against the same table where neither one is stronger than the other (in particular, SHARE and ROW EXCLUSIVE), consider a lock mode that subsumes both such as SHARE ROW EXCLUSIVE.

Table 1. Lock compatibility matrix
	Access Share	Row Share	Row Exclusive	Share	Share Row Exclusive	Exclusive	Access Exclusive
Access Share	OK	OK	OK	OK	OK	OK	Conflict
Row Share	OK	OK	OK	OK	OK	Conflict	Conflict
Row Exclusive	OK	OK	OK	Conflict	Conflict	Conflict	Conflict
Share	OK	OK	Conflict	OK	Conflict	Conflict	Conflict
Share Row Exclusive	OK	OK	Conflict	Conflict	Conflict	Conflict	Conflict
Exclusive	OK	Conflict	Conflict	Conflict	Conflict	Conflict	Conflict
Access Exclusive	Conflict	Conflict	Conflict	Conflict	Conflict	Conflict	Conflict

Syntax

Syntax to lock a table:

LOCK TABLE <table> in <lockmode> MODE [NOWAIT]

Inputs

The LOCK TABLE command takes the following inputs:

Table 2. LOCK TABLE inputs
Input	Description
<table>	The name of the table to be locked.
<lockmode>	The type of lock to be acquired on the table (see Table 3). The word ROW in a lock mode does not imply a row-level lock.
NOWAIT	The command is to return an error and abort the transaction if it cannot acquire the requested lock immediately.

Table 3. Lock modes
Lock mode	Acquired by	Required privileges	Note
ACCESS SHARE	SELECT	Select	This is the weakest lock mode. Several transactions can hold this kind of lock on a table.
ROW SHARE		Insert, Delete, or Update	Several transactions can hold this kind of lock on a table.
ROW EXCLUSIVE	INSERT, DELETE, and UPDATE	Insert, Delete, or Update	Several transactions can hold this kind of lock on a table.
SHARE	CREATE MATERIALIZED VIEW and GENERATE STATISTICS	Select or Genstats	Several transactions can hold this kind of lock on a table.
SHARE ROW EXCLUSIVE		Insert, Delete, or Update
EXCLUSIVE		Insert, Delete, or Update
ACCESS EXCLUSIVE	DROP TABLE, ALTER TABLE, and TRUNCATE TABLE	Drop, Alter, or Truncate

Output

The LOCK TABLE command has the following output:

Table 4. LOCK TABLE outputs
Output	Description
`LOCK TABLE`	The command was successful.
`NOTICE: LOCK TABLE issued after query against user tables`	If a LOCK TABLE is issued after the transaction has issued a query against user tables, the system displays a NOTICE message and the command succeeds when it is able to acquire the requested lock.

Privileges

You must be the admin user, the table owner, the owner of the database or schema where the table is defined, or your account must have the appropriate object privileges as described in Table 3.

Usage

The following provides sample usage.

LOCK TABLE customers IN ROW EXCLUSIVE MODE;