Locks in PostgreSQL
Locks protect data integrity and transaction isolation in concurrent PostgreSQL contexts with its core Multiversion Concurrency Control (MVCC) technology. MVCC allows transactions to execute on a consistent snapshot of data without blocking readers, although locks are needed for database schema changes or higher concurrency guarantees.
Types of Locks
The many lock types offered by PostgreSQL are arranged according to their behavior and scope:
Table-Level Locks: The LOCK command can be used to specifically request table-level locks, which are the most common and are automatically obtained by various SQL procedures. Even though the titles of these locks may imply row-level scope for historical reasons, they are applied to the entire table. Different table-level lock modes differ primarily in the set of other lock modes they clash with.
The following table-level lock modes are available in PostgreSQL:
ACCESS SHARE MODE: SELECT statements automatically acquire this lock mode, which is the least restrictive. This prevents concurrent ALTER TABLE, DROP TABLE, and VACUUM operations from affecting a table and only conflicts with ACCESS EXCLUSIVE mode. In order to wait for transactions with write locks on the table to complete, the mode CREATE INDEX CONCURRENTLY or REINDEX CONCURRENTLY is acquired.
ROW SHARE MODE: SELECT…FOR UPDATE statements allow for the automatic acquisition of ROW SHARE MODE. Both the ACCESS EXCLUSIVE and EXCLUSIVE lock modes are incompatible with it.
ROW EXCLUSIVE MODE: Acquired automatically by INSERT, DELETE, and UPDATE commands. EXCLUSIVE, ACCESS EXCLUSIVE, SHARE, and SHARE ROW EXCLUSIVE modes are incompatible with it.
SHARE UPDATE EXCLUSIVE MODE: A few ALTER INDEX and ALTER TABLE variations, VACUUM (without FULL), ANALYZE, CREATE INDEX CONCURRENTLY, CREATE STATISTICS, COMMENT ON, and REINDEX CONCURRENTLY are equipped with this mode. EXCLUSIVE, ACCESS EXCLUSIVE, SHARE, and SHARE ROW EXCLUSIVE modes are incompatible with it. Other commands are usually not blocked by autovacuum processes unless they are preventing transaction ID wraparound, and they usually maintain a SHARE UPDATE EXCLUSIVE lock.
SHARE MODE: Acquired automatically by CREATE INDEX (without CONCURRENCE). EXCLUSIVE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, and ACCESS EXCLUSIVE modes are all in conflict with it. This mode guards against concurrent modifications to a table’s data.
SHARE ROW EXCLUSIVE MODE: This option permits SHARE ROW locks by other users, like EXCLUSIVE mode. Being self-exclusive that is, only one session can hold it at a time it shields a table from concurrent data changes. Share, EXCLUSIVE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, and ACCESS EXCLUSIVE modes are all in conflict with it. There are no PostgreSQL commands that automatically acquire this lock mode.
EXCLUSIVE MODE: Only concurrent ACCESS SHARE locks are permitted in the exclusive mode, which means that only table reads can take place concurrently with a transaction that is holding the lock. The granting of other EXCLUSIVE locks is prevented. SHARE, SHARE ROW EXCLUSIVE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, and ACCESS EXCLUSIVE modes are incompatible with this mode.
ACCESS EXCLUSIVE MODE: Conflicting with all other lock modes, this is the most restrictive lock mode. Statements like ALTER, DROP, TRUNCATE, VACUUM FULL, CLUSTER, and REINDEX that change a table’s physical structure can obtain it, as can the explicit LOCK command in ACCESS EXCLUSIVE mode. Additional access exclusive mode is acquired by an unqualified LOCK TABLE command.
Row-Level Locks: The INSERT, DELETE, and UPDATE statements, as well as SELECT… FOR UPDATE, all require row-level locks. Until the transaction is committed or rolled back, these locks prevent row changes. Rows can be locked infinitely, but max_locks_per_transaction limits the number of objects.
Page-Level Locks: Read/write access to table pages within the shared buffer pool is managed via page-level share/exclusive locks in addition to table and row locks. Short-term locks like these are released as soon as an index row is inserted or fetched. It is often not necessary for application developers to worry about page-level locks.
Advisory Locks: PostgreSQL provides a special way to create advisory locks, which are locks with meanings defined by the application. Applications must properly manage them; the system does not mandate their use. If the MVCC model does not work well for a locking strategy, as simulating pessimistic locking in “flat file” data management systems, advisory locks can be helpful. In addition to avoiding table bloat and being immediately cleaned up at the conclusion of a session, they are quicker than using a flag in a table.
A 64-bit key value or two 32-bit key values are indicative of advisory locks. Both shared and exclusive locks are possible; only exclusive locks on the same will clash with a shared lock. Locks can be held at the transaction or session level until the transaction finishes or they are expressly released. The functions pg_advisory_lock, pg_advisory_lock_shared, pg_unlock, and pg_unlock_all control these locks.
Code Example:
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO employees(name) VALUES ('Alice'), ('Bob');
BEGIN;
LOCK TABLE employees IN ACCESS SHARE MODE;
SELECT * FROM employees WHERE id=1 FOR UPDATE;
SELECT pg_advisory_lock(12345);Output:
DROP TABLE
CREATE TABLE
INSERT 0 2
BEGIN
LOCK TABLE
id | name
----+-------
1 | Alice
(1 row)
pg_advisory_lock
------------------
(1 row)Lock Management and Monitoring
A production environment requires effective lock management, which needs to be closely watched.
Monitoring Locks: A database server’s list of open locks is shown by the pg_locks system view. Another way to locate locks is to use the pg_stat_activity function.
Explicit Locking: Table-level locks can be explicitly created using the LOCK SQL statement. For example, the car table is granted an exclusive lock when LOCK TABLE car_portal_app.car IN ACCESS EXCLUSIVE MODE is used.
Configuration Parameters:
- This option limits the average number of object locks (such as tables) each transaction uses. If all locks fit in the shared lock table, transactions may use more locks. The quantity of rows that can be locked is not restricted. The number 64 is the default.
- The size of the shared memory pool for tracking locks is determined by max_connections in addition to max_locks_per_transaction. If the server runs out of memory, it may not give any locks.
- PostgreSQL automatically identifies deadlocks, as indicated by deadlock_timeout. Before logging a message regarding the lock wait, this option establishes how long a session will wait for a lock. In case of deadlock, one transaction is canceled.
Performance Impact: Long-running transactions that are locked (e.g., awaiting user input) may slow performance.
Concurrency Control and Locks
Typically, queries get a consistent picture of data with PostgreSQL MVCC mechanism, which reduces the need for explicit locking. But locks are still necessary for:
Transaction Isolation Levels: SQL supports a number of isolation levels for transactions, including serializable, repeatable read, committed, and uncommitted. Checks for data consistency might be challenging because Read Committed transactions may have a changing data view. Although they offer a reliable view, read/write conflicts can still occur with repeatable read operations. All important tables should be explicitly locked with SHARE mode (or higher) for global validity checks, especially in non-serializable modes, to prevent other transactions from making uncommitted changes.
DDL Statements: Many DDL statements, like CREATE INDEX, gain SHARE locks. The CREATE INDEX command’s relation hold prevents concurrent insert, remove, and update operations until index building is complete.
Locking and Indices
Distinct locking techniques are used by various index types:
- Read/write access is provided by short-term share/exclusive page-level locks in the B-tree, GIN, GiST, and SP-GiST indices. When each index row is fetched or inserted, these locks are released instantly, allowing for large concurrency without deadlocks.
- Read/write access to hash indices is provided by share/exclusive hash-bucket-level locks. Unlike a single index operation, these locks are held until the entire bucket is handled, which could result in deadlocks.
- SIRead locks are obtained by indicators that support predicate locking on index pages that are accessed during predicate searches.
In complex application contexts, understanding PostgreSQL’s locking techniques is critical for concurrency management, database performance, and data integrity.