Skip to content

13 Lesson 13: Locks / Row-Level Locks

Native Markdown source page: Locks / Row-Level Locks
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 Archive: DBA2-handouts-16-en.zip PDF pages: 32

Page 1: Locks / Row-Level Locks

© 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

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

Page 3: Row-Level Locks

Source slide page 3: Row-Level Locks{#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

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

Update No Key Update

  • row deletion
  • change of any fields,
  • or change of all fields
  • except for key fields
SELECT FOR UPDATE
SELECT FOR NO KEY UPDATE
  • UPDATE
  • UPDATE
  • (with key field changes)
  • (without key field changes)
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 slide page 6: Exclusive Modes{#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

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 slide page 8: Shared modes{#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

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

Row‐level locks is the most common type of locks. Let’s create a table of bank accounts as in the previous lesson.

=> CREATE DATABASE locks_rows;
CREATE DATABASE
=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
=> CREATE TABLE accounts(acc_no integer PRIMARY KEY, amount numeric);
CREATE TABLE
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
INSERT 0 3

Since row lock information is stored only in the tuples, we will use the familiar pageinspect extension.

=> CREATE EXTENSION pageinspect;
CREATE EXTENSION

Create a view that decodes the information bits we are interested in for the first three tuples.

=> 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;
CREATE VIEW

Update the balance of the first account (key unchanged) and the number of the second account (key changed):

=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
UPDATE 1
=> UPDATE accounts SET acc_no = 20 WHERE acc_no = 2;
UPDATE 1

Check the view. Note that it shows only the first three (original) tuples.

=> SELECT * FROM accounts_v;
 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:

=> \c locks_rows

Page 11: Page 11

Source slide page 11: Page 11{#source-figure-011 caption=“Source slide page 11: Page 11”}

You are now connected to database "locks_rows" as user "student".
=> BEGIN;
BEGIN
=> SELECT * FROM accounts WHERE acc_no = 1 FOR KEY SHARE;
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)
=> SELECT * FROM accounts WHERE acc_no = 3 FOR SHARE;
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)

All the requested locks are compatible with each other. In the tuples we see the following:

=> SELECT * FROM accounts_v;
 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.

=> CREATE EXTENSION pgrowlocks;
CREATE EXTENSION
=> SELECT * FROM pgrowlocks('accounts') \gx
-[ 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.

=> ROLLBACK;
ROLLBACK
=> ROLLBACK;
ROLLBACK

Page 12: Wait Queue

Source slide page 12: Wait Queue{#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 slide page 13: Wait Queue{#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 slide page 14: Wait Queue{#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

For convenience, create a view over pg_locks that rolls up identifiers of different lock types into a single field:

=> 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;
CREATE VIEW

Let one transaction lock a row in shared mode…

S1=> BEGIN;
BEGIN
S1=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                752 |          28275
(1 row)
S1=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)

..and let another one try to update it:

U1=> BEGIN;
BEGIN
U1=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                753 |          28434
(1 row)
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):

=> SELECT * FROM locks WHERE pid = 28434; -- U1
  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):

=> SELECT pg_blocking_pids(28434); -- U1

Page 16: Page 16

Source slide page 16: Page 16{#source-figure-016 caption=“Source slide page 16: Page 16”}

 pg_blocking_pids
------------------
 {28275}
(1 row)

Now a third transaction appears, trying to acquire an incompatible lock.

U2=> BEGIN;
BEGIN
U2=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                754 |          28623
(1 row)
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):

=> SELECT * FROM locks WHERE pid = 28623; -- U2
  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)
=> SELECT pg_blocking_pids(28623); -- U2
 pg_blocking_pids
------------------
 {28434}
(1 row)

Page 17: Wait Queue

Source slide page 17: Wait Queue{#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 slide page 18: Wait Queue{#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 slide page 19: Wait Queue{#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 slide page 20: Page 20{#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.

S2=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
S2=> BEGIN;
BEGIN
S2=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                755 |          29574
(1 row)
S2=> SELECT * FROM accounts WHERE acc_no = 1 FOR SHARE;
 acc_no | amount
--------+---------
      1 | 1000.00
(1 row)

The tuple now contains a multitransaction:

=> SELECT * FROM pgrowlocks('accounts') \gx
-[ 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.

S1=> COMMIT;
COMMIT

The transaction that is first in the queue now waits for the remaining transaction to finish.

=> SELECT * FROM locks WHERE pid = 28434; -- U1
  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.

=> SELECT * FROM pgrowlocks('accounts') \gx
-[ 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.

S2=> COMMIT;
COMMIT

The transaction that was first in the queue gains access to the tuple:

Page 21: Page 21

Source slide page 21: Page 21{#source-figure-021 caption=“Source slide page 21: Page 21”}

UPDATE 1
=> SELECT * FROM pgrowlocks('accounts') \gx
-[ 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:

=> SELECT * FROM locks WHERE pid = 28623; -- U2
  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.

U1=> ROLLBACK;
ROLLBACK
UPDATE 1
U2=> ROLLBACK;
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:

=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount + 1 WHERE acc_no = 1;
UPDATE 1
=> BEGIN;
BEGIN
=> LOCK TABLE accounts NOWAIT; -- IN ACCESS EXCLUSIVE MODE
ERROR:  could not obtain lock on relation "accounts"

The transaction receives an error immediately.

=> ROLLBACK;
ROLLBACK

For advisory locks, there are functions that either acquire the lock immediately or return false in case of failure:

=> \df pg_try_advisory*

Page 22: Page 22

                                       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

SELECT ... FOR UPDATE NOWAIT; -- or FOR NO KEY UPDATE NOWAIT

and then, if the rows are locked successfully, modify or delete them. For example:

=> BEGIN;
BEGIN
=> SELECT * FROM accounts WHERE acc_no = 1 FOR UPDATE NOWAIT;
ERROR:  could not obtain lock on row in relation "accounts"

We get an error immediately without attempting to execute DELETE or UPDATE.

=> ROLLBACK;
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:

=> BEGIN;
BEGIN
=> SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE SKIP LOCKED LIMIT 1;
 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.

=> ROLLBACK;
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):

=> SET lock_timeout = '1s';
SET
=> ALTER TABLE accounts DROP COLUMN amount;
ERROR:  canceling statement due to lock timeout

We get an error without a long wait for the resource to be released.

=> RESET lock_timeout;
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

particularly important when vacuuming the entire database:

=> VACUUM (skip_locked);
VACUUM
=> ROLLBACK;
ROLLBACK

Page 24: Deadlocks

Source slide page 24: Deadlocks{#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 slide page 25: Deadlocks{#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:

=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
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:

=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount - 10.00 WHERE acc_no = 2;
UPDATE 1

Now the first transaction tries to increase the second account, but finds the row locked.

=> 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.

=> 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.

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"
UPDATE 1
=> COMMIT;
ROLLBACK
=> COMMIT;
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

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

  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.
    1. Create a three-way deadlock. Can you analyze what happened by examining the log file afterward?
    1. 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

For the sake of simplicity, let’s create a table without a primary key.

=> CREATE DATABASE locks_rows;
CREATE DATABASE
=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
=> CREATE TABLE accounts(acc_no integer, amount numeric);
CREATE TABLE
=> INSERT INTO accounts VALUES (1,1000.00),(2,2000.00),(3,3000.00);
INSERT 0 3

Create a view over pg_locks as in the demonstration:

=> 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;
CREATE VIEW

The first transaction updates and thereby locks the row:

=> BEGIN;
BEGIN
=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                747 |          62025
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
UPDATE 1

The second transaction does the same:

=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
=> BEGIN;
BEGIN
=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                748 |          62227
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;

And the third one:

=> \c locks_rows
You are now connected to database "locks_rows" as user "student".
=> BEGIN;

Page 29: Page 29

BEGIN
=> SELECT pg_current_xact_id(), pg_backend_pid();
 pg_current_xact_id | pg_backend_pid
--------------------+----------------
                749 |          62348
(1 row)
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;

Locks for the first transaction:

=> SELECT * FROM locks WHERE pid = 62025;
  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:

=> SELECT * FROM locks WHERE pid = 62227;
  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:

=> SELECT * FROM locks WHERE pid = 62348;
  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:

=> SELECT pid, wait_event_type, wait_event, pg_blocking_pids(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend';
  pid  | wait_event_type |  wait_event   | pg_blocking_pids
-------+-----------------+---------------+------------------
 62227 | Lock            | transactionid | {62025}
 62348 | Lock            | tuple         | {62227}
 62025 |                 |               | {}
(3 rows)
=> ROLLBACK;

Page 30: Page 30

Source slide page 30: Page 30{#source-figure-030 caption=“Source slide page 30: Page 30”}

ROLLBACK
UPDATE 1
=> ROLLBACK;
ROLLBACK
UPDATE 1
=> ROLLBACK;
ROLLBACK

2. Three-Way Deadlock

Let’s reproduce a three-way deadlock scenario.

=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 1;
UPDATE 1
=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 2;
UPDATE 1
=> BEGIN;
BEGIN
=> UPDATE accounts SET amount = amount - 100.00 WHERE acc_no = 3;
UPDATE 1
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 2;
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 3;
=> UPDATE accounts SET amount = amount + 100.00 WHERE acc_no = 1;
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"
UPDATE 1
=> COMMIT;
COMMIT
UPDATE 1
=> COMMIT;
COMMIT
=> COMMIT;
ROLLBACK

Here is the information about the deadlock you can find in the WAL:

student$ tail -n 10 /var/log/postgresql/postgresql-16-main.log
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 slide page 31: 3. Deadlock of Two UPDATE Statements{#source-figure-031 caption=“Source slide page 31: 3. Deadlock of Two UPDATE Statements”}

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.

=> BEGIN;
BEGIN
=> DECLARE c1 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no
FOR UPDATE;
DECLARE CURSOR
=> BEGIN;
BEGIN
=> DECLARE c2 CURSOR FOR
SELECT * FROM accounts ORDER BY acc_no DESC -- in reverse order
FOR UPDATE;
DECLARE CURSOR
=> FETCH c1;
 acc_no | amount
--------+---------
      1 | 1100.00
(1 row)
=> FETCH c2;
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
=> FETCH c1;
 acc_no | amount
--------+---------
      2 | 1900.00
(1 row)
=> FETCH c2;

The second command is waiting for a lock…

=> FETCH c1;

A deadlock has occurred. After a while:

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"
 acc_no | amount
--------+---------
      3 | 3000.00
(1 row)
=> COMMIT;

Page 32: Page 32

COMMIT
=> COMMIT;
ROLLBACK