| |
NameLOCK -- 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 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
|
|
