Locks
Object-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
General Information about Locks
Relation- and Other Object-Level Locks
Predicate Locks
3
General Information
Lock Function and Mechanism
Locked Resources
Performance Factors
Duration of Locks
4
Locks
Function: coordinate concurrent access to shared resources
Mechanism
before accessing a resource, a process acquires a lock,
and releases it after access
locks lead to queues and waits
Alternatives
multiversion concurrency control — several data versions
optimistic locking — processes are not locked,
but an error occurs in case of a conflict
Locks are used to coordinate concurrent access to shared resources.
Concurrent access means simultaneous access by several processes.
These processes can run either in parallel (if the hardware allows) or
sequentially in a time-sharing mode.
Locks are unnecessary if there is no concurrency (only one process
accesses the data at a time) or if there is no shared resource (for example, a
shared buffer cache requires locks, while a local one does not).
Before accessing a resource protected by a lock, a process must acquire
that lock. When the process no longer needs the resource, it releases the
lock so that other processes can use it.
Certainly, sometimes a lock cannot be acquired: the resource might already
be in use by another process. Then the process either joins a wait queue or
retries acquiring the lock after a certain interval. In any case, the process is
forced to idle while waiting for the lock to be released.
Sometimes, other, non-blocking strategies can be used. For example, in the
the MVCC module we discussed the MVCC mechanism. Another example is
optimistic locking, which does not lock the process but results in an error if a
conflict occurs.
5
Resources
Resource
anything that can be identified
Examples of resources
stored objects: pages, tables, tuples, etc.
data structures in shared memory (hash tables, buffers, etc.)
abstract resources (integers)
In general, a lock can protect any resource, as long as the resource can be
unambiguously identified.
For example, a DBMS object such as a data page (identified by the filename
and offset), a table (identified by oid in the system catalog), or a tuple
(identified by its page and offset).
A data structure in RAM such as a hash table, buffer, etc. (identified by a
previously assigned number) can also be a resource.
Sometimes it is even convenient to use abstract resources that have no
physical meaning (identified by an integer).
6
Performance Factors
Lock granularity
granularity, level in the resource hierarchy
for example: table → page → rows, hash table → buckets
higher granularity means greater potential for parallelism
Lock modes
mode compatibility is determined by a matrix
more compatible modes provide greater opportunities for parallelism
Locking performance depends on numerous factors. We will examine only a
few of them.
Granularity (level of detail) is important when resources form a hierarchy.
For example, a table consists of pages which contain rows. All these objects
can serve as resources. If a process needs only a few rows, but a lock is set
at the table level, other processes will be unable to work with the remaining
rows.
Therefore, the higher the granularity, the greater the potential for parallelism.
However, it also increases the number of locks, whose information must be
stored somewhere.
Locks can be acquired in various modes. The names of lock modes can be
arbitrary; what matters is compatibility between modes.
The mode that is incompatible with any other mode is called exclusive.
When modes are compatible, several processes can acquire a lock
simultaneously; such modes are called shared.
In general, the more compatible modes exist, the greater the potential for
parallelism.
7
Duration of Locks
Long-term locks
usually acquired up to the end of the transaction and pertain to stored data
numerous lock modes
developed “heavyweight” infrastructure, monitoring
Short-term locks
usually acquired for fractions of a second and pertain to structures in RAM
minimal number of lock modes
“lightweight” infrastructure, monitoring may not be available
Based on duration, locks can be divided into long-term and short-term.
Long-term locks are acquired for a potentially long time (usually up to the
end of the transaction) and most often relate to such resources as tables
(relations) and rows. As a rule, PostgreSQL controls such locks
automatically, though a user still has certain control over this process.
A large number of modes is typical of long-term locks to enable as many
concurrent data operations as possible.
Long-term locks are usually supported by extensive infrastructure (e.g.,
queue management and deadlock detection) and monitoring tools.
Short-term locks are acquired for a short time (from a few CPU cycles to
fractions of seconds) and usually relate to data structures in the shared
memory. PostgreSQL controls such locks in a fully automated manner, you
only need to be aware of their existence.
The minimum of modes (exclusive and shared) and a simple infrastructure
are typical of short-term locks. Monitoring may not be available.
8
Types of Locks
locks
in RAM
row-level
locks
predicate
locks
object-level
locks
relation-level
locks
PostgreSQL uses various types of locks.
Object-level locks pertain to long-term, “heavyweight” locks. Relations and
other objects (e.g., schemas) serve as resources here.
Another class of locks (optimistic) is predicate locks.
Information about all these locks is stored uniformly in RAM. We will
examine these types of locks in detail later in this lesson.
Among long-term locks, row-level locks are of particular interest. They are
implemented differently from other long-term locks due to their potentially
enormous number (imagine updating a million of rows in one transaction).
We will cover these locks in the Row-Level Locks lesson.
Short-term locks include various locks on RAM structures. We will cover
them in the Locks in RAM lesson.
9
Object-Level Locks
object-level
locks
relation-level
locks
locks
in RAM
row-level
locks
predicate
locks
10
Architecture
Information in the shared memory of the server
pg_locks view:
locktype — type of locked resource
mode — lock mode
Limited number
max_locks_per_transaction × max_connections
Infrastructure
wait queue: waiting processes do not consume resources
deadlock detection
Let’s begin our study of locks with object-level locks, such as tables,
indexes, pages, IDs of transactions, etc. These locks protect objects against
change or use while an object is being modified, and serve a number of
other needs.
Information about object-level locks is stored in the server’s shared memory.
The number of locks is limited by the product of the values of two
parameters: max_locks_per_transaction and max_connections (64 × 100 by
default). The pool of locks is one for all transactions, that is, a transaction
can acquire more locks than max_locks_per_transaction. The only important
thing is that the total number of locks in the system does not exceed the set
limit.
You can see all the locks in the pg_locks view.
When a resource is already locked and a transaction attempts to acquire a
lock in an incompatible mode, it joins a wait queue and waits until the lock is
released. Waiting transactions do not consume CPU resources: they fall
asleep and wake up only when the resource is released. Several SQL
commands allow to specify the NOWAIT keyword: in this case, trying to
acquire a busy resource results in an error instead of waiting.
A deadlock situation is possible, where two or more transactions are waiting
for each other. PostgreSQL automatically detects such deadlocks and aborts
the endless wait.
11
Resource Type: Relation
Modes
Access Share SELECT
Row Share SELECT FOR UPDATE/SHARE
Row Exclusive UPDATE, DELETE, INSERT, MERGE
Share Update Exclusive VACUUM, ANALYZE, ALTER TABLE,
CREATE INDEX CONCURRENTLY
Share CREATE INDEX
Share Row Exclusive CREATE TRIGGER, ALTER TABLE
Exclusive REFRESH MATERIALIZED VIEW
CONCURRENTLY
Access Exclusive DROP, TRUNCATE, REINDEX, VACUUM FULL,
LOCK TABLE, ALTER TABLE,
REFRESH MATERIALIZED VIEW
Relation Locks
allow
concurrent
change
in the table
A particularly important type of locks is relation (tables, indexes, sequences,
etc.) locks. These locks are identified by the relation value in the
pg_locks.locktype column.
They have 8 different modes as shown on the slide together with examples
of SQL commands that use each mode. The compatibility matrix showing
which locks can be acquired simultaneously is provided in the
documentation.
Such a variety of lock modes is to allow as many operations as possible that
refer to the same table (or index, etc.) to run concurrently.
The weakest mode is Access Share, which is acquired by the SELECT
command. It is compatible with any modes except Access Exclusive, the
strongest one. This means that a query does not interfere with other queries,
data changes, or other operations, but prevents operations like dropping the
table while data is being read from it.
Another example: Share mode (along with other stronger modes) is
incompatible with commands that modify data. For example, the CREATE
INDEX command blocks INSERT, UPDATE, and DELETE commands (and
vice versa). Therefore, the CREATE INDEX CONCURRENTLY command
exists, it uses Share Update Exclusive mode, which is compatible with such
data changes (though the command takes longer to complete).
12
Wait Queue
relation
T 101
Access Share
transaction
acquired
a lock
The following example illustrates the consequences of acquiring an
incompatible lock.
Initially, the SELECT command is executed on the table. It requests and
acquires an Access Share lock.
13
Wait Queue
relation
T 101
T 111
Access Share
compatible
lock
Then another SELECT command requests an Access Share lock and
acquires it, since the requested lock is compatible with the existing one.
14
Wait Queue
relation
T 101
T 111
Access Share
T 121
Access Exclusive
incompatible
lock
After that the administrator runs the VACUUM FULL command, which
requires an Access Exclusive lock that is incompatible with the existing
Access Share locks. The transaction enters the wait queue.
15
Wait Queue
relation
T 101
T 111
Access Share
T 121
T 131
T 141
T 151
Access Share
Access ShareAccess Share
Access Exclusive
Several more SELECT commands then occur in the system. Hypothetically,
some of them could skip ahead while VACUUM FULL is waiting, but they all
dutifully take their place behind VACUUM FULL.
16
Wait Queue
relation
T 121T 131
T 141
T 151
Access Share
Access Share
Access Share
Access Exclusive
Once the first two transactions with SELECT commands complete and
release their locks, VACUUM FULL command starts to run.
Only when VACUUM FULL completes and releases the lock, all the queued
SELECT commands can acquire Access Share locks and start execution.
So, an improperly executed command can paralyze work of the system for
the time interval that is way longer than it takes to execute the command
itself.
Routine vacuuming (and autovacuuming) may also need an exclusive lock
at the end of its operation to truncate the table, i.e., to cut off the empty tail
of the data file and return the space to the operating system. Vacuum should
avoid long waits, so if issues arise, the truncation step can be disabled using
the vacuum_truncate storage parameter or by running vacuum with the
VACUUM (truncate off) option.
17
Other Resources
Types
Extend (adding pages to a relation file)
Object (non-relation objects such as databases, schemas, etc.)
Page (data page, used by certain index types)
Tuple
Advisory (advisory lock)
Transactionid
Virtualxid
Modes
exclusive
shared
Besides relations, there are several other types of locked resources.
Their locks are acquired either in exclusive mode only, or in both exclusive
and shared modes. These include:
●
Extend used when adding pages to a relation’s file.
●
Object to lock an object that is not a relation (such as databases,
schemas, subscriptions, etc.).
●
Page to lock a page (a rare lock, used by certain index types).
●
Tuple used in some cases to prioritize several transactions waiting for a
lock on the same row (for details, see the Row-Level Locks lesson of this
module).
●
Advisory for advisory locks (covered in detail further).
Transactionid and Virtualxid resource types are of particular interest.
Each transaction holds an exclusive lock on both its virtual and permanent
IDs, if assigned. This provides a simple way to wait for a transaction to
complete: just request a lock on its ID. This is covered in detail in the Row-
Level Locks lesson.
18
Monitoring Tools
Messages in the Server Log
log_lock_waits parameter:
outputs a message when wait exceeds deadlock_timeout
Current Locks
pg_locks view
pg_blocking_pids function
Locks that occur in the system are necessary for maintaining integrity and
isolation, but they can lead to undesirable waits. Such waits can be
monitored in order to identify their causes and eliminate them whenever
possible (for example, by changing the algorithm of the application).
Another way is to turn the log_lock_waits parameter on. In this case,
information will get into the server message log if a transaction waited longer
than deadlock_timeout (although the parameter for deadlocks is used,
ordinary waits are meant here).
The second approach is to query the pg_locks view when a long-running
lock occurs (or on a regular basis), examine blocked and blocking
transactions (using the pg_blocking_pids function) and decode them with the
help of pg_stat_activity.
20
Advisory Locks
Set by the application
Duration
until the end of the session
until the end of the transaction
Modes
exclusive
shared
Advisory locks can be used when you need locking logic that is inconvenient
to implement using regular locks. Advisory locks are set only by the
application; PostgreSQL never enforces them automatically.
Names of functions related to advisory locks start with pg_advisory and may
include additional descriptive keywords.
An advisory lock can be acquired until the end of the session
(pg_advisory_lock function) or until the end of the transaction
(pg_advisory_xact_lock).
Session-level locks are not released automatically when a transaction ends,
unlike transaction-level locks; they require explicit release using
pg_advisory_unlock function. So, session-level locks break the normal
transactional behavior: a lock acquired within a transaction will persist in the
session even if that transaction is rolled back. Similarly, if we release a lock
in the transaction and abort it later, the lock will remain in the released state.
By default, advisory locks are acquired in exclusive mode. To use shared
mode append “shared” to the function name, for example,
pg_advisory_lock_shared and pg_advisory_unlock_shared.
22
Predicate Locks
locks
in RAM
row-level
locks
predicate
locks
object-level
locks
relation-level
locks
23
Predicate Locks
Task: implement Serializable isolation level
used in addition to standard snapshot isolation
optimistic locks, a historically established term
Information in the shared memory of the server
pg_locks view
Limited number
max_pred_locks_per_transaction × max_connections
The predicate lock term dates back to time when early DBMSes made first
attempts to implement Serializable isolation using locks. The idea was to
lock not only specific rows, but also predicates. For example, when
executing a query with the condition a > 10, you need to lock the a > 10
range to prevent phantom rows and other anomalies.
In PostgreSQL, the Serializable level is built on top of the existing snapshot
mechanism, but the terminology has retained. In fact, these locks do not
block anything; they are used to track data dependencies between
transactions.
As with regular locks, information about predicate locks is shown in the
pg_locks view, where they have a single special mode called SIRead
(Serializable Isolation Read).
The number of predicate locks is limited by the product of the
max_pred_locks_per_transaction and max_connections parameters
(= 64 × 100).
Use of predicate locks imposes the requirement that all transactions should
operate at the Serializable level to achieve full isolation. Dependency
tracking only works for transactions that set predicate locks.
24
Types of Resources
Relation
Page
Tuple
Mode
SIRead
Predicate Locks
max_pred_locks_per_relation
max_pred_locks_per_page
escalation
Predicate locks are acquired at three levels.
During a full table scan, a lock is acquired at the table level.
During an index scan, locks are acquired on those index pages that match
the access condition (predicate). In addition, locks are acquired at the level
of individual tuples (which are not the same as the row-level locks discussed
in the Row-Level Locks lesson).
As the number of predicate locks grows, automatic escalation occurs:
instead of several fine‑grained locks, a single lock at a higher level is
acquired.
When the number of tuple locks for a single page exceeds the
max_pred_locks_per_page parameter value (default is 2), they are replaced
by a single page-level lock.
When the number of page- or row-level locks for a single table (index)
exceeds the max_pred_locks_per_relation parameter value, they are
replaced by a single lock on the entire relation. The default value is −2;
for negative numbers, the value is calculated as
max_pred_locks_per_transaction / abs(max_pred_locks_per_relation).
Lock escalation can lead to false serialization failures, which reduces system
throughput.
26
Takeaways
Relation-level locks and other database object-level locks
are used to coordinate concurrent access to shared resources
wait queues and deadlock detection are supported
advisory locks are used for resources not associated with stored objects
Predicate locks are used to implement Serializable isolation
do not block anything, track data dependencies between transactions
27
Practice
1. What locks does a transaction hold at the Read Committed
isolation level after reading a single table row by its primary
key? Run an experiment.
2. Demonstrate automatic escalation of predicate lock granularity
when reading table rows via an index. Show that this can lead to
a false serialization failure.
3. Configure the server to log information about lock waits that
exceed 100 milliseconds. Reproduce a scenario that triggers such
log messages.
2. In each of two transactions, read two rows from the table using an index
and modify one of the read rows. Such transactions can be serialized. Set
max_pred_locks_per_relation = 2 and repeat the experiment.
