pgrowlocks

show row-level locking information
Repository
https://www.postgresql.org/docs/current/pgrowlocks.html
https://www.postgresql.org/docs/current/pgrowlocks.html

Overview

PackageVersionCategoryLicenseLanguage
pgrowlocks1.2STATPostgreSQLC
IDExtensionBinLibLoadCreateTrustRelocSchema
6910pgrowlocksNoYesNoYesNoNo-
Relatedpg_profile pg_tracing pg_show_plans pg_stat_kcache pg_stat_monitor pg_qualstats pg_store_plans pg_track_settings

Version

PG18PG17PG16PG15PG14
1.21.21.21.21.2

Install

Note: This is a built-in contrib extension of PostgreSQL

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

ColumnTypeDescription
locked_rowtidTuple ID of the locked row
lockerxidTransaction ID (or multixact ID)
multibooleanTrue if locker is a multitransaction
xidsxid[]Transaction IDs of all lockers
modestext[]Lock modes: For Key Share, For Share, For No Key Update, For Update, etc.
pidsinteger[]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 Modified 2026-03-12: add pg extension catalog (95749bf)