# Lesson 13: Locks / Row-Level Locks

> Native Markdown source page: Locks / Row-Level Locks

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** [dba2_13_locks_rows.html](https://edu.postgrespro.com/16/dba2-16-en/dba2_13_locks_rows.html)
> **Archive:** [DBA2-handouts-16-en.zip](https://edu.postgrespro.com/16/DBA2-handouts-16-en.zip)
> **PDF pages:** 32

## Page 1: Locks / Row-Level Locks {#source-page-001}

### Copyright

© Postgres Professional, 2016–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Igor Gnatyuk Translated by: Elena Sharafutdinova Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
### Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.
### Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru
### Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

## Page 2: Topics {#source-page-002}

- Exclusive and Shared Row‐Level Locks
- Multitransactions and Freezing
- Implementation of the Wait Queue
- Deadlocks

## Page 3: Row-Level Locks {#source-page-003}

![Source slide page 3: Row-Level Locks](images/page-003.png){#source-figure-003 caption="Source slide page 3: Row-Level Locks"}


### Speaker notes

Although information about row-level locks is stored in tuples on disk, the wait queue for exclusive row locks is organized using tuple object locks.

## Page 4: Architecture {#source-page-004}

**Information is stored only in data pages**
  - xmax field in the tuple header + information bits
  - nothing is stored in RAM
- Unlimited number
  - large number does not impact performance
- Infrastructure
  - wait queue is organized using object locks

### Speaker notes

Due to MVCC, row‐level locks are required only to change data (or to prevent changes), they are unnecessary for reads.

While object locks have a dedicated lock in RAM for each resource, this approach does not work for rows: a separate lock for every row (there could be millions or billions of them) would incur excessive overhead and consume enormous amounts of RAM.

One approach to this problem is lock escalation: if N rows in a table are already locked, attempting to lock one more row triggers a lock on the entire table, and all the row-level locks are released. However, this approach would impact throughput.

PostgreSQL takes a different approach. Information about a row being locked is stored exclusively in the tuple header inside the data page. This information is represented by the ID of the locking transaction (xmax) and additional information bits.

This architecture allows PostgreSQL to support an unlimited number of row-level locks without consuming additional resources or degrading system performance.

The drawback of this approach is the complexity of managing lock queues. For this purpose, PostgreSQL still relies on object-level locks, though a minimal number of them is required (proportional to the number of processes rather than the number of locked rows).

## Page 5: Exclusive Modes {#source-page-005}

**Update No Key Update**
  - row deletion
  - change of any fields,
  - or change of all fields
  - except for key fields
```sql
SELECT FOR UPDATE
SELECT FOR NO KEY UPDATE
```
  - UPDATE
  - UPDATE
  - (with key field changes)
  - (without key field changes)
```sql
DELETE
```

### Speaker notes

There are 4 modes for locking a row (the mode is set in the tuple using additional information bits).

Two of these modes represent exclusive locks, which can be held by only one transaction at a time. The Update mode implies a complete change (or deletion) of a row, while the No Key Update mode allows changes only of fields that are not part of unique indexes (in other words, all foreign keys remain unchanged during such a modification).

The UPDATE command automatically selects the least restrictive appropriate lock mode; rows are typically locked in No Key Update mode.

<https://postgrespro.com/docs/postgresql/16/explicit-locking#LOCKING-ROW> S

## Page 6: Exclusive Modes {#source-page-006}

![Source slide page 6: Exclusive Modes](images/page-006.png){#source-figure-006 caption="Source slide page 6: Exclusive Modes"}


### Speaker notes

When a row is updated or deleted, the xmax field of the current tuple is set to the ID of the current transaction. This xmax value, corresponding to an active transaction serves as a lock.

The same mechanism is used when a row is explicitly locked with SELECT FOR UPDATE, with an additional information bit (xmax lock only) set to indicate that the tuple remains current, even though it is locked.

The lock mode is determined by another hint bit (keys updated).

(In practice, a greater number of bits are used with more complex conditions and checks to maintain compatibility with earlier versions. However, these details are not fundamental.)

If another transaction attempts to update or delete a locked row in an incompatible mode, it is forced to wait for the xmax transaction to complete.

## Page 7: Shared modes {#source-page-007}

**Share Key Share**
  - prevents modification of
  - prevents modification of
  - any fields of the row
  - key fields of the row
  - SELECT FOR SHARE
  - SELECT FOR KEY SHARE
  - and foreign key checks
- Mode Compatibility Matrix
  - Key
  - No Key
  - Share
  - Share
  - Update
  - Update
- Key Share × Share × × No Key Update × × × Update × × × ×

### Speaker notes

Two other modes represent shared locks, which can be held by several transactions simultaneously.

The Share mode is used when we need to read a row while ensuring it cannot be changed by another transaction.

The Key Share mode allows a row to be modified, but only in non-key fields. PostgreSQL automatically uses this mode, for example, during foreign key constraint checks.

The overall compatibility matrix is shown at the bottom of the slide. The matrix shows that:

● Exclusive modes conflict with each other.

● Shared modes are compatible with each other.

● The Key Share mode is compatible with the exclusive No Key Update mode (i.e., we can update non-key fields while ensuring the key remains unchanged).

<https://postgrespro.com/docs/postgresql/16/explicit-locking#LOCKING-ROW> S

## Page 8: Shared modes {#source-page-008}

![Source slide page 8: Shared modes](images/page-008.png){#source-figure-008 caption="Source slide page 8: Shared modes"}


### Speaker notes

We discussed that a lock is represented by the ID of the locking transaction in the xmax field. Shared locks can be held by several transactions, but a single xmax field cannot store several IDs.

To handle shared locks, PostgreSQL uses multitransactions (MultiXact). They are assigned separate IDs that correspond to an entire group rather than a single transaction. To distinguish a multitransaction from a regular one, another information bit (xmax is multi) is used. Detailed information about the group members and the lock modes is stored in the PGDATA/ pg\_multixact/ directory. The most recently accessed data is cached in the server’s shared memory buffers to speed up access.

## Page 9: Freezing Configuration {#source-page-009}

**Multitransaction Parameters**
  - vacuum\_multixact\_freeze\_min\_age
  - = 5 000 000
  - vacuum\_multixact\_freeze\_table\_age
  - = 150 000 000
  - autovacuum\_multixact\_freeze\_max\_age = 400 000 000
  - vacuum\_multixact\_failsafe\_age
  - = 1 600 000 000
- Table Storage Parameters
  - autovacuum\_multixact\_freeze\_min\_age
  - toast.autovacuum\_multixact\_freeze\_min\_age
  - autovacuum\_multixact\_freeze\_table\_age
  - toast.autovacuum\_multixact\_freeze\_table\_age
  - autovacuum\_multixact\_freeze\_max\_age
  - toast.autovacuum\_multixact\_freeze\_max\_age

### Speaker notes

Since multitransactions are assigned separate IDs that are written to the xmax field of tuples, the limited bitness of the counter creates the same challenges as with regular transaction IDs. This refers to the issue of wraparound, which was covered in the Freezing lesson of the MVCC module.

Therefore, IDs of multitransactions require a similar freezing process, i.e., old multixact IDs are replaced with new ones (or with ID of a regular transaction if the lock is currently held by only a single transaction).

Note that regular tuple freezing operates on the xmin field (if a tuple has a non-empty xmax field, it means either the tuple is already obsolete and will be cleaned up, or the xmax transaction was aborted and its ID is of no interest). For multitransactions, the problem lies in the xmax field of a current tuple that stays valid but can be repeatedly locked by different transactions in shared mode.

Multitransaction freezing is controlled by parameters similar to those used for regular freezing.

## Page 10: Row-Level Locks {#source-page-010}

Row‐level locks is the most common type of locks.
Let’s create a table of bank accounts as in the previous lesson.
```sql
=> CREATE DATABASE locks_rows;
```
```sql
CREATE DATABASE
```
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```sql
=> CREATE TABLE accounts(acc_no integer PRIMARY KEY, amount numeric);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
```
```sql
INSERT 0 3
```
Since row lock information is stored only in the tuples, we will use the familiar pageinspect extension.
```sql
=> CREATE EXTENSION pageinspect;
```
```sql
CREATE EXTENSION
```
Create a view that decodes the information bits we are interested in for the first three tuples.
```sql
=> CREATE VIEW accounts_v AS
SELECT '(0,'||lp||')' AS ctid,
       t_xmax as xmax,
       CASE WHEN (t_infomask & 1024) > 0  THEN 't' END AS committed,
       CASE WHEN (t_infomask & 2048) > 0  THEN 't' END AS aborted,
       CASE WHEN (t_infomask & 128) > 0   THEN 't' END AS lock_only,
       CASE WHEN (t_infomask & 4096) > 0  THEN 't' END AS is_multi,
       CASE WHEN (t_infomask2 & 8192) > 0 THEN 't' END AS keys_upd
FROM heap_page_items(get_raw_page('accounts',0))
WHERE lp <= 3
ORDER BY lp;
```
```sql
CREATE VIEW
```
Update the balance of the first account (key unchanged) and the number of the second account (key changed):
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```sql
=> UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
Check the view. Note that it shows only the first three (original) tuples.
```sql
=> SELECT * FROM accounts_v;
```
```text
 ctid  | xmax | committed | aborted | lock_only | is_multi | keys_upd
-------+------+-----------+---------+-----------+----------+----------
 (0,1) |  748 |           |         |           |          |
 (0,2) |  748 |           |         |           |          | t
 (0,3) |    0 |           | t       |           |          |
(3 rows)
```
The rows for the first and second accounts are locked, the xmax field contains the transaction ID of the locking transaction. The bits in the keys\_upd column show that the rows are locked in different modes.
Now in another session, request shared locks on the first and third accounts:
```sql
=> \c locks_rows
```

## Page 11: Page 11 {#source-page-011}

![Source slide page 11: Page 11](images/page-011.png){#source-figure-011 caption="Source slide page 11: Page 11"}

```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 3 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
All the requested locks are compatible with each other. In the tuples we see the following:
```sql
=> SELECT * FROM accounts_v;
```
```text
 ctid  | xmax | committed | aborted | lock_only | is_multi | keys_upd
-------+------+-----------+---------+-----------+----------+----------
 (0,1) |    1 |           |         |           | t        |
 (0,2) |  748 |           |         |           |          | t
 (0,3) |  749 |           |         | t         |          |
(3 rows)
```
The lock\_only column allows to distinguish a regular lock from an update or deletion. We also see that in the first row the regular transaction ID in the xmax column has been replaced by a multitransaction ID, as indicated by the is\_multi column.
To avoid delving into the details of information bits and multitransaction implementation, we can use another extension that presents information about all row‐level locks in a convenient format.
```sql
=> CREATE EXTENSION pgrowlocks;
```
```sql
CREATE EXTENSION
```
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```sql
-[ RECORD 1 ]-----------------------------
locked_row | (0,1)
locker     | 1
multi      | t
xids       | {748,749}
modes      | {"No Key Update","Key Share"}
pids       | {28434,28623}
-[ RECORD 2 ]-----------------------------
locked_row | (0,2)
locker     | 748
multi      | f
xids       | {748}
modes      | {Update}
pids       | {28434}
-[ RECORD 3 ]-----------------------------
locked_row | (0,3)
locker     | 749
multi      | f
xids       | {749}
modes      | {"For Share"}
pids       | {28623}
```
The extension provides information about IDs of transactions, multitransactions, and modes of all locks.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```

## Page 12: Wait Queue {#source-page-012}

![Source slide page 12: Wait Queue](images/page-012.png){#source-figure-012 caption="Source slide page 12: Wait Queue"}


### Speaker notes

To organize the wait queue, an additional object‐lock mechanism is used, with the information stored in RAM and visible in pg\_locks.

Let’s assume that transaction 101 (as shown in the slide) has successfully locked a row in Share mode. The lock information is recorded in the tuple header, the ID of the transaction appears in the xmax field.

## Page 13: Wait Queue {#source-page-013}

![Source slide page 13: Wait Queue](images/page-013.png){#source-figure-013 caption="Source slide page 13: Wait Queue"}


### Speaker notes

Another transaction 102 that wants to acquire a lock accesses the row and sees that it is locked. If the lock modes conflict, the transaction needs to enter a wait queue so the system can wake it up when the lock is released. However, row-level locks do not provide this capability, they are not represented in RAM at all. They are simply bytes inside a data page.

As we saw in the Object-Level Locks lesson, each transaction holds an exclusive lock on its own ID. Since transaction 102 needs to wait for transaction 101 to complete (as row locks are only released when a transaction completes), it requests a lock on ID 101.

When transaction 101 completes, the locked resource is released (or simply disappears if committed), transaction 102 is awakened and can lock the row (by setting xmax = 102 in the corresponding tuple).

Additionally, the waiting transaction holds a lock on a tuple resource, indicating that it is first in the queue.

## Page 14: Wait Queue {#source-page-014}

![Source slide page 14: Wait Queue](images/page-014.png){#source-figure-014 caption="Source slide page 14: Wait Queue"}


### Speaker notes

When other transactions appear that conflict with the existing tuple lock (in our example, these are 112, 122, and 132), they first attempt to acquire a tuple lock on that same tuple.

Since the tuple lock is already held by transaction 102, the other transactions wait for it to be released. This creates a queue where there is one transaction at the front and others waiting behind.

If all arriving transactions joined the queue directly behind transaction 101, a race condition would occur whenever the lock was released. In an unlucky timing scenario, transaction 102 could wait indefinitely for the lock. The two-level locking scheme helps manage this contention more effectively.

We should avoid design decisions that involve frequent updates of the same row. This creates a hot spot that can degrade performance under heavy load.

## Page 15: Wait Queue {#source-page-015}

For convenience, create a view over pg\_locks that rolls up identifiers of different lock types into a single field:
```sql
=> CREATE VIEW locks AS
SELECT pid,
       locktype,
       CASE locktype
         WHEN 'relation' THEN relation::regclass::text
         WHEN 'virtualxid' THEN virtualxid::text
         WHEN 'transactionid' THEN transactionid::text
         WHEN 'tuple' THEN relation::regclass::text||':'||page::text||','||tuple::text
       END AS lockid,
       mode,
       granted
FROM pg_locks;
```
```sql
CREATE VIEW
```
Let one transaction lock a row in shared mode...
```text
S1=> BEGIN;
```
```text
BEGIN
```
```sql
S1=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                752 |          28275
(1 row)
```
```sql
S1=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
..and let another one try to update it:
```text
U1=> BEGIN;
```
```text
BEGIN
```
```sql
U1=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                753 |          28434
(1 row)
```
```sql
U1=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
In the pg\_locks view we can see that the second transaction is waiting for the first to finish (granted = f), while holding a lock on the tuple (locktype = tuple):
```sql
=> SELECT * FROM locks WHERE pid = 28434; -- U1
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28434 | relation      | accounts_pkey | RowExclusiveLock | t
 28434 | relation      | accounts      | RowExclusiveLock | t
 28434 | virtualxid    | 3/13          | ExclusiveLock    | t
 28434 | transactionid | 752           | ShareLock        | f
 28434 | tuple         | accounts:0,1  | ExclusiveLock    | t
 28434 | transactionid | 753           | ExclusiveLock    | t
(6 rows)
```
Instead of manually analyzing pg\_locks to see which process is blocking another one, we can call a function that returns the blocking process ID(s):
```sql
=> SELECT pg_blocking_pids(28434); -- U1
```

## Page 16: Page 16 {#source-page-016}

![Source slide page 16: Page 16](images/page-016.png){#source-figure-016 caption="Source slide page 16: Page 16"}

```text
 pg_blocking_pids
------------------
 {28275}
(1 row)
```
Now a third transaction appears, trying to acquire an incompatible lock.
```text
U2=> BEGIN;
```
```text
BEGIN
```
```sql
U2=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                754 |          28623
(1 row)
```
```sql
U2=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
It enters the queue behind the transaction that holds the tuple lock (locktype = tuple, granted = f):
```sql
=> SELECT * FROM locks WHERE pid = 28623; -- U2
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28623 | relation      | accounts_pkey | RowExclusiveLock | t
 28623 | relation      | accounts      | RowExclusiveLock | t
 28623 | virtualxid    | 4/6           | ExclusiveLock    | t
 28623 | tuple         | accounts:0,1  | ExclusiveLock    | f
 28623 | transactionid | 754           | ExclusiveLock    | t
(5 rows)
```
```sql
=> SELECT pg_blocking_pids(28623); -- U2
```
```text
 pg_blocking_pids
------------------
 {28434}
(1 row)
```

## Page 17: Wait Queue {#source-page-017}

![Source slide page 17: Wait Queue](images/page-017.png){#source-figure-017 caption="Source slide page 17: Wait Queue"}


### Speaker notes

In our example, transaction 101 has locked the row in shared mode. While transaction 102 is waiting for transaction 101 to complete, transaction 133 might come along wishing to acquire a shared lock on the row, which is compatible with transaction 101’s current lock.

Such a transaction does not wait in the queue — it acquires the lock immediately. In this case, the list of blocking transaction IDs 101 and 133 is combined into a multi‐transaction 2, whose ID will be recorded in the xmax field of the tuple.

## Page 18: Wait Queue {#source-page-018}

![Source slide page 18: Wait Queue](images/page-018.png){#source-figure-018 caption="Source slide page 18: Wait Queue"}


### Speaker notes

When transaction 101 completes, transaction 102 will be awakened, but it will not be able to acquire the row‐level lock because the row is still locked by transaction 133. Transaction 102 will be forced to go back to sleep.

Under a continuous stream of shared locks, a write transaction may wait indefinitely. This situation is called locker starvation.

Note that this problem does not occur with object‐level locks (such as relation locks). In this case, each resource has its own lock in random access memory, and all waiting processes form a fair queue.

<https://git.postgresql.org/gitweb/?p=postgresql.git;f=src/backend/access/hea> p/README.tuplock;hb=HEAD

## Page 19: Wait Queue {#source-page-019}

![Source slide page 19: Wait Queue](images/page-019.png){#source-figure-019 caption="Source slide page 19: Wait Queue"}


### Speaker notes

Once transaction 133 completes, transaction 102 will be the first to write its own ID into the xmax field, after which it will release the tuple lock. Then one random transaction from all the remaining ones will manage to acquire the tuple lock and become first in the queue.

## Page 20: Page 20 {#source-page-020}

![Source slide page 20: Page 20](images/page-020.png){#source-figure-020 caption="Source slide page 20: Page 20"}

In our experiment, a transaction that tries to lock the row in shared mode proceeds without waiting.
```sql
S2=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
S2=> BEGIN;
```
```text
BEGIN
```
```sql
S2=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                755 |          29574
(1 row)
```
```sql
S2=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
```
```text
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
```
The tuple now contains a multitransaction:
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```text
-[ RECORD 1 ]-------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {752,755}
modes      | {Share,Share}
pids       | {28275,29574}
```
After one of the transactions holding the row in shared mode finishes, another one continues to hold the lock.
```text
S1=> COMMIT;
```
```text
COMMIT
```
The transaction that is first in the queue now waits for the remaining transaction to finish.
```sql
=> SELECT * FROM locks WHERE pid = 28434; -- U1
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28434 | relation      | accounts_pkey | RowExclusiveLock | t
 28434 | relation      | accounts      | RowExclusiveLock | t
 28434 | virtualxid    | 3/13          | ExclusiveLock    | t
 28434 | tuple         | accounts:0,1  | ExclusiveLock    | t
 28434 | transactionid | 755           | ShareLock        | f
 28434 | transactionid | 753           | ExclusiveLock    | t
(6 rows)
```
> [!NOTE]
> **Note**
> Note that the xmax field still contains the ID of the multitransaction, even though one of its member transactions has already completed. This ID can be replaced by another one (ID of a new multitransaction or a regular transaction) during vacuuming.
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```text
-[ RECORD 1 ]-------------
locked_row | (0,1)
locker     | 2
multi      | t
xids       | {752,755}
modes      | {Share,Share}
pids       | {0,29574}
```
Now the second transaction holding the shared lock also finishes.
```text
S2=> COMMIT;
```
```text
COMMIT
```
The transaction that was first in the queue gains access to the tuple:

## Page 21: Page 21 {#source-page-021}

![Source slide page 21: Page 21](images/page-021.png){#source-figure-021 caption="Source slide page 21: Page 21"}

```sql
UPDATE 1
```
```sql
=> SELECT * FROM pgrowlocks('accounts') \gx
```
```sql
-[ RECORD 1 ]-----------------
locked_row | (0,1)
locker     | 753
multi      | f
xids       | {753}
modes      | {"No Key Update"}
pids       | {28434}
```
The remaining transaction now acquires the tuple lock and takes the first position in the queue:
```sql
=> SELECT * FROM locks WHERE pid = 28623; -- U2
```
```text
  pid  |   locktype    |    lockid     |       mode       | granted
-------+---------------+---------------+------------------+---------
 28623 | relation      | accounts_pkey | RowExclusiveLock | t
 28623 | relation      | accounts      | RowExclusiveLock | t
 28623 | virtualxid    | 4/6           | ExclusiveLock    | t
 28623 | tuple         | accounts:0,1  | ExclusiveLock    | t
 28623 | transactionid | 754           | ExclusiveLock    | t
 28623 | transactionid | 753           | ShareLock        | f
(6 rows)
```
Let’s roll back the changes.
```text
U1=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
U2=> ROLLBACK;
```
```text
ROLLBACK
```
### How to Avoid Waiting for a Lock?
Sometimes it is more convenient to receive an error immediately when a resource is busy, rather than waiting for the lock to be released. The application can catch and handle such an error.
For this purpose, several SQL commands (such as SELECT and some variants of ALTER) allow to specify the NOWAIT keyword. Lock the table by updating the first row:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount + 1 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```text
=> LOCK TABLE accounts NOWAIT; -- IN ACCESS EXCLUSIVE MODE
```
```text
ERROR:  could not obtain lock on relation "accounts"
```
The transaction receives an error immediately.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
For advisory locks, there are functions that either acquire the lock immediately or return false in case of failure:
```text
=> \df pg_try_advisory*
```

## Page 22: Page 22 {#source-page-022}

```text
                                       List of functions
   Schema   |               Name               | Result data type | Argument data types |
Type
------------+----------------------------------+------------------+---------------------+-
-----
 pg_catalog | pg_try_advisory_lock             | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_lock             | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_lock_shared      | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_xact_lock        | boolean          | integer, integer    |
func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | bigint              |
func
 pg_catalog | pg_try_advisory_xact_lock_shared | boolean          | integer, integer    |
func
(8 rows)
```
The UPDATE and DELETE commands do not support NOWAIT. You can first execute the command
```sql
SELECT ... FOR UPDATE NOWAIT; -- or FOR NO KEY UPDATE NOWAIT
```
and then, if the rows are locked successfully, modify or delete them. For example:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts WHERE acc_no = 1 FOR UPDATE NOWAIT;
```
```text
ERROR:  could not obtain lock on row in relation "accounts"
```
We get an error immediately without attempting to execute DELETE or UPDATE.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
Another way to avoid waiting for row locks to be released is to use the SKIP LOCKED clause. Lock one row without specifying a particular account number:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE SKIP LOCKED LIMIT 1;
```
```text
 acc_no | amount
--------+---------
      2 | 2000.00
(1 row)
```
In this case, the command skips the already locked first row and immediately acquires a lock on the second row.
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
For commands not related to row locking, NOWAIT cannot be used. In this case, we can set a short wait timeout. (By default, the timeout is zero, meaning endless waiting):
```text
=> SET lock_timeout = '1s';
```
```text
SET
```
```sql
=> ALTER TABLE accounts DROP COLUMN amount;
```
```text
ERROR:  canceling statement due to lock timeout
```
We get an error without a long wait for the resource to be released.
```text
=> RESET lock_timeout;
```
```text
RESET
```
During vacuuming, we can specify that it should skip a table if it cannot acquire the lock immediately. This option becomes

## Page 23: Page 23 {#source-page-023}

particularly important when vacuuming the entire database:
```sql
=> VACUUM (skip_locked);
```
```sql
VACUUM
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```

## Page 24: Deadlocks {#source-page-024}

![Source slide page 24: Deadlocks](images/page-024.png){#source-figure-024 caption="Source slide page 24: Deadlocks"}

**Detected by searching for cycles in the wait graph**
  - check is performed after waiting for deadlock\_timeout
- One of the transactions is aborted, the others proceed

### Speaker notes

A deadlock can occur when one transaction attempts to acquire a resource already held by another transaction, while that another transaction is simultaneously trying to acquire a resource held by the first one. A deadlock is also possible with several transactions; the slide shows an example involving three transactions.

A wait graph provides a clear visual representation of a deadlock. To create this graph, we remove specific resources and keep only transactions, marking their wait dependencies. If the graph contains a cycle (meaning that we can trace a path of arrows that leads back to the starting transaction) that is a deadlock.

Note that the locked resources are not necessarily rows; deadlock detection depends not on the resource type, but on the circular wait dependency among the transactions waiting for those resources to be released.

Once a deadlock has occurred, the involved transactions are trapped in an endless wait. Therefore, PostgreSQL automatically monitors for deadlocks. The check is performed when any transaction has been waiting for a resource longer than the time specified in the deadlock\_timeout parameter. If a deadlock is detected, one of the transactions is forcibly aborted so that the others can proceed.

Deadlocks usually indicate that the application is poorly designed. Messages in the server log or a rising value of pg\_stat\_database.deadlocks are reasons to reconsider the causes.

<https://postgrespro.com/docs/postgresql/16/explicit-locking#LOCKING-DEA> DLOCKS

## Page 25: Deadlocks {#source-page-025}

![Source slide page 25: Deadlocks](images/page-025.png){#source-figure-025 caption="Source slide page 25: Deadlocks"}

Deadlocks commonly occur when transactions lock rows in a different order.
The first transaction intends to transfer 100 rubles from the first account to the second. To do this, it first reduces the balance of the first account:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
At the same time, the second transaction intends to transfer 10 rubles from the second account to the first. It starts reducing the balance of the second account:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
Now the first transaction tries to increase the second account, but finds the row locked.
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
```
Then the second transaction tries to increase the first account, but it also gets blocked.
```sql
=> UPDATE accounts SET amount = amount + 10.00 WHERE acc_no = 1;
```
A circular wait occurs that will never be resolved on its own. Therefore, when the server detects such a cycle, it aborts one of the transactions.
```text
ERROR:  deadlock detected
DETAIL:  Process 28434 waits for ShareLock on transaction 761; blocked by process 28623.
Process 28623 waits for ShareLock on transaction 760; blocked by process 28434.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
ROLLBACK
```
```text
=> COMMIT;
```
```text
COMMIT
```
The right way to perform such operations is to lock resources in the same order. For example, in this case we can lock the accounts in ascending order by account number.

## Page 26: Takeaways {#source-page-026}

**Row locks are stored in data pages**
  - due to their potentially large number
- Wait queues and deadlock detection are provided by object-level locks
  - complex locking schemes become necessary

## Page 27: Practice {#source-page-027}

1. Reproduce a scenario where the same row is updated by three UPDATE commands in different sessions. Examine the resulting locks in the pg\_locks view and make sure that you understand all of them.
- 2. Create a three-way deadlock. Can you analyze what happened by examining the log file afterward?
- 3. Can two transactions, each executing a single UPDATE command on the same table, deadlock each other? Try to reproduce this scenario.

## Page 28: 1. Row-Level Locks during Multiple Updates {#source-page-028}

For the sake of simplicity, let’s create a table without a primary key.
```sql
=> CREATE DATABASE locks_rows;
```
```sql
CREATE DATABASE
```
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```sql
=> CREATE TABLE accounts(acc_no integer, amount numeric);
```
```sql
CREATE TABLE
```
```sql
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
```
```sql
INSERT 0 3
```
Create a view over pg\_locks as in the demonstration:
```sql
=> CREATE VIEW locks AS
SELECT pid,
       locktype,
       CASE locktype
         WHEN 'relation' THEN relation::REGCLASS::text
         WHEN 'virtualxid' THEN virtualxid::text
         WHEN 'transactionid' THEN transactionid::text
         WHEN 'tuple' THEN relation::REGCLASS::text||':'||page::text||','||tuple::text
       END AS lockid,
       mode,
       granted
FROM pg_locks;
```
```sql
CREATE VIEW
```
The first transaction updates and thereby locks the row:
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                747 |          62025
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
The second transaction does the same:
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                748 |          62227
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
And the third one:
```sql
=> \c locks_rows
```
```text
You are now connected to database "locks_rows" as user "student".
```
```text
=> BEGIN;
```

## Page 29: Page 29 {#source-page-029}

```text
BEGIN
```
```sql
=> SELECT pg_current_xact_id(), pg_backend_pid();
```
```text
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                749 |          62348
(1 row)
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
Locks for the first transaction:
```sql
=> SELECT * FROM locks WHERE pid = 62025;
```
```text
  pid  |   locktype    |  lockid  |       mode       | granted
-------+---------------+----------+------------------+---------
 62025 | relation      | pg_locks | AccessShareLock  | t
 62025 | relation      | locks    | AccessShareLock  | t
 62025 | relation      | accounts | RowExclusiveLock | t
 62025 | virtualxid    | 6/6      | ExclusiveLock    | t
 62025 | transactionid | 747      | ExclusiveLock    | t
(5 rows)
```
Relation locks on pg\_locks and locks in AccessShareLock mode are set on relations being read. A relation lock on the accounts in RowExclusiveLock mode is set when modifying the table. virtualxid and transactionid locks in ExclusiveLock mode are held by the transaction itself.
Locks for the second transaction:
```sql
=> SELECT * FROM locks WHERE pid = 62227;
```
```text
  pid  |   locktype    |    lockid    |       mode       | granted
-------+---------------+--------------+------------------+---------
 62227 | relation      | accounts     | RowExclusiveLock | t
 62227 | virtualxid    | 3/12         | ExclusiveLock    | t
 62227 | transactionid | 747          | ShareLock        | f
 62227 | transactionid | 748          | ExclusiveLock    | t
 62227 | tuple         | accounts:0,1 | ExclusiveLock    | t
(5 rows)
```
Compared to the first transaction:
The locks on pg\_locks and locks are absent because the second transaction has not accessed these relations. The transaction is waiting to acquire a transactionid lock for the first transaction in ShareLock mode. A tuple lock is held on the row being updated.
Locks for the third transaction:
```sql
=> SELECT * FROM locks WHERE pid = 62348;
```
```text
  pid  |   locktype    |    lockid    |       mode       | granted
-------+---------------+--------------+------------------+---------
 62348 | relation      | accounts     | RowExclusiveLock | t
 62348 | virtualxid    | 4/5          | ExclusiveLock    | t
 62348 | tuple         | accounts:0,1 | ExclusiveLock    | f
 62348 | transactionid | 749          | ExclusiveLock    | t
(4 rows)
```
The transaction is waiting to acquire a tuple lock on the row being updated.
You can see the overall picture of current waits in the pg\_stat\_activity view. For convenience, you can also include information about blocking processes:
```sql
=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend';
```
```text
  pid  | wait_event_type |  wait_event   | pg_blocking_pids
-------+-----------------+---------------+------------------
 62227 | Lock            | transactionid | {62025}
 62348 | Lock            | tuple         | {62227}
 62025 |                 |               | {}
(3 rows)
```
```text
=> ROLLBACK;
```

## Page 30: Page 30 {#source-page-030}

![Source slide page 30: Page 30](images/page-030.png){#source-figure-030 caption="Source slide page 30: Page 30"}

```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
```sql
UPDATE 1
```
```text
=> ROLLBACK;
```
```text
ROLLBACK
```
### 2. Three-Way Deadlock
Let’s reproduce a three-way deadlock scenario.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 2;
```
```sql
UPDATE 1
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 3;
```
```sql
UPDATE 1
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
```
```sql
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```
```text
ERROR:  deadlock detected
DETAIL:  Process 62025 waits for ShareLock on transaction 751; blocked by process 62227.
Process 62227 waits for ShareLock on transaction 752; blocked by process 62348.
Process 62348 waits for ShareLock on transaction 750; blocked by process 62025.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,2) in relation "accounts"
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
```sql
UPDATE 1
```
```text
=> COMMIT;
```
```text
COMMIT
```
```text
=> COMMIT;
```
```text
ROLLBACK
```
Here is the information about the deadlock you can find in the WAL:
```sh
student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log
```
```sql
2026-06-11 17:43:34.495 MSK [62025] student@locks_rows ERROR:  deadlock detected
2026-06-11 17:43:34.495 MSK [62025] student@locks_rows DETAIL:  Process 62025 waits for
ShareLock on transaction 751; blocked by process 62227.
Process 62227 waits for ShareLock on transaction 752; blocked by process 62348.
Process 62348 waits for ShareLock on transaction 750; blocked by process 62025.
Process 62025: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
Process 62227: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
Process 62348: UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
```

## Page 31: 3. Deadlock of Two UPDATE Statements {#source-page-031}

![Source slide page 31: 3. Deadlock of Two UPDATE Statements](images/page-031.png){#source-figure-031 caption="Source slide page 31: 3. Deadlock of Two UPDATE Statements"}

```sql
2026-06-11 17:43:34.495 MSK [62025] student@locks_rows HINT:  See server log for query
details.
2026-06-11 17:43:34.495 MSK [62025] student@locks_rows CONTEXT:  while updating tuple
(0,2) in relation "accounts"
2026-06-11 17:43:34.495 MSK [62025] student@locks_rows STATEMENT:  UPDATE accounts SET
amount = amount + 100.00 WHERE acc_no = 2;
```
The UPDATE command locks rows as it updates them. This does not happen all at once.
A deadlock can occur if one command updates rows in one order while another one updates them in a different order. This can happen if different execution plans are generated for the commands, for example, one performs a sequential scan while the other uses an index.
It is difficult to create this situation even intentionally, so in real‐world workloads it is highly unlikely to occur. The easiest way to demonstrate this is using cursors, since they allow you to control the reading order.
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DECLARE c1 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE;
```
```text
DECLARE CURSOR
```
```text
=> BEGIN;
```
```text
BEGIN
```
```sql
=> DECLARE c2 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no DESC -- in reverse order
FOR UPDATE;
```
```text
DECLARE CURSOR
```
```text
=> FETCH c1;
```
```text
 acc_no | amount
--------+---------
      1 | 1100.00
(1 row)
```
```text
=> FETCH c2;
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
```text
=> FETCH c1;
```
```text
 acc_no | amount
--------+---------
      2 | 1900.00
(1 row)
```
```text
=> FETCH c2;
```
The second command is waiting for a lock...
```text
=> FETCH c1;
```
A deadlock has occurred. After a while:
```text
ERROR:  deadlock detected
DETAIL:  Process 62348 waits for ShareLock on transaction 753; blocked by process 62227.
Process 62227 waits for ShareLock on transaction 754; blocked by process 62348.
HINT:  See server log for query details.
CONTEXT:  while locking tuple (0,8) in relation "accounts"
```
```text
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
```
```text
=> COMMIT;
```

## Page 32: Page 32 {#source-page-032}

```text
COMMIT
```
```text
=> COMMIT;
```
```text
ROLLBACK
```