Locks
Row-Level Locks
16
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.
2
Topics
Exclusive and Shared Row‑Level Locks
Multitransactions and Freezing
Implementation of the Wait Queue
Deadlocks
3
Row-Level Locks
object-level
locks
relation-level
locks
locks
in RAM
row-level
locks
predicate
locks
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.
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
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).
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
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.
6
xmin committed
xmin aborted
xmax committed
xmax aborted
t
xmin xmax data
100 42, FOO
(0,1)
item identifier
normal
101
ID of
the locking
transaction
Exclusive Modes
xmax lock only
keys updated
have the
key field values
changed?
SELECT
FOR …
UPDATE
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.
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 × × × ×
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).
8
xmin committed
xmin aborted
xmax committed
xmax aborted
t
Shared modes
xmin xmax data
100 42,FOO
(0,1)
item identifier
normal
1
ID of
multitransaction
(multixact ID)
xmax is multi
101
Key Share
102
No Key Update
PGDATA/pg_multixact/
t
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.
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
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.
11
Wait Queue
xmin xmax
100
...
101(0,1)
xid 101
T 101
Share
holds
a shared
lock
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.
12
Wait Queue
T 102
xmin xmax
100
...
101(0,1)
tuple 0,1
Update
xid 101
T 101
Share
waits
for T 101
to finish
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.
13
Wait Queue
T 102
xmin xmax
100
...
101(0,1)
T 122
T 112
T 132
tuple 0,1
Update
Update
Update
Update
xid 101
T 101
Share
first in
the queue
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.
15
Wait Queue
T 102
xmin xmax
100
...
2(0,1)
T 122
T 101
T 112
T 132
tuple 0,1 xid 101
Share
Update
Update
Update
Update
T 133
Share
multitransaction
“I just have
a question”
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.
16
Wait Queue
T 102
xmin xmax
100
...
2(0,1)
T 122
T 133
T 112
T 132
tuple 0,1
Share
Update
Update
Update
Update
xid 133
now waiting
for transaction 133
to complete
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.
17
Wait Queue
xmin xmax
100
...
102(0,1)
T 102
Update
xid 102
holds
an exclusive
lock
T 122
T 132
Update
Update
T 112
Update
tuple 0,1
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.
19
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
Deadlocks
T 3
T 2
T 1
as a rule,
because of
poor design of
application
T 2
T 1
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.
21
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
22
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.
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.
