PostgreSQL 19 — Monitoring Lock Contention and Fast-Path Overflow with pg_stat_lock

By -

 PostgreSQL 19 introduces the pg_stat_lock view, which provides cumulative statistics about lock activity across the database cluster. It helps database administrators investigate lock contention, measure time spent waiting for locks, and identify situations where relation locks exceed the available fast-path slots.

In this article, I will demonstrate these capabilities through two practical scenarios.

The view contains the following columns:

locktype: Type of lockable object, such as a relation or transaction ID
waits: Number of qualifying lock waits
wait_time: Total time spent waiting for locks, in milliseconds
fastpath_exceeded: Number of times a lock could not be acquired through the fast path because the available slots were exceeded
stats_reset: Time when the statistics were last reset

Initially, all counters are zero in my test environment. The view returns 12 rows, one for each lock type.

An important detail is that waits and wait_time only include waits that exceed deadlock_timeout and end with successful lock acquisition. In my environment, the configured threshold is one second:

Investigating Lock Waits

In this scenario, I will demonstrate how pg_stat_lock records lock waits caused by concurrent transactions.

Concurrent Updates
I will use an existing table named tbl_counter. The table contains a counter row that both sessions attempt to update.

Before starting the experiment, I reset the lock statistics:

Session 1
Start a transaction and update the counter:

The update completes, but the transaction remains open. PostgreSQL retains the relevant locks until the transaction ends.

Session 2
While Session 1 remains open, execute the same update:

The second update waits because Session 1 has modified the same row and has not yet committed.

At this point, querying pg_stat_lock shows that the counters have not increased for this wait:

The waits and wait_time values remain zero in the displayed statistics.

Why?

The second transaction is still waiting. PostgreSQL records this qualifying wait in the cumulative statistics when the lock is successfully acquired after the wait ends, rather than incrementing the counters continuously while the transaction is blocked.

Session 1
Commit the first transaction:

Session 2 can now proceed and complete its update.
Query the statistics again:

The recorded wait_time is approximately 332 seconds.

Table-Level Lock Contention
Now let’s generate a wait involving a relation lock.

First, reset the statistics again:

Session 1
Acquire an exclusive table lock and keep the transaction open:

The ACCESS EXCLUSIVE lock conflicts with the ACCESS SHARE lock required by a normal SELECT.


Session 2
While Session 1 holds the lock, execute:

The query waits until Session 1 releases its lock.

Session 1
Commit the transaction:

After Session 2 completes, query the statistics:

The relation row records one qualifying wait, with approximately 63.3 seconds of accumulated wait time.

This example differs from the concurrent-update scenario. The ACCESS EXCLUSIVE lock directly prevents another session from acquiring the relation lock required by its SELECT statement. Consequently, the recorded wait appears under relation.

The wait duration depends on the actual time the second session spends waiting. PostgreSQL only includes waits exceeding deadlock_timeout that subsequently end in successful acquisition.

Detecting Fast-Path Lock Overflow

PostgreSQL uses a fast-path mechanism to acquire certain relation locks efficiently. When the available fast-path slots are insufficient for a backend’s relation-lock requirements, additional lock acquisitions can use the regular shared lock table instead.

The fastpath_exceeded counter helps identify this activity. Unlike waits and wait_time, it does not depend on a lock wait exceeding deadlock_timeout.

This is particularly interesting for partition-heavy workloads, where a single query can access thousands of relations.
First, check the current value of max_locks_per_transaction:

To generate a workload that accesses many relations, I created a range-partitioned table with 5,000 partitions.

Next, I created the partitions using a DO block:

The operation completed successfully and took approximately 68.6 seconds in my environment.

The table is empty, but its partition structure provides a useful test case because a query against the parent can require locks on many relations.

Before executing the test query, I reset the lock statistics:

At this stage, the fastpath_exceeded counters were zero.

Resetting the statistics makes it easier to attribute subsequent counter changes to the experiment. This function resets cluster-wide lock statistics, so it should be used carefully on shared or production servers.
Now, execute a simple aggregate query:

Although the table contains no rows, PostgreSQL still needs to process the partitioned relation and its partitions. The query therefore provides a way to investigate relation-lock activity without inserting thousands of rows.

Next, query pg_stat_lock again:

The fastpath_exceeded counter increased to 92,796.

This result shows that the workload generated many relation-lock acquisitions that could not use the fast path because its available slots had been exceeded.

Conclusion

The pg_stat_lock view in PostgreSQL 19 provides a useful way to investigate lock activity through cumulative statistics.

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — error_message_details Parameter for Displaying Error Details

By -
  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...
Read more

Buffer Busy Wait and Read by Other Session in Oracle

By -
Image
Assume a block is accessed and pinned in the buffer cache by Session 1 (each buffer can be pinned by only one session at a time). While this block is pinned, if another session attempts to access the same block, it must wait. This type of wait is called  Buffer Busy Wait . What distinguishes Buffer Busy Wait from some other wait events is that the contention occurs at the  block level . Even if the two sessions intend to access different rows within the same block, they are still competing at the block level. As you know, Oracle blocks can contain different types of data. For example, one block may hold user data, while another may store control information. Therefore, contention for blocks can be divided into several categories: Data Block Data Segment Header Undo Header Undo Block Let’s briefly review the role of each block type in this wait event. 1) Data Block This refers to the blocks that store object data. Buffer Busy Waits at this level can have several causes: Poorly ...
Read more