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 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
AccessShareLockon the target table - Not guaranteed to produce a self-consistent snapshot
- Can be slow on large tables
Last updated on