Postgresql 中文操作指南

F.31. pgrowlocks — show a table’s row locking information #

pgrowlocks 模块提供了一个函数，以显示指定表的行锁定信息。

The pgrowlocks module provides a function to show row locking information for a specified table.

默认情况下，仅超级用户、具有 pg_stat_scan_tables 角色特权的角色以及对表具有 SELECT 权限的用户可以使用。

By default use is restricted to superusers, roles with privileges of the pg_stat_scan_tables role, and users with SELECT permissions on the table.

F.31.1. Overview #

pgrowlocks(text) returns setof record

该参数是一个表的名称。结果是一组记录，对于表中每一行锁定，都有一个行。输出列显示在 Table F.21中。

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 in Table F.21.

Table F.21. 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; see Section 74.1

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 of Key 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 将目标表的 AccessShareLock 作为参数，并逐一读取每行以收集行锁定信息。对于一张大表而言，这不是很快的方法。请注意：

pgrowlocks takes AccessShareLock 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:

pgrowlocks 不会显示锁定行的内容。如果您想要同时查看行内容，可以像这样操作：

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.31.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.31.3. Author #

Tatsuo Ishii