Locks a table within a transaction.
LOCK [ TABLE ] name LOCK [ TABLE ] name IN lock_mode lock_mode ::= { [ ROW | ACCESS ] { SHARE | EXCLUSIVE } | SHARE ROW EXCLUSIVE } MODE
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.
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.
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
.
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.
3.147.70.247