Locks
Locks in RAM
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
Locks in RAM
Wait Event Monitoring
3
Locks in RAM
object-level
locks
relation-level
locks
locks
in RAM
row-level
locks
predicate
locks
4
Spinlocks
Acquired for short periods
a few CPU instructions
Exclusive mode
Monitoring is available
pg_stat_activity
No deadlock detection
Spin loop
atomic CPU instructions are used
We have already covered long-term locks which typically remain active until
the end of a transaction and support multiple modes. Simpler locks with
lower overhead are used to protect data structures in shared memory
accessed by multiple processes.
The simplest of them are spinlocks. They are meant to be acquired for very
short time intervals (a few CPU instructions), they protect individual data
fields from simultaneous changes.
Spinlocks are implemented using atomic CPU instructions, such as
compare-and-swap (https://en.wikipedia.org/wiki/Compare-and-swap).
They can be acquired only in exclusive mode. If the resource is locked, the
process enters an active waiting loop, repeatedly attempting to acquire the
lock until it succeeds. This approach makes sense since spinlocks are used
only in cases where the probability of a conflict is estimated as very low.
There is also monitoring available for tracking wait events related to
spinlocks.
However, spinlocks do not provide deadlock detection; preventing such
scenarios is the responsibility of PostgreSQL developers.
5
Lightweight Locks
Acquired for short periods
typically fractions of a second
Exclusive and shared modes
Monitoring is available
pg_stat_activity
No deadlock detection
“Unfair” queue
shared locks allow read processes to bypass the queue
The next category is lightweight locks (lwlocks).
They are acquired for a short period to work with a data structure (such as a
hash table or a list of pointers). Lightweight locks are typically held for a
short time, but during that time IO operations may occur, so the duration can
sometimes become significant.
Two locking modes are supported: exclusive (for writing) and shared (for
reading).
A wait queue is supported. However, while the lock is held in shared mode,
other reading processes can bypass the queue. In highly concurrent
systems under heavy load, this can lead to unpredictably long waits for
processes that need to modify data
Deadlock detection is not provided, just as with spinlocks. Monitoring is
available for lightweight locks.
6
Buffer Pin
Acquired for the duration of buffer operations
potentially for an extended period
Shared mode
Monitoring is available
pg_stat_activity
pg_buffercache
No deadlock detection
Passive waiting
a pinned buffer is typically skipped
Another type of lock, which we already covered in the Buffer Cache lesson
of the WAL module is the buffer pin.
A list of processes working with the buffer is maintained for each buffer.
Certain operations are prohibited on a pinned buffer. For example, we can
insert a new row into a page (which other processes will not see due to
MVCC), but we cannot replace a page with another one.
Typically, processes skip a pinned buffer and choose another one to avoid
waiting. However, in some cases where a specific buffer is required, a
lightweight lock in exclusive mode is requested. If necessary, the process
waits until all other processes finish working with the buffer. This waiting is
passive: the system wakes up the waiting process when the buffer is
unpinned.
Deadlocks are impossible here, preventing such scenarios is the
responsibility of PostgreSQL developers.
Wait events related to buffer pins can be monitored using the
pg_stat_activity view. The current number of buffer pins is shown by the
pg_buffercache extension.
7
Example: Buffer Cache
free
buffers
hash table
1
3 1 1
next
victim
1 0
buffer strategy lock
BufferMapping lock
x 128
buffer header lock
BufferPin
IO in progress
BufferContent lock
increment of counters
is implemented via atomic
CPU instructions
To get a basic (incomplete) understanding of how and where locks are used,
let’s look at the buffer cache as an example.
To access the hash table containing references to buffers, the process
should acquire a lightweight BufferMapping lock in shared mode; or in
exclusive mode if the table needs to be modified. To increase granularity,
this lock is implemented as a tranche of 128 separate locks, each protecting
its own portion of the hash table.
A process accesses a buffer header using a spinlock. Some operations
(such as incrementing a counter) can be performed without explicit locks,
using atomic CPU instructions.
To read the buffer’s contents, a BufferContent lock is required. Typically, it is
acquired to read only item pointers, and after that, a buffer pin provides
sufficient protection. To change the buffer contents, this lock is acquired in
exclusive mode.
When a buffer is being read from disk (or written to disk), the IO in progress
flag is set in its header. This signals to other processes that the page is
being read, so they can queue up if they need the same page.
Pointers to free buffers and to the next victim are protected by a single
spinlock called the buffer strategy lock.
8
Example: WAL Buffers
insert
position
hash table
insert position lock
WALBufMapping lock
WALWrite lock
WALInsert lock
x 8
after allocating space
in the buffer, the insertion
proceeds in parallel
WAL buffers provide another example.
The WAL cache also uses a hash table that contains mapping of pages to
buffers. Unlike the buffer cache’s hash table, this hash table is protected by
a single lightweight WALBufMapping lock, since the WAL cache is smaller
(usually 1/32 of the buffer cache) and access to the buffers is more
sequential.
Writing pages to disk is protected by the lightweight WALWrite lock, so that
only one process can perform this operation at a time.
To create a WAL record, the process must first allocate space in a page. To
do this, it acquires an insert position spinlock. When the space is allocated,
the process copies the contents of the record into it. Copying can be
performed by several processes simultaneously, therefore, recording is
protected by a tranche of eight lightweight WALInsert locks.
The figure does not show all WAL-related locks, but this and previous
examples should give a basic idea of how locks in RAM are used.
9
Wait Event Monitoring
Wait events in pg_stat_activity and sampling
Wait event types
10
Wait Event Sampling
When a process is waiting for something,
this information appears in the pg_stat_activity view
wait_event_type — type of wait event
wait_event — name of the specific wait event
The information may be incomplete
not all code locations where wait events can occur are covered
The information reflects only the current moment
the only way to observe behavior over time is through sampling
a reliable picture emerges only with a large number of measurements
The pg_stat_activity view is used for monitoring wait events. When a
process (system or backend) cannot proceed with its work and is waiting for
something, that wait event appears in the view. The wait_event_type column
shows the type of wait event, while the wait_event column shows the name
of the specific wait event.
Note that the view shows only waits that are properly handled in the source
code. If the view does not show a wait event, this does not guarantee with
100% certainty that the process is not waiting for something.
Unfortunately, the only available information about wait events is the current
information. No cumulative statistics are maintained. The only way to get a
picture of wait events is sampling the state of the view at regular intervals.
While there is no built‑in tool for this, extensions like pg_wait_sampling can
be used.
Remember that sampling has a probabilistic nature: to get a reasonably
reliable picture, the number of samples must be sufficiently high. Therefore,
low‑frequency sampling does not provide accurate data, while increasing the
frequency leads to higher overhead. For the same reason, sampling is
useless for analyzing short-lived sessions.
11
Wait Event Types
Locks
object lock Lock
lightweight lock LWLock
buffer pin BufferPin
Other wait events
input/output IO
waiting for data from another process IPC
waiting for data from the client Client
waiting in an extension module Extension
idle waiting Activity, Timeout
Any remaining time is considered unaccounted
pg_stat_activity.wait_event_type
Wait events can be categorized into several types. The locks we have
examined represent a major category of wait events: object lock waits (Lock
in the wait_event_type column), lightweight locks (LWLock), and buffer pins
(BufferPin).
Processes can also wait for other types of events. IO wait events occur
when a process needs to read or write data. A process might wait for the
data it needs to operate, either from a client (Client) or another process
(IPC).
Extensions can register their specific wait events.
There are also situations where a process is simply not doing any productive
work. This category includes waits for background processes in their main
loop (Activity) and timer waits (Timeout). These types of wait events are
typically normal and do not indicate any problems.
Each type of wait event has a specific wait name:
If the name of a wait event is not defined, the process is not in the waiting
state. This time should be considered as unaccounted for, since we are
actually unaware of what exactly is happening at that moment.
In the following demonstration, we will use the FUSE filesystem
(https://github.com/libfuse/libfuse) and the slowfs project based on it
(https://github.com/nirs/slowfs) to slow down IO operations.
13
Takeaways
Locks in RAM are implemented using different mechanisms
spinlocks, lightweight locks, buffer pins
short duration and lightweight infrastructure
Monitoring of current wait events using
the pg_stat_activity view
sampling is required to observe behavior over time
14
Practice
1. An open cursor holds a buffer pin so that reading the next row
can be performed more quickly. Check this using the
pg_buffercache extension.
2. Open a cursor on a table and, without closing the cursor, run
VACUUM on that table. Will the vacuum wait for the buffer pin
to be released?
3. Repeat the experiment, this time running VACUUM FREEZE.
Check that the buffer pin wait appears in the backend’s wait
profile.
1. The pg_buffercache extension was covered in the WAL module, in the
Buffer Cache lesson.
