Extensions
pgrowlocks

pgrowlocks

pgrowlocks : show row-level locking information

Overview

ID Extension Package Version Category License Language
6910
pgrowlocks
pgrowlocks
 1.2
STAT
PostgreSQL
C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--s-d--
No
Yes
No
Yes
no
no
Relationships
See Also
pg_profile
pg_tracing
pg_show_plans
pg_stat_kcache
pg_stat_monitor
pg_qualstats
pg_store_plans
pg_track_settings

Packages

PG18 PG17 PG16 PG15 PG14
1.2
1.2
1.2
1.2
1.2

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Create this extension with:

CREATE EXTENSION pgrowlocks;

Usage

pgrowlocks: display row-level locking information

pgrowlocks shows which rows in a table are currently locked, by which transactions, and the lock modes.

Function 

SELECT * FROM pgrowlocks('my_table');

 locked_row | locker | multi | xids  |     modes      |  pids
------------+--------+-------+-------+----------------+--------
 (0,1)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,2)      |    609 | f     | {609} | {"For Share"}  | {3161}
 (0,3)      |    607 | f     | {607} | {"For Update"} | {3107}

Return Columns

Column Type Description
locked_row tid Tuple ID of the locked row
locker xid Transaction ID (or multixact ID)
multi boolean True if locker is a multitransaction
xids xid[] Transaction IDs of all lockers
modes text[] Lock modes: For Key Share, For Share, For No Key Update, For Update, etc.
pids integer[] Process IDs of locking backends

View Locked Row Contents 

SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
WHERE p.locked_row = a.ctid;

Access

Restricted to superusers, roles with pg_stat_scan_tables, and users with SELECT on the target table.

Caveats

  • Takes AccessShareLock on the target table
  • Not guaranteed to produce a self-consistent snapshot
  • Can be slow on large tables
Last updated on
pageinspectsslinfo