Easy Tutorial
Postgresql Like Postgresql Syntax Postgresql With Postgresql Null Postgresql Lock Postgresql View Postgresql Privileges Postgresql Limit Postgresql And Or Clauses Postgresql Autoincrement Postgresql Expressions Postgresql Data Type Postgresql Constraints Postgresql Where Clause Postgresql Drop Database Postgresql Select Database Windows Install Postgresql Postgresql Select Linux Install Postgresql Postgresql Join Postgresql Index Postgresql Tutorial Postgresql Schema Postgresql Trigger Postgresql Delete Postgresql Update Postgresql Alias Postgresql Union Postgresql Alter Table Postgresql Functions Postgresql Truncate Table Postgresql Group By Postgresql Sub Queries Postgresql Having Postgresql Insert Into Postgresql Create Table Postgresql Order By Postgresql Datetime Postgresql Create Database Postgresql Drop Table Postgresql Operators Postgresql Transaction Postgresql Distinct Mac Install Postgresql
❮ Postgresql Null Postgresql View ❯

PostgreSQL LOCK

Locks are primarily used to maintain the consistency of database data, preventing users from modifying a row or an entire table, typically in databases with high concurrency.

When multiple users access the database, uncontrolled concurrent operations may lead to incorrect data being read and stored, thereby compromising database consistency.

If an exclusive lock is placed on a data object, no other transactions can read or modify it.

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

LOCK Command Syntax

The basic syntax of the LOCK command is as follows:

LOCK [ TABLE ]
name
 IN
lock_mode

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

Deadlocks

Deadlocks 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 inconvenient. To prevent applications from encountering this issue, ensure that the application is designed to lock objects in the same order.

Advisory Locks

PostgreSQL provides a method to create locks with application-defined meanings. These are known as advisory locks. Since the system does not enforce their use, correct usage depends on 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 typical pessimistic locking strategy found in "flat file" data management systems. While flags stored in tables can serve the same purpose, advisory locks are faster, avoid table bloat, and are automatically cleaned up by the server at the end of the session.

Example

Create a COMPANY table (download COMPANY SQL file), with the following data content:

tutorialprodb# 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 tutorialprodb database in ACCESS EXCLUSIVE mode.

The LOCK statement works only in transaction mode.

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

The above operation will yield the following result:

LOCK TABLE

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

❮ Postgresql Null Postgresql View ❯