PostgreSQL LOCK (Lock)

Locks are mainly used to maintain the consistency of database data. They can prevent users from modifying a row or the entire table, and are generally used in databases with high concurrency.

When multiple users access the database, if concurrent operations are not controlled, incorrect data may be read and stored, destroying the consistency of the database.

There are two basic types of locks in a database: Exclusive Locks and Shared Locks.

If an exclusive lock is added to a data object, other transactions cannot read or modify it.

If a shared lock is added, the database object can be read by other transactions, but cannot be modified.

LOCK command syntax

The basic syntax of the LOCK command is as follows:

LOCK [ TABLE ]
name
 IN
lock_mode

• 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 child tables (if any) are locked.
• lock_mode: The lock mode specifies which locks this lock conflicts with. If no lock mode is specified, the most restrictive mode, ACCESS EXCLUSIVE, is used. Possible values are: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE.

Once a lock is obtained, it is held for the remainder of the current transaction. There is no UNLOCK TABLE command; locks are always released at transaction end.

Deadlock

A deadlock can occur when two transactions wait for each other to complete their operations. Although PostgreSQL can detect them and end them with a rollback, deadlocks are still very inconvenient. To prevent your application from encountering this problem, ensure that the application is designed to lock objects in the same order.

Advisory Locks

PostgreSQL provides methods for creating locks that have application-defined meanings. These are called advisory locks. Since the system does not enforce their use, correct usage is up to the application. Advisory locks are useful for locking strategies that do not fit the MVCC model.

For example, a common use of advisory locks is to simulate the pessimistic locking strategy typical of so-called "flat-file" data management systems. Although flags stored in a table can be used for the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of a session.

Examples

Create the COMPANY table (Download the COMPANY SQL file), with the following data:

exampledb# select * from COMPANY;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)

The following example locks the COMPANY table in the exampledb database in ACCESS EXCLUSIVE mode.

The LOCK statement only works in transaction mode.

exampledb=#BEGIN;
LOCK TABLE company1 IN ACCESS EXCLUSIVE MODE;

The above operation will give the following result:

LOCK TABLE

The above message indicates that the table is locked until the end of the transaction, and to complete the transaction, you must roll back or commit the transaction.

Other extensions