# Relation-Level Locks
LLMS index: [llms.txt](/en/llms.txt)
---
## 12.1 About Locks {#internal-section-12-1}
*Locks* control concurrent access to shared resources.
Concurrent access implies that several processes try to get the same resource at the same time. It makes no difference whether these processes are executed in parallel (if the hardware permits) or sequentially in the time-sharing mode. If there is no concurrent access, there is no need to acquire locks (for example, shared buffer cache requires locking, while local cache can do without it).
Before accessing a resource, the process must *acquire* a lock on it; when the operation is complete, this lock must be *released* for the resource to become available to other processes. If locks are managed by the database system, the established order of operations is maintained automatically; if locks are controlled by the application, the protocol must be enforced by the application itself.
At a low level, a lock is simply a chunk of shared memory that defines the lock status (whether it is acquired or not); it can also provide some additional information, such as the process number or acquisition time.
> [!NOTE]
> As you can guess, a shared memory segment is a resource in its own right. Concurrent access to such resources is regulated by synchronization primitives (such as semaphores or mutexes) provided by the operating system. They guarantee strictly consecutive execution of the code that accesses a shared resource. At the lowest level, these primitives are based on atomic CPU instructions (such as test-and-set or compare-and-swap).
In general, we can use locks to protect any resource as long as it can be unambiguously identified and assigned a particular lock address.
For example, we can lock a database object, such as a table (identified by `oid` in the system catalog), a data page (identified by a filename and a position within this file), a row version (identified by a page and an offset within this page). We can also lock a memory structure, such as a hash table or a buffer (identified by an assigned ID). We can even lock an abstract resource that has no physical representation.
But it is not always possible to acquire a lock at once: a resource can be already locked by someone else. Then the process either joins the queue (if it is allowed for this particular lock type) or tries again some time later. Either way, it has to wait for the lock to be released.
I would like to single out two factors that can greatly affect locking efficiency.
**Granularity,** or the “grain size” of a lock. Granularity is important if resources form a hierarchy.
For example, a table consists of pages, which, in their turn, consist of tuples. All these objects can be protected by locks. Table-level locks are coarse-grained; they forbid concurrent access even if the processes need to get to different pages or rows.
Row-level locks are fine-grained, so they do not have this drawback; however, the number of locks grows. To avoid using too much memory for lock-related metadata, PostgreSQL can apply various methods, one of them being *lock escalation*: if the number of fine-grained locks exceeds a certain threshold, they are replaced by a single lock of coarser granularity.
**A set of modes** in which a lock can be acquired.
As a rule, only two modes are applied. The *exclusive* mode is incompatible with all the other modes, including itself. The *shared* mode allows a resource to be locked by several processes at a time. The shared mode can be used for reading, while the exclusive mode is applied for writing.
In general, there may be other modes too. Names of modes are unimportant, it is their compatibility matrix that matters.
Finer granularity and support for multiple compatible modes give more opportunities for concurrent execution.
All locks can be classified by their duration.
**Long-term** locks are acquired for a potentially long time (in most cases, till the end of the transaction); they typically protect such resources as relations and rows. These locks are usually managed by PostgreSQL automatically, but a user still has some control over this process.
Long-term locks offer multiple modes that enable various concurrent operations on data. They usually have extensive infrastructure (including such features as wait queues, deadlock detection, and instrumentation) since its maintenance is anyway much cheaper than operations on protected data.
**Short-term** locks are acquired for fractions of a second and rarely last longer than several CPU instructions; they usually protect data structures in the shared memory. PostgreSQL manages such locks in a fully automated way.
Short-term locks typically offer very few modes and only basic infrastructure, which may have no instrumentation at all.
PostgreSQL supports various types of locks.[^internal-201-1] *Heavyweight locks* (which are acquired on relations and other objects) and *row-level locks* are considered long-term. Short-term locks comprise various *locks on memory structures*. Besides, there is also a distinct group of *predicate locks*, which, despite their name, are not locks at all. (*p.* [210](/en/13-row-level-locks/#internal-page-210)) (*p.* [240](/en/15-locks-on-memory-structures/#internal-page-240)) (*p.* [235](/en/14-miscellaneous-locks/#internal-page-235))
## 12.2 Heavyweight Locks {#internal-section-12-2}
*Heavyweight* locks are long-term ones. Acquired at the *object* level, they are mainly used for relations, but can also be applied to some other types of objects. Heavyweight locks typically protect objects from concurrent updates or forbid their usage during restructuring, but they can address other needs too. Such a vague definition is deliberate: locks of this type are used for all kinds of purposes. The only thing they have in common is their internal structure.
Unless explicitly specified otherwise, the term *lock* usually implies a heavyweight lock.
Heavyweight locks are located in the server’s shared memory[^internal-201-2] and can be displayed in the `pg_locks` view. Their total number is limited by the *max\_locks\_per\_transaction* value multiplied by *max\_connections*. (*default:* `64`) (*default:* `100`)
All transactions use a common pool of locks, so one transaction can acquire more than *max\_locks\_per\_transaction* locks. What really matters is that the total number of locks in the system does not exceed the defined limit. Since the pool is initialized when the server is launched, changing any of these two parameters requires a server restart.
If a resource is already locked in an incompatible mode, the process trying to acquire another lock joins the queue. Waiting processes do not waste CPU time: they fall asleep until the lock is released and the operating system wakes them up.
Two transactions can find themselves in a *deadlock* if the first transaction is unable to continue its operation until it gets a resource locked by the other transaction, which, in its turn, needs a resource locked by the first transaction. This case is rather simple; a deadlock can also involve more than two transactions. Since deadlocks cause infinite waits, PostgreSQL detects them automatically and aborts one of the affected transactions to ensure that normal operation can continue. (*p.* [225](/en/13-row-level-locks/#internal-page-225))
Different types of heavyweight locks serve different purposes, protect different resources, and support different modes, so we will consider them separately.
The following list provides the names of lock types as they appear in the `locktype` column of the `pg_locks` view:
**transactionid** and **virtualxid** — a lock on a transaction ID (*p.* [202](/en/12-relation-level-locks/#internal-page-202))
**relation** — a relation-level lock (*p.* [204](/en/12-relation-level-locks/#internal-page-204))
**tuple** — a lock acquired on a tuple (*p.* [215](/en/13-row-level-locks/#internal-page-215))
**object** — a lock on an object that is not a relation (*p.* [231](/en/14-miscellaneous-locks/#internal-page-231))
**extend** — a relation extension lock (*p.* [232](/en/14-miscellaneous-locks/#internal-page-232))
**page** — a page-level lock used by some index types (*p.* [233](/en/14-miscellaneous-locks/#internal-page-233))
**advisory** — an advisory lock (*p.* [234](/en/14-miscellaneous-locks/#internal-page-234))
Almost all heavyweight locks are acquired automatically as needed and are released automatically when the corresponding transaction completes. There are some exceptions though: for example, a relation-level lock can be set explicitly, while advisory locks are always managed by users.
## 12.3 Locks on Transaction IDs {#internal-section-12-3}
Each transaction always holds an exclusive lock on its own ID (both virtual and real, if available). (*p.* [75](/en/03-pages-and-tuples/#internal-page-75))
PostgreSQL offers two locking modes for this purpose, exclusive and shared. Their compatibility matrix is very simple: the shared mode is compatible with itself, while the exclusive mode cannot be combined with any mode.
| Mode | Shared | Exclusive |
| --- | --- | --- |
| Shared | | × |
| Exclusive | × | × |
{#internal-table-12-1 num="12.1" caption="Transaction ID lock compatibility (× marks a conflict)" class="wide"}
To track completion of a particular transaction, a process can request a lock on this transaction’s ID, in any mode. Since the transaction itself is already holding an exclusive lock on its own ID, another lock is impossible to acquire. The process requesting this lock joins the queue and falls asleep. Once the transaction completes, the lock is released, and the queued process wakes up. Clearly, it will not manage to acquire the lock because the corresponding resource has already disappeared, but this lock is not what is actually needed anyway.
Let’s start a transaction in a separate session and get the process ID (PID) of the backend:
```psql
=> BEGIN;
=> SELECT pg_backend_pid();
pg_backend_pid
----------------
28980
(1 row)
```
The started transaction holds an exclusive lock on its own virtual ID:
```psql
=> SELECT locktype, virtualxid, mode, granted
FROM pg_locks WHERE pid = 28980;
locktype | virtualxid | mode | granted
------------+------------+---------------+---------
virtualxid | 5/2 | ExclusiveLock | t
(1 row)
```
Here `locktype` is the type of the lock, `virtualxid` is the virtual transaction ID (which identifies the locked resource), and `mode` is the locking mode (exclusive in this case). The `granted` flag shows whether the requested lock has been acquired.
Once the transaction gets a real ID, the corresponding lock is added to this list:
```psql
=> SELECT pg_current_xact_id();
pg_current_xact_id
--------------------
122849
(1 row)
```
```psql
=> SELECT locktype, virtualxid, transactionid AS xid, mode, granted
FROM pg_locks WHERE pid = 28980;
locktype | virtualxid | xid | mode | granted
---------------+------------+--------+---------------+---------
virtualxid | 5/2 | | ExclusiveLock | t
transactionid | | 122849 | ExclusiveLock | t
(2 rows)
```
Now this transaction holds exclusive locks on both its IDs.
## 12.4 Relation-Level Locks {#internal-section-12-4}
PostgreSQL provides as many as eight modes in which a relation (a table, an index, or any other object) can be locked.[^internal-204-1] Such a variety allows you to maximize the number of concurrent commands that can be run on a relation.
The next page shows the compatibility matrix extended with examples of commands that require the corresponding locking modes. There is no point in memorizing all these modes or trying to find the logic behind their naming, but it is definitely useful to look through this data, draw some general conclusions, and refer to this table as required.
| Mode | AS | RS | RE | SUE | S | SRE | E | AE | Commands |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Access Share | | | | | | | | × | `SELECT` |
| Row Share | | | | | | | × | × | `SELECT FOR UPDATE/SHARE` |
| Row Exclusive | | | | | × | × | × | × | `INSERT, UPDATE, DELETE` |
| Share Update Exclusive | | | | × | × | × | × | × | `VACUUM, CREATE INDEX CONCURRENTLY` |
| Share | | | × | × | | × | × | × | `CREATE INDEX` |
| Share Row Exclusive | | | × | × | × | × | × | × | `CREATE TRIGGER` |
| Exclusive | | × | × | × | × | × | × | × | `REFRESH MAT. VIEW CONCURRENTLY` |
| Access Exclusive | × | × | × | × | × | × | × | × | `DROP, TRUNCATE, VACUUM FULL, LOCK TABLE, REFRESH MAT. VIEW` |
{#internal-table-12-2 num="12.2" caption="Relation-level lock compatibility (× marks a conflict)" class="wide"}
The `Access Share` mode is the weakest one; it can be used with any other mode except `Access Exclusive`, which is incompatible with all the modes. Thus, a `SELECT` command can be run in parallel with almost any operation, but it does not let you drop a table that is being queried.
The first four modes allow concurrent heap modifications, while the other four do not. For example, the `CREATE INDEX` command uses the `Share` mode, which is compatible with itself (so you can create several indexes on a table concurrently) and with the modes used by read-only operations. As a result, `SELECT` commands can run in parallel with index creation, while `INSERT`, `UPDATE`, and `DELETE` commands will be blocked.
Conversely, unfinished transactions that modify heap data block the `CREATE INDEX` command. Instead, you can call `CREATE INDEX CONCURRENTLY`, which uses a weaker `Share Update Exclusive` mode: it takes longer to create an index (and this operation can even fail), but in return, concurrent data updates are allowed.
The `ALTER TABLE` command has multiple flavors that use different locking modes (`Share Update Exclusive`, `Share Row Exclusive`, `Access Exclusive`). All of them are described in the documentation.[^internal-205-1]
Examples in this part of the book rely on the `accounts` table again:
```psql
=> TRUNCATE accounts;
=> INSERT INTO accounts(id, client, amount)
VALUES
(1, 'alice', 100.00),
(2, 'bob', 200.00),
(3, 'charlie', 300.00);
```
We will have to access the `pg_locks` table more than once, so let’s create a view that shows all IDs in a single column, thus making the output more concise:
```psql
=> CREATE VIEW locks AS
SELECT pid,
locktype,
CASE locktype
WHEN 'relation' THEN relation::regclass::text
WHEN 'transactionid' THEN transactionid::text
WHEN 'virtualxid' THEN virtualxid
END AS lockid,
mode,
granted
FROM pg_locks
ORDER BY 1, 2, 3;
```
The transaction that is still running in the first session updates a row. This operation locks the `accounts` table and all its indexes, which results in two new locks of the `relation` type acquired in the `Row Exclusive` mode:
```psql
=> UPDATE accounts SET amount = amount + 100.00 WHERE id = 1;
=> SELECT locktype, lockid, mode, granted
FROM locks WHERE pid = 28980;
locktype | lockid | mode | granted
---------------+---------------+------------------+---------
relation | accounts | RowExclusiveLock | t
relation | accounts_pkey | RowExclusiveLock | t
transactionid | 122849 | ExclusiveLock | t
virtualxid | 5/2 | ExclusiveLock | t
(4 rows)
```
## 12.5 Wait Queue {#internal-section-12-5}
Heavyweight locks form a fair wait queue.[^internal-206-1] A process joins the queue if it attempts to acquire a lock that is incompatible either with the current lock or with the locks requested by other processes already in the queue.
While the first session is working on an update, let’s try to create an index on this table in another session:
```psql
=> SELECT pg_backend_pid();
pg_backend_pid
----------------
29459
(1 row)
=> CREATE INDEX ON accounts(client);
```
The command hangs, waiting for the resource to be released. The transaction tries to lock the table in the `Share` mode but cannot do it:
```psql
=> SELECT locktype, lockid, mode, granted
FROM locks WHERE pid = 29459;
locktype | lockid | mode | granted
------------+----------+---------------+---------
relation | accounts | ShareLock | f
virtualxid | 6/3 | ExclusiveLock | t
(2 rows)
```
Now let the third session start the `VACUUM FULL` command. It will also join the queue because it requires the `Access Exclusive` mode, which conflicts with all the other modes:
```psql
=> SELECT pg_backend_pid();
pg_backend_pid
----------------
29662
(1 row)
=> VACUUM FULL accounts;
```
```psql
=> SELECT locktype, lockid, mode, granted
FROM locks WHERE pid = 29662;
locktype | lockid | mode | granted
---------------+----------+---------------------+---------
relation | accounts | AccessExclusiveLock | f
transactionid | 122853 | ExclusiveLock | t
virtualxid | 7/4 | ExclusiveLock | t
(3 rows)
```
All the subsequent contenders will now have to join the queue, regardless of their locking mode. Even simple `SELECT` queries will honestly follow `VACUUM FULL`, although they are compatible with the `Row Exclusive` lock held by the first session performing the update.
```psql
=> SELECT pg_backend_pid();
pg_backend_pid
----------------
29872
(1 row)
=> SELECT * FROM accounts;
```
```psql
=> SELECT locktype, lockid, mode, granted
FROM locks WHERE pid = 29872;
locktype | lockid | mode | granted
------------+----------+-----------------+---------
relation | accounts | AccessShareLock | f
virtualxid | 8/3 | ExclusiveLock | t
(2 rows)
```
T1
UPDATE relation T2
CREATE INDEX T3
VACUUM FULL T4
SELECT
The `pg_blocking_pids` function gives a high-level overview of all waits. It shows the IDs of all processes queued before the specified one that are already holding or would like to acquire an incompatible lock: (v. 9.6)
```psql
=> SELECT pid,
pg_blocking_pids(pid),
wait_event_type,
state,
left(query,50) AS query
FROM pg_stat_activity
WHERE pid IN (28980,29459,29662,29872) \gx
-[ RECORD 1 ]----+---------------------------------------------------
pid | 28980
pg_blocking_pids | {}
wait_event_type | Client
state | idle in transaction
query | UPDATE accounts SET amount = amount + 100.00 WHERE
-[ RECORD 2 ]----+---------------------------------------------------
pid | 29459
pg_blocking_pids | {28980}
wait_event_type | Lock
state | active
query | CREATE INDEX ON accounts(client);
-[ RECORD 3 ]----+---------------------------------------------------
pid | 29662
pg_blocking_pids | {28980,29459}
wait_event_type | Lock
state | active
query | VACUUM FULL accounts;
-[ RECORD 4 ]----+---------------------------------------------------
pid | 29872
pg_blocking_pids | {29662}
wait_event_type | Lock
state | active
query | SELECT * FROM accounts;
```
To get more details, you can review the information provided in the `pg_locks` table.[^internal-208-1]
Once the transaction is completed (either committed or aborted), all its locks are released.[^internal-208-2] The first process in the queue gets the requested lock and wakes up.
Here the transaction commit in the first session leads to sequential execution of all the queued processes:
```psql
=> ROLLBACK;
ROLLBACK
```
```sql
CREATE INDEX
```
```sql
VACUUM
id | client | amount
----+---------+--------
1 | alice | 100.00
2 | bob | 200.00
3 | charlie | 300.00
(3 rows)
```
[^internal-201-1]: [backend/storage/lmgr/README]()
[^internal-201-2]: [backend/storage/lmgr/lock.c]()
[^internal-204-1]: [postgresql.org/docs/14/explicit-locking.html#LOCKING-TABLES]()
[^internal-205-1]: [postgresql.org/docs/14/sql-altertable.html]()
[^internal-206-1]: [backend/storage/lmgr/lock.c](), LockAcquire function
[^internal-208-1]: [wiki.postgresql.org/wiki/Lock\_dependency\_information]()
[^internal-208-2]: [backend/storage/lmgr/lock.c](), LockReleaseAll & LockRelease functions