F.28. pgrowlocks
Thepgrowlocks module provides a function to show row locking information for a specified table.
By default use is restricted to superusers, members of thepg_stat_scan_tables role, and users withSELECT permissions on the table.
F.28.1. Overview
pgrowlocks(text) returns setof record
The parameter is the name of a table. The result is a set of records, with one row for each locked row within the table. The output columns are shown inTable F.20.
Table F.20. pgrowlocks Output Columns
| Name | Type | Description |
|---|---|---|
locked_row | tid | Tuple ID (TID) of locked row |
locker | xid | Transaction ID of locker, or multixact ID if multitransaction |
multi | boolean | True if locker is a multitransaction |
xids | xid[] | Transaction IDs of lockers (more than one if multitransaction) |
modes | text[] | Lock mode of lockers (more than one if multitransaction), an array ofKey Share,Share,For No Key Update,No Key Update,For Update,Update. |
pids | integer[] | Process IDs of locking backends (more than one if multitransaction) |
pgrowlocks takesAccessShareLock for the target table and reads each row one by one to collect the row locking information. This is not very speedy for a large table. Note that:
If an
ACCESS EXCLUSIVElock is taken on the table,pgrowlockswill be blocked.pgrowlocksis not guaranteed to produce a self-consistent snapshot. It is possible that a new row lock is taken, or an old lock is freed, during its execution.
pgrowlocks does not show the contents of locked rows. If you want to take a look at the row contents at the same time, you could do something like this:
SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p WHERE p.locked_row = a.ctid;Be aware however that such a query will be very inefficient.
F.28.2. Sample Output
=# SELECT * FROM pgrowlocks('t1'); 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} (0,4) | 607 | f | {607} | {"For Update"} | {3107}(4 rows)F.28.3. Author
Tatsuo Ishii