LOCK [ TABLE ] name
LOCK [ TABLE ] name IN lock_modelock_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 a CREATE INDEX command. It blocks INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE, and VACUUM commands.
This mode also blocks concurrent ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE, and ACCESS EXCLUSIVE MODE locks from being acquired on the same table.
SHARE ROW EXCLUSIVE MODE
The SHARE ROW EXCLUSIVE MODE lock is a special lock mode nearly identical to the EXCLUSIVE MODE lock, but which allows concurrent ROW SHARE MODE locks to be acquired.
EXCLUSIVE MODE
The EXCLUSIVE MODE lock blocks INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE, and VACUUM commands on the table on which it is acquired, as well as SELECT commands with a FOR UPDATE clause.
This mode also blocks concurrent ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE, and ACCESS EXCLUSIVE MODE locks.
ACCESS EXCLUSIVE MODE
The ACCESS EXCLUSIVE MODE lock is acquired automatically by a ALTER TABLE, DROP TABLE, or VACUUM command on the table it modifies.
This mode blocks any concurrent command or other lock_mode from being acquired on the locked table.
Results
LOCK TABLE
The message returned when a lock is successfully applied to a table.
ERROR: Relation 'name' does not exist
The error returned if the table name does not exist in the connected database.
ERROR: Deadlock detected
The error returned if two LOCK TABLE commands result in a deadlock between two concurrent transactions.
Description
Use the LOCK TABLE command to manually lock tables during a transaction. Locking is a function of an RDBMS that temporarily blocks various kinds of access to a table (depending on the lock_mode). The session that locks the table retains normal access; the effect is only felt by concurrently connected users attempting to access the locked table.
Note that, in this context, blocking access is not the same as denying access. Any concurrently connected user attempting access which is blocked by a SQL lock will pause, but not fail, and wait until either the blocked command is terminated by the user, or until the table lock is released.
Several SQL commands implicitly acquire locks before they perform their work; in these cases, PostgreSQL will always choose the least restrictive lock necessary. A table lock immediately releases when a transaction is committed.
Using LOCK TABLE without an explicit locking mode causes the most restrictive mode (ACCESS EXCLUSIVE) to be used. You can specify less restrictive locking procedures by providing an explicit lock_mode.
Warning
You can only lock tables when working within a transaction. Using LOCK TABLE outside of a transaction will not display an error, but it will immediately autocommit, and release the lock, which serves no purpose. Use the BEGIN command to start a transaction, and the COMMIT command to commit your changes, and release the lock.
Deadlocks can occur when two transactions are waiting for each other to finish their operations. While PostgreSQL can detect them and end them with a ROLLBACK, deadlocks can still be inconvenient. To prevent your applications from running into this problem, make sure to design them in such a way that they will lock objects in the same order.
Examples
The following example locks the books table within the booktown database in ACCESS EXCLUSIVE mode:
booktown=# BEGIN;
BEGIN
booktown=# LOCK TABLE books IN ACCESS EXCLUSIVE MODE;
LOCK TABLE
