LOCK - Explicitly lock a table inside a transaction
SYNOPSIS
LOCK [ TABLE ] name
LOCK [ TABLE ] name IN [ ROW | ACCESS ] { SHARE | EXCLUSIVE } MODE
LOCK [ TABLE ] name IN SHARE ROW EXCLUSIVE MODE
INPUTS
name The name of an existing table to lock.
ACCESS SHARE MODE
Note: This lock mode is acquired automatically over
tables being queried.
This is the least restrictive lock mode. It conflicts only
with ACCESS EXCLUSIVE mode. It is used to protect a table
from being modified by concurrent ALTER TABLE, DROP TABLE
and VACUUM commands.
ROW SHARE MODE
Note: Automatically acquired by SELECT...FOR
UPDATE. While it is a shared lock, may be upgraded
later to a ROW EXCLUSIVE lock.
Conflicts with EXCLUSIVE and ACCESS EXCLUSIVE lock modes.
ROW EXCLUSIVE MODE
Note: Automatically acquired by UPDATE, DELETE, and
INSERT statements.
Conflicts with SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE and
ACCESS EXCLUSIVE modes.
SHARE MODE
Note: Automatically acquired by CREATE INDEX.
Share-locks the entire table.
Conflicts with ROW EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLU
SIVE and ACCESS EXCLUSIVE modes. This mode protects a
table against concurrent updates.
SHARE ROW EXCLUSIVE MODE
Note: This is like EXCLUSIVE MODE, but allows SHARE
ROW locks by others.
Conflicts with ROW EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE,
EXCLUSIVE MODE
Note: This mode is yet more restrictive than SHARE
ROW EXCLUSIVE. It blocks all concurrent ROW
SHARE/SELECT...FOR UPDATE queries.
Conflicts with ROW SHARE, ROW EXCLUSIVE, SHARE, SHARE ROW
EXCLUSIVE, EXCLUSIVE and ACCESS EXCLUSIVE modes.
ACCESS EXCLUSIVE MODE
Note: Automatically acquired by ALTER TABLE, DROP
TABLE, VACUUM statements. This is the most
restrictive lock mode which conflicts with all
other lock modes and protects a locked table from
any concurrent operations.
Note: This lock mode is also acquired by an unqual
ified LOCK TABLE (i.e., the command without an
explicit lock mode option).
OUTPUTS
LOCK TABLE
The lock was successfully applied.
ERROR name: Table does not exist.
Message returned if name does not exist.
DESCRIPTION
LOCK TABLE controls concurrent access to a table for the
duration of a transaction. Postgres always uses the least
restrictive lock mode whenever possible. LOCK TABLE pro
vides for cases when you might need more restrictive lock
ing.
RDBMS locking uses the following terminology:
EXCLUSIVE
Exclusive lock that prevents other locks from being
granted.
SHARE Allows others to share lock. Prevents EXCLUSIVE
locks.
ACCESS Locks table schema.
ROW Locks individual rows.
EXCLUSIVE is assumed. Locks exist for the duration
of the transaction.
For example, an application runs a transaction at READ
COMMITTED isolation level and needs to ensure the exis
tence of data in a table for the duration of the transac
tion. To achieve this you could use SHARE lock mode over
the table before querying. This will protect data from
concurrent changes and provide any further read operations
over the table with data in their actual current state,
because SHARE lock mode conflicts with any ROW EXCLUSIVE
one acquired by writers, and your LOCK TABLE name IN SHARE
MODE statement will wait until any concurrent write opera
tions commit or rollback.
Note: To read data in their real current state when
running a transaction at the SERIALIZABLE isolation
level you have to execute a LOCK TABLE statement
before executing any DML statement, when the trans
action defines what concurrent changes will be vis
ible to itself.
In addition to the requirements above, if a transaction is
going to change data in a table, then SHARE ROW EXCLUSIVE
lock mode should be acquired to prevent deadlock condi
tions when two concurrent transactions attempt to lock the
table in SHARE mode and then try to change data in this
table, both (implicitly) acquiring ROW EXCLUSIVE lock mode
that conflicts with a concurrent SHARE lock.
To continue with the deadlock (when two transaction wait
for one another) issue raised above, you should follow two
general rules to prevent deadlock conditions:
· Transactions have to acquire locks on the same objects
in the same order.
For example, if one application updates row R1 and than
updates row R2 (in the same transaction) then the second
application shouldn't update row R2 if it's going to
update row R1 later (in a single transaction). Instead,
it should update rows R1 and R2 in the same order as the
first application.
· Transactions should acquire two conflicting lock modes
only if one of them is self-conflicting (i.e., may be
held by one transaction at time only). If multiple lock
modes are involved, then transactions should always
acquire the most restrictive mode first.
cussing the use of SHARE ROW EXCLUSIVE mode rather than
SHARE mode.
Note: Postgres does detect deadlocks and will rollback
at least one waiting transaction to resolve the dead
lock.
NOTES
LOCK is a Postgres language extension.
Except for ACCESS SHARE/EXCLUSIVE lock modes, all other
Postgres lock modes and the LOCK TABLE syntax are compati
ble with those present in Oracle.
LOCK works only inside transactions.
USAGE
Illustrate a SHARE lock on a primary key table when going
to perform inserts into a foreign key table:
BEGIN WORK;
LOCK TABLE films IN SHARE MODE;
SELECT id FROM films
WHERE name = 'Star Wars: Episode I - The Phantom Menace';
-- Do ROLLBACK if record was not returned
INSERT INTO films_user_comments VALUES
(_id_, 'GREAT! I was waiting for it for so long!');
COMMIT WORK;
Take a SHARE ROW EXCLUSIVE lock on a primary key table
when going to perform a delete operation:
BEGIN WORK;
LOCK TABLE films IN SHARE ROW EXCLUSIVE MODE;
DELETE FROM films_user_comments WHERE id IN
(SELECT id FROM films WHERE rating < 5);
DELETE FROM films WHERE rating < 5;
COMMIT WORK;
COMPATIBILITY
SQL92
There is no LOCK TABLE in SQL92, which instead uses SET
TRANSACTION to specify concurrency levels on transactions.
We support that too; see SET TRANSACTION [set_transac
tion(l)] for details.
Man(1) output converted with
man2html