Name
LOCK
-- Explicitly locks a specified table within the current transaction.
Synopsis
LOCK [ TABLE ] name
LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE
Parameters
- name
This parameter takes the name of an existing table you wish to lock access to.
- share mode
There are a few different share modes available for use with this command. RDBMS terms used
: EXCLUSIVE locks prevent other locks from being granted; SHARE locks allow
others to share lock (and they prevent EXCLUSIVE locks); ACCESS a table's schema; ROW locks an
individual row. By default, EXCLUSIVE locks are used when this command is called without SHARE/EXCLUSIVE
being specified.
Table 13-1. Share modes
| Share mode | Description |
|---|
ACCESS SHARE MODE |
This mode is used to lock a table from being modified by a concurrent ALTER TABLE, DROP TABLE, or VACUUM
command. This cannot be combined with the ACCESS EXCLUSIVE mode.
|
ROW SHARE MODE | This mode is set on a table automatically when a SELECT FOR UPDATE command
is issued on it. This cannot be set in combination with the EXCLUSIVE or ACCESS EXCLUSIVE
lock modes.
|
ROW EXCLUSIVE MODE
| This mode is set automatically when the CREATE INDEX
command is issued. This cannot be combined with the ROW EXCLUSIVE,
SHARE ROW EXCLUSIVE, EXLCUSIVE, or ACCESS EXCLUSIVE modes.
|
SHARE MODE
|
This mode is set automatically by CREATE INDEX. It cannot be
used with the ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, ROW EXCLUSIVE, EXCLUSIVE, or ACCESS EXCLUSIVE modes.
|
SHARE ROW EXCLUSIVE MODE |
This mode is automatically set by the CREATE INDEX command.
You cannot use this in combination with the ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE,
or ACCESS EXCLUSIVE modes.
|
EXCLUSIVE MODE |
This mode stops all concurrent ROW [SHARE/SELECT] FOR UPDATE commands
from being committed until the lock is lifted. This cannot be combined with ROW SHARE, ROW EXCLUSIVE,
SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE modes.
|
ACCESS EXCLUSIVE MODE
|
This is the most restrictive mode available; use it to block any and all concurrent operations. It
is set by the ALTER TABLE, DROP TABLE,
and VACUUM commands. Calling LOCK TABLE
without specifying any parameters also sets this lock mode. This cannot be combined with any other lock modes.
|
Results
- LOCK TABLE
This message is displayed if the lock is successfully applied to the table.
- ERROR name: Table does not exist.
This message is displayed if the table name does not exist.
Description
Use the LOCK TABLE command to manually lock tables during a transaction.
By default, the least restrictive mode available is always used in PostgreSQL; however, you can specify
that more restrictive locking procedures should be used on certain tables with the use of this command.
When either EXCLUSIVE or SHARE locks are not specified, EXCLUSIVE will be used by default.
 | Note |
|---|
| | You can only lock tables when you are working within a transaction. Use the BEGIN
command to start a transaction and COMMIT
to end it.
|
LOCK TABLE can become useful in situations where the isolation level
is such that whatever application is trying to access a table needs to be certain that the data within
it is current. To do this, the appliation would SHARE lock the table; if other users were currently
writing to the table, the SHARE lock would conflict with the ROW EXCLUSIVE locks that they had already
acquired on the table, and the application requesting a SHARE lock would be forced to wait until any
writing transactions were completed.
| SERIALIZABLE |
|---|
| | To read current data from a table when within a transaction at the SERIALIZABLE isolation level, you would
need to lock the table using this command before executing any DML statements.
|
If an application were to attempt altering the data within a table, it would use SHARE ROW
EXCLUSIVE. Doing so will prevent deadlock in situations where two transactions are attempting to
SHARE lock a table to change data within it.
Deadlock occurs when two transactions are waiting for each other to finish their operations. While PostgreSQL
can detect deadlocks and end them with a ROLLBACK, they
can still be extremely 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 if more than one appliation is accessing a database at the same time. Also,
make sure that when using multiple applications they are not attempting to acquire conflicting lock modes
unless one of the modes can only be held by one transaction at a time. If multiple locks are involved in
the process, the most restrictive should always be obtained first.
Examples
Use the following example to lock the books table
in EXCLUSIVE mode.
booktown=# BEGIN;
BEGIN
booktown=# LOCK TABLE books IN EXCLUSIVE MODE;
LOCK TABLE