doc/src/sgml/pgrowlocks.sgml

   1 <!-- $PostgreSQL$ -->
   2
   3 <sect1 id="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  <sect2>
  16   <title>Overview</title>
  17
  18 <synopsis>
  19 pgrowlocks(text) returns setof record
  20 </synopsis>
  21
  22   <para>
  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">.
  26   </para>
  27
  28   <table id="pgrowlocks-columns">
  29    <title><function>pgrowlocks</> output columns</title>
  30
  31    <tgroup cols="3">
  32     <thead>
  33      <row>
  34       <entry>Name</entry>
  35       <entry>Type</entry>
  36       <entry>Description</entry>
  37      </row>
  38     </thead>
  39     <tbody>
  40
  41      <row>
  42       <entry><structfield>locked_row</structfield></entry>
  43       <entry><type>tid</type></entry>
  44       <entry>Tuple ID (TID) of locked row</entry>
  45      </row>
  46      <row>
  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>
  51      </row>
  52      <row>
  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>
  56      </row>
  57      <row>
  58       <entry><structfield>multi</structfield></entry>
  59       <entry><type>boolean</type></entry>
  60       <entry>True if locker is a multi-transaction</entry>
  61      </row>
  62      <row>
  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>
  66      </row>
  67      <row>
  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>
  71      </row>
  72
  73     </tbody>
  74    </tgroup>
  75   </table>
  76
  77   <para>
  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:
  81   </para>
  82
  83   <orderedlist>
  84    <listitem>
  85     <para>
  86     If the table as a whole is exclusive-locked by someone else,
  87     <function>pgrowlocks</function> will be blocked.
  88     </para>
  89    </listitem>
  90    <listitem>
  91     <para>
  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.
  95     </para>
  96    </listitem>
  97   </orderedlist>
  98
  99   <para>
 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:
 103
 104   <programlisting>
 105 SELECT * FROM accounts AS a, pgrowlocks('accounts') AS p
 106   WHERE p.locked_row = a.ctid;
 107   </programlisting>
 108
 109    Be aware however that (as of <productname>PostgreSQL</> 8.3) such a
 110    query will be very inefficient.
 111   </para>
 112  </sect2>
 113
 114  <sect2>
 115   <title>Sample output</title>
 116
 117   <programlisting>
 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}
 125 (4 rows)
 126   </programlisting>
 127  </sect2>
 128
 129  <sect2>
 130   <title>Author</title>
 131
 132   <para>
 133    Tatsuo Ishii
 134   </para>
 135  </sect2>
 136
 137 </sect1>