PostgreSQL LOCK (Lock)

Locks are mainly used to maintain the consistency of database data, which 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, it may lead to reading and storing incorrect data, thus destroying the consistency of the database.

There are two basic types of locks in the database: exclusive locks (Exclusive Locks) and shared locks (Share Locks).

If an exclusive lock is added to the 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 of the existing table to be locked (optional schema qualification). If only the table name is specified before, only the table is locked. If not specified, the table and all its subtables (if any) are locked.

• lock_mode: The lock mode specifies which lock conflicts with this lock. If no lock mode is specified, the most restrictive 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 a lock is obtained, it will be held for the rest of the current transaction. There is no unlock table command; locks are always released at the end of the transaction.

Deadlocks

Deadlocks may occur when two transactions are waiting 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 the application from encountering this problem, make sure that the application is designed to lock objects in the same order.

Consulting Locks

PostgreSQL provides methods to create locks with application-defined meanings. These are called consulting locks. Since the system does not enforce their use, their correct use depends on the application. Consulting locks are very useful for locking strategies that are not suitable for the MVCC model.

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

Online example

Create COMPANY table (Download COMPANY SQL file ），Data content as follows：

w3codeboxdb# 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 will show w3The COMPANY table in the codeboxdb database is locked in ACCESS EXCLUSIVE mode.

Lock statements only work in transaction mode.

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

The above operation will produce the following result:

LOCK TABLE

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