1<!-- doc/src/sgml/pgrowlocks.sgml --> 2 3<sect1 id="pgrowlocks" xreflabel="pgrowlocks"> 4 <title>pgrowlocks</title> 5 6 <indexterm zone="pgrowlocks"> 7 <primary>pgrowlocks</primary> 8 </indexterm> 9 10 <para> 11 The <filename>pgrowlocks</filename> module provides a function to show row 12 locking information for a specified table. 13 </para> 14 15 <para> 16 By default use is restricted to superusers, members of the 17 <literal>pg_stat_scan_tables</literal> role, and users with 18 <literal>SELECT</literal> permissions on the table. 19 </para> 20 21 22 <sect2> 23 <title>Overview</title> 24 25 <indexterm> 26 <primary>pgrowlocks</primary> 27 </indexterm> 28 29<synopsis> 30pgrowlocks(text) returns setof record 31</synopsis> 32 33 <para> 34 The parameter is the name of a table. The result is a set of records, 35 with one row for each locked row within the table. The output columns 36 are shown in <xref linkend="pgrowlocks-columns"/>. 37 </para> 38 39 <table id="pgrowlocks-columns"> 40 <title><function>pgrowlocks</function> Output Columns</title> 41 42 <tgroup cols="3"> 43 <thead> 44 <row> 45 <entry>Name</entry> 46 <entry>Type</entry> 47 <entry>Description</entry> 48 </row> 49 </thead> 50 <tbody> 51 52 <row> 53 <entry><structfield>locked_row</structfield></entry> 54 <entry><type>tid</type></entry> 55 <entry>Tuple ID (TID) of locked row</entry> 56 </row> 57 <row> 58 <entry><structfield>locker</structfield></entry> 59 <entry><type>xid</type></entry> 60 <entry>Transaction ID of locker, or multixact ID if multitransaction</entry> 61 </row> 62 <row> 63 <entry><structfield>multi</structfield></entry> 64 <entry><type>boolean</type></entry> 65 <entry>True if locker is a multitransaction</entry> 66 </row> 67 <row> 68 <entry><structfield>xids</structfield></entry> 69 <entry><type>xid[]</type></entry> 70 <entry>Transaction IDs of lockers (more than one if multitransaction)</entry> 71 </row> 72 <row> 73 <entry><structfield>modes</structfield></entry> 74 <entry><type>text[]</type></entry> 75 <entry>Lock mode of lockers (more than one if multitransaction), 76 an array of <literal>Key Share</literal>, <literal>Share</literal>, 77 <literal>For No Key Update</literal>, <literal>No Key Update</literal>, 78 <literal>For Update</literal>, <literal>Update</literal>.</entry> 79 </row> 80 81 <row> 82 <entry><structfield>pids</structfield></entry> 83 <entry><type>integer[]</type></entry> 84 <entry>Process IDs of locking backends (more than one if multitransaction)</entry> 85 </row> 86 87 </tbody> 88 </tgroup> 89 </table> 90 91 <para> 92 <function>pgrowlocks</function> takes <literal>AccessShareLock</literal> for the 93 target table and reads each row one by one to collect the row locking 94 information. This is not very speedy for a large table. Note that: 95 </para> 96 97 <orderedlist> 98 <listitem> 99 <para> 100 If an <literal>ACCESS EXCLUSIVE</literal> lock is taken on the table, 101 <function>pgrowlocks</function> will be blocked. 102 </para> 103 </listitem> 104 <listitem> 105 <para> 106 <function>pgrowlocks</function> is not guaranteed to produce a 107 self-consistent snapshot. It is possible that a new row lock is taken, 108 or an old lock is freed, during its execution. 109 </para> 110 </listitem> 111 </orderedlist> 112 113 <para> 114 <function>pgrowlocks</function> does not show the contents of locked 115 rows. If you want to take a look at the row contents at the same time, you 116 could do something like this: 117 118<programlisting> 119SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p 120 WHERE p.locked_row = a.ctid; 121</programlisting> 122 123 Be aware however that such a query will be very inefficient. 124 </para> 125 </sect2> 126 127 <sect2> 128 <title>Sample Output</title> 129 130 <screen> 131=# SELECT * FROM pgrowlocks('t1'); 132 locked_row | locker | multi | xids | modes | pids 133------------+--------+-------+-------+----------------+-------- 134 (0,1) | 609 | f | {609} | {"For Share"} | {3161} 135 (0,2) | 609 | f | {609} | {"For Share"} | {3161} 136 (0,3) | 607 | f | {607} | {"For Update"} | {3107} 137 (0,4) | 607 | f | {607} | {"For Update"} | {3107} 138(4 rows) 139</screen> 140 </sect2> 141 142 <sect2> 143 <title>Author</title> 144 145 <para> 146 Tatsuo Ishii 147 </para> 148 </sect2> 149 150</sect1> 151