5.39.1. LOCK command syntax ¶

The basic syntax for LOCK commands is as follows:

LOCK [ TABLE ]
name
 IN
lock_mode

• Name: the name of the existing table to lock (optional schema qualification). If specified only before the table name, only the table is locked. If not specified, the table and all its child tables, if any, are locked.

• Lock_mode: the locking mode specifies which lock the lock conflicts with. If no locking mode is specified, the most restricted access exclusive mode is used. Possible values are: ACCESS SHARE,ROW SHARE,ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE,SHARE ROW EXCLUSIVE,EXCLUSIVE,ACCESS EXCLUSIVE.

Once the lock is acquired, the lock will be held for the rest of the current transaction. There is no unlock table command; the lock is always released at the end of the transaction.

5.39.2. Deadlock ¶

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

5.39.3. Consulting lock ¶

PostgreSQL provides a way to create locks with the meaning of an application definition. These are called consulting locks. Because the system does not force them to be used, it is up to the application to use them correctly. Consulting locks are useful for locking strategies that do not fit the MVCC model.

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

5.39.4. Example ¶

Create COMPANY 表（ 下载 COMPANY SQL 文件 ), the data are as follows:

runoobdb# 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 sets the runoobdb In the database COMPANY The table is locked in ACCESS EXCLUSIVE mode.

The LOCK statement only works in transactional mode.

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

The above action will result in the following result:

LOCK TABLE

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