Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com
Answertopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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
Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire