Command Prompt, Inc.
LOCK

LOCK

Name

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

Powered by Mammoth PostgreSQL
Copyright © 2000-2007 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.