1 <!-- $PostgreSQL: pgsql/doc/src/sgml/pgrowlocks.sgml,v 1.6 2010/07/29 19:34:40 petere Exp $ -->
3 <sect1 id="pgrowlocks">
4 <title>pgrowlocks</title>
6 <indexterm zone="pgrowlocks">
7 <primary>pgrowlocks</primary>
11 The <filename>pgrowlocks</filename> module provides a function to show row
12 locking information for a specified table.
16 <title>Overview</title>
19 pgrowlocks(text) returns setof record
23 The parameter is the name of a table. The result is a set of records,
24 with one row for each locked row within the table. The output columns
25 are shown in <xref linkend="pgrowlocks-columns">.
28 <table id="pgrowlocks-columns">
29 <title><function>pgrowlocks</> output columns</title>
36 <entry>Description</entry>
42 <entry><structfield>locked_row</structfield></entry>
43 <entry><type>tid</type></entry>
44 <entry>Tuple ID (TID) of locked row</entry>
47 <entry><structfield>lock_type</structfield></entry>
48 <entry><type>text</type></entry>
49 <entry><literal>Shared</> for shared lock, or
50 <literal>Exclusive</> for exclusive lock</entry>
53 <entry><structfield>locker</structfield></entry>
54 <entry><type>xid</type></entry>
55 <entry>Transaction ID of locker, or multixact ID if multi-transaction</entry>
58 <entry><structfield>multi</structfield></entry>
59 <entry><type>boolean</type></entry>
60 <entry>True if locker is a multi-transaction</entry>
63 <entry><structfield>xids</structfield></entry>
64 <entry><type>xid[]</type></entry>
65 <entry>Transaction IDs of lockers (more than one if multi-transaction)</entry>
68 <entry><structfield>pids</structfield></entry>
69 <entry><type>integer[]</type></entry>
70 <entry>Process IDs of locking backends (more than one if multi-transaction)</entry>
78 <function>pgrowlocks</function> takes <literal>AccessShareLock</> for the
79 target table and reads each row one by one to collect the row locking
80 information. This is not very speedy for a large table. Note that:
86 If the table as a whole is exclusive-locked by someone else,
87 <function>pgrowlocks</function> will be blocked.
92 <function>pgrowlocks</function> is not guaranteed to produce a
93 self-consistent snapshot. It is possible that a new row lock is taken,
94 or an old lock is freed, during its execution.
100 <function>pgrowlocks</function> does not show the contents of locked
101 rows. If you want to take a look at the row contents at the same time, you
102 could do something like this:
105 SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
106 WHERE p.locked_row = a.ctid;
109 Be aware however that (as of <productname>PostgreSQL</> 8.3) such a
110 query will be very inefficient.
115 <title>Sample output</title>
118 test=# SELECT * FROM pgrowlocks('t1');
119 locked_row | lock_type | locker | multi | xids | pids
120 ------------+-----------+--------+-------+-----------+---------------
121 (0,1) | Shared | 19 | t | {804,805} | {29066,29068}
122 (0,2) | Shared | 19 | t | {804,805} | {29066,29068}
123 (0,3) | Exclusive | 804 | f | {804} | {29066}
124 (0,4) | Exclusive | 804 | f | {804} | {29066}
130 <title>Author</title>