Description

LOCK TABLE obtains a table-level
lock, waiting if necessary for any conflicting locks to be
released. If NOWAIT is specified,
LOCK TABLE does not wait to acquire the
desired lock: if it cannot be acquired immediately, the command
is aborted and an error is emitted. Once obtained, the lock is
held for the remainder of the current transaction. (There is no
UNLOCK TABLE command; locks are always
released at transaction end.)

When acquiring locks automatically for commands that reference
tables, PostgreSQL always uses
the least restrictive lock mode possible. LOCK TABLE provides for cases when you might need
more restrictive locking. For example, suppose an application
runs a transaction at the Read Committed isolation level and
needs to ensure that data in a table remains stable for the
duration of the transaction. To achieve this you could obtain
SHARE lock mode over the table before
querying. This will prevent concurrent data changes and ensure
subsequent reads of the table see a stable view of committed
data, because SHARE lock mode conflicts
with the ROW EXCLUSIVE lock acquired by
writers, and your LOCK TABLE name IN SHARE MODE statement will wait
until any concurrent holders of ROW
EXCLUSIVE mode locks commit or roll back. Thus, once you
obtain the lock, there are no uncommitted writes outstanding;
furthermore none can begin until you release the lock.

To achieve a similar effect when running a transaction at the
REPEATABLE READ or SERIALIZABLE isolation level, you have to execute
the LOCK TABLE statement before
executing any SELECT or data
modification statement. A REPEATABLE
READ or SERIALIZABLE transaction's
view of data will be frozen when its first SELECT or data modification statement begins. A
LOCK TABLE later in the transaction will
still prevent concurrent writes — but it won't ensure that what
the transaction reads corresponds to the latest committed
values.

If a transaction of this sort is going to change the data in
the table, then it should use SHARE ROW
EXCLUSIVE lock mode instead of SHARE mode. This ensures that only one transaction
of this type runs at a time. Without this, a deadlock is
possible: two transactions might both acquire SHARE mode, and then be unable to also acquire
ROW EXCLUSIVE mode to actually perform
their updates. (Note that a transaction's own locks never
conflict, so a transaction can acquire ROW
EXCLUSIVE mode when it holds SHARE
mode — but not if anyone else holds SHARE mode.) To avoid deadlocks, make sure all
transactions acquire locks on the same objects in the same order,
and if multiple lock modes are involved for a single object, then
transactions should always acquire the most restrictive mode
first.

More information about the lock modes and locking strategies
can be found in Section
13.3.

Parameters

name

The name (optionally schema-qualified) of an existing
table to lock. If ONLY is
specified before the table name, only that table is locked.
If ONLY is not specified, the
table and all its descendant tables (if any) are locked.
Optionally, * can be specified
after the table name to explicitly indicate that descendant
tables are included.

The command LOCK TABLE a, b; is
equivalent to LOCK TABLE a; LOCK TABLE
b;. The tables are locked one-by-one in the order
specified in the LOCK TABLE
command.

lockmode

The lock mode specifies which locks this lock conflicts
with. Lock modes are described in Section 13.3.

If no lock mode is specified, then ACCESS EXCLUSIVE, the most restrictive mode,
is used.

NOWAIT

Specifies that LOCK TABLE
should not wait for any conflicting locks to be released:
if the specified lock(s) cannot be acquired immediately
without waiting, the transaction is aborted.

Notes

LOCK TABLE is useless outside a
transaction block: the lock would remain held only to the
completion of the statement. Therefore PostgreSQL reports an error if LOCK is used outside a transaction block. Use
BEGIN and COMMIT (or ROLLBACK) to define a transaction
block.

LOCK TABLE only deals with
table-level locks, and so the mode names involving ROW are all misnomers. These mode names should
generally be read as indicating the intention of the user to
acquire row-level locks within the locked table. Also, ROW EXCLUSIVE mode is a sharable table lock. Keep
in mind that all the lock modes have identical semantics so far
as LOCK TABLE is concerned, differing
only in the rules about which modes conflict with which. For
information on how to acquire an actual row-level lock, see
Section 13.3.2
and the FOR UPDATE/FOR SHARE
Clause in the SELECT reference
documentation.

Examples

Obtain 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: