YouTip

## PostgreSQL LOCK In a high-concurrency database environment, locking is a fundamental mechanism used to maintain data consistency and integrity. It prevents multiple users or transactions from modifying the same rows or tables simultaneously, which could otherwise lead to dirty reads, non-repeatable reads, or corrupted data. PostgreSQL provides two basic categories of locks: * **Exclusive Locks:** When a transaction acquires an exclusive lock on a data object, no other transaction can read or modify that object. * **Share Locks:** When a transaction acquires a share lock on a data object, other transactions can read the object, but they cannot modify it. --- ## The LOCK Command Syntax The `LOCK` statement explicitly acquires a table-level lock. The basic syntax is as follows: ```sql LOCK name [, ...] ``` ### Parameter Descriptions: * **`name`**: The name of an existing table to lock (optionally schema-qualified). If only the table name is specified, only that table is locked. If not specified otherwise, the lock also extends to all of its descendant tables (if any). * **`lockmode`**: Specifies which lock mode this lock conflicts with. If no lock mode is specified, the most restrictive mode—`ACCESS EXCLUSIVE`—is used by default. The available lock modes (ordered from least restrictive to most restrictive) are: 1. `ACCESS SHARE` 2. `ROW SHARE` 3. `ROW EXCLUSIVE` 4. `SHARE UPDATE EXCLUSIVE` 5. `SHARE` 6. `SHARE ROW EXCLUSIVE` 7. `EXCLUSIVE` 8. `ACCESS EXCLUSIVE` > **Note:** Once acquired, a lock is held for the remainder of the current transaction. There is no "UNLOCK" command; locks are always released automatically at the end of a transaction (either upon `COMMIT` or `ROLLBACK`). --- ## Deadlocks A **deadlock** occurs when two or more transactions are waiting for each other to release locks, creating a dependency cycle where none of the transactions can proceed. While PostgreSQL automatically detects deadlocks and resolves them by aborting and rolling back one of the involved transactions, deadlocks still degrade system performance and user experience. To prevent deadlocks in your application, ensure that all transactions acquire locks on database objects in the exact same order. --- ## Advisory Locks PostgreSQL provides a mechanism to create locks that have application-defined meanings, known as **Advisory Locks**. Because the database engine does not enforce these locks automatically, their correct usage depends entirely on the application's logic. Advisory locks are highly useful for locking strategies that do not fit well into the standard MVCC (Multi-Version Concurrency Control) model. For example, a common use case for advisory locks is to emulate pessimistic locking strategies typical of "flat-file" data management systems. While you could achieve a similar result by updating a flag column in a table, advisory locks are significantly faster, avoid table bloat, and are automatically cleaned up by the server when the session ends. --- ## Practical Examples To demonstrate how table locking works, let's first set up a sample table named `COMPANY`. ### 1. Creating the Sample Table ```sql -- Create the COMPANY table CREATE TABLE COMPANY ( id INT PRIMARY KEY NOT NULL, name TEXT NOT NULL, age INT NOT NULL, address CHAR(50), salary REAL ); -- Insert sample data INSERT INTO COMPANY (id, name, age, address, salary) VALUES (1, 'Paul', 32, 'California', 20000.00), (2, 'Allen', 25, 'Texas', 15000.00), (3, 'Teddy', 23, 'Norway', 20000.00), (4, 'Mark', 25, 'Rich-Mond', 65000.00), (5, 'David', 27, 'Texas', 85000.00), (6, 'Kim', 22, 'South-Hall', 45000.00), (7, 'James', 24, 'Houston', 10000.00); ``` Verify the table contents: ```sql 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) ``` ### 2. Acquiring an Explicit Table Lock The `LOCK` statement must be executed inside a transaction block. The following example starts a transaction and locks the `COMPANY` table in `ACCESS EXCLUSIVE` mode. ```sql runoobdb=# BEGIN; BEGIN runoobdb=# LOCK TABLE COMPANY IN ACCESS EXCLUSIVE MODE; LOCK TABLE ``` The output `LOCK TABLE` indicates that the lock was successfully acquired. * While this transaction remains open, any other session attempting to query (`SELECT`) or modify (`UPDATE`/`DELETE`) the `COMPANY` table will be blocked and put into a waiting state. * To release the lock and allow other sessions to access the table, you must complete the transaction using either `COMMIT` or `ROLLBACK`: ```sql runoobdb=# COMMIT; COMMIT ```