summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/view-pg-locks.html
blob: 1dfd859260c0a60d3292c8518803b8beca0f4b9c (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>54.12. pg_locks</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="view-pg-indexes.html" title="54.11. pg_indexes" /><link rel="next" href="view-pg-matviews.html" title="54.13. pg_matviews" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">54.12. <code class="structname">pg_locks</code></th></tr><tr><td width="10%" align="left"><a accesskey="p" href="view-pg-indexes.html" title="54.11. pg_indexes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="views.html" title="Chapter 54. System Views">Up</a></td><th width="60%" align="center">Chapter 54. System Views</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="view-pg-matviews.html" title="54.13. pg_matviews">Next</a></td></tr></table><hr /></div><div class="sect1" id="VIEW-PG-LOCKS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">54.12. <code class="structname">pg_locks</code></h2></div></div></div><a id="id-1.10.5.16.2" class="indexterm"></a><p>
   The view <code class="structname">pg_locks</code> provides access to
   information about the locks held by active processes within the
   database server.  See <a class="xref" href="mvcc.html" title="Chapter 13. Concurrency Control">Chapter 13</a> for more discussion
   of locking.
  </p><p>
   <code class="structname">pg_locks</code> contains one row per active lockable
   object, requested lock mode, and relevant process.  Thus, the same
   lockable object might
   appear many times, if multiple processes are holding or waiting
   for locks on it.  However, an object that currently has no locks on it
   will not appear at all.
  </p><p>
   There are several distinct types of lockable objects:
   whole relations (e.g., tables), individual pages of relations,
   individual tuples of relations,
   transaction IDs (both virtual and permanent IDs),
   and general database objects (identified by class OID and object OID,
   in the same way as in <a class="link" href="catalog-pg-description.html" title="53.19. pg_description"><code class="structname">pg_description</code></a> or
   <a class="link" href="catalog-pg-depend.html" title="53.18. pg_depend"><code class="structname">pg_depend</code></a>).  Also, the right to extend a
   relation is represented as a separate lockable object, as is the right to
   update <code class="structname">pg_database</code>.<code class="structfield">datfrozenxid</code>.
   Also, <span class="quote"><span class="quote">advisory</span></span> locks can be taken on numbers that have
   user-defined meanings.
  </p><div class="table" id="id-1.10.5.16.6"><p class="title"><strong>Table 54.12. <code class="structname">pg_locks</code> Columns</strong></p><div class="table-contents"><table class="table" summary="pg_locks Columns" border="1"><colgroup><col /></colgroup><thead><tr><th class="catalog_table_entry"><p class="column_definition">
       Column Type
      </p>
      <p>
       Description
      </p></th></tr></thead><tbody><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">locktype</code> <code class="type">text</code>
      </p>
      <p>
       Type of the lockable object:
       <code class="literal">relation</code>,
       <code class="literal">extend</code>,
       <code class="literal">frozenid</code>,
       <code class="literal">page</code>,
       <code class="literal">tuple</code>,
       <code class="literal">transactionid</code>,
       <code class="literal">virtualxid</code>,
       <code class="literal">spectoken</code>,
       <code class="literal">object</code>,
       <code class="literal">userlock</code>, or
       <code class="literal">advisory</code>.
       (See also <a class="xref" href="monitoring-stats.html#WAIT-EVENT-LOCK-TABLE" title="Table 28.11. Wait Events of Type Lock">Table 28.11</a>.)
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">database</code> <code class="type">oid</code>
       (references <a class="link" href="catalog-pg-database.html" title="53.15. pg_database"><code class="structname">pg_database</code></a>.<code class="structfield">oid</code>)
      </p>
      <p>
       OID of the database in which the lock target exists, or
       zero if the target is a shared object, or
       null if the target is a transaction ID
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">relation</code> <code class="type">oid</code>
       (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code>)
      </p>
      <p>
       OID of the relation targeted by the lock, or null if the target is not
       a relation or part of a relation
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">page</code> <code class="type">int4</code>
      </p>
      <p>
       Page number targeted by the lock within the relation,
       or null if the target is not a relation page or tuple
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">tuple</code> <code class="type">int2</code>
      </p>
      <p>
       Tuple number targeted by the lock within the page,
       or null if the target is not a tuple
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">virtualxid</code> <code class="type">text</code>
      </p>
      <p>
       Virtual ID of the transaction targeted by the lock,
       or null if the target is not a virtual transaction ID
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">transactionid</code> <code class="type">xid</code>
      </p>
      <p>
       ID of the transaction targeted by the lock,
       or null if the target is not a transaction ID
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">classid</code> <code class="type">oid</code>
       (references <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code>)
      </p>
      <p>
       OID of the system catalog containing the lock target, or null if the
       target is not a general database object
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">objid</code> <code class="type">oid</code>
       (references any OID column)
      </p>
      <p>
       OID of the lock target within its system catalog, or null if the
       target is not a general database object
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">objsubid</code> <code class="type">int2</code>
      </p>
      <p>
       Column number targeted by the lock (the
       <code class="structfield">classid</code> and <code class="structfield">objid</code> refer to the
       table itself),
       or zero if the target is some other general database object,
       or null if the target is not a general database object
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">virtualtransaction</code> <code class="type">text</code>
      </p>
      <p>
       Virtual ID of the transaction that is holding or awaiting this lock
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">pid</code> <code class="type">int4</code>
      </p>
      <p>
       Process ID of the server process holding or awaiting this
       lock, or null if the lock is held by a prepared transaction
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">mode</code> <code class="type">text</code>
      </p>
      <p>
       Name of the lock mode held or desired by this process (see <a class="xref" href="explicit-locking.html#LOCKING-TABLES" title="13.3.1. Table-Level Locks">Section 13.3.1</a> and <a class="xref" href="transaction-iso.html#XACT-SERIALIZABLE" title="13.2.3. Serializable Isolation Level">Section 13.2.3</a>)
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">granted</code> <code class="type">bool</code>
      </p>
      <p>
       True if lock is held, false if lock is awaited
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">fastpath</code> <code class="type">bool</code>
      </p>
      <p>
       True if lock was taken via fast path, false if taken via main
       lock table
      </p></td></tr><tr><td class="catalog_table_entry"><p class="column_definition">
       <code class="structfield">waitstart</code> <code class="type">timestamptz</code>
      </p>
      <p>
       Time when the server process started waiting for this lock,
       or null if the lock is held.
       Note that this can be null for a very short period of time after
       the wait started even though <code class="structfield">granted</code>
       is <code class="literal">false</code>.
      </p></td></tr></tbody></table></div></div><br class="table-break" /><p>
   <code class="structfield">granted</code> is true in a row representing a lock
   held by the indicated process.  False indicates that this process is
   currently waiting to acquire this lock, which implies that at least one
   other process is holding or waiting for a conflicting lock mode on the same
   lockable object.  The waiting process will sleep until the other lock is
   released (or a deadlock situation is detected).  A single process can be
   waiting to acquire at most one lock at a time.
  </p><p>
   Throughout running a transaction, a server process holds an exclusive lock
   on the transaction's virtual transaction ID.  If a permanent ID is assigned
   to the transaction (which normally happens only if the transaction changes
   the state of the database), it also holds an exclusive lock on the
   transaction's permanent transaction ID until it ends.  When a process finds
   it necessary to wait specifically for another transaction to end, it does
   so by attempting to acquire share lock on the other transaction's ID
   (either virtual or permanent ID depending on the situation). That will
   succeed only when the other transaction terminates and releases its locks.
  </p><p>
   Although tuples are a lockable type of object,
   information about row-level locks is stored on disk, not in memory,
   and therefore row-level locks normally do not appear in this view.
   If a process is waiting for a
   row-level lock, it will usually appear in the view as waiting for the
   permanent transaction ID of the current holder of that row lock.
  </p><p>
   Advisory locks can be acquired on keys consisting of either a single
   <code class="type">bigint</code> value or two integer values.
   A <code class="type">bigint</code> key is displayed with its
   high-order half in the <code class="structfield">classid</code> column, its low-order half
   in the <code class="structfield">objid</code> column, and <code class="structfield">objsubid</code> equal
   to 1. The original <code class="type">bigint</code> value can be reassembled with the
   expression <code class="literal">(classid::bigint &lt;&lt; 32) |
   objid::bigint</code>. Integer keys are displayed with the
   first key in the
   <code class="structfield">classid</code> column, the second key in the <code class="structfield">objid</code>
   column, and <code class="structfield">objsubid</code> equal to 2.  The actual meaning of
   the keys is up to the user.  Advisory locks are local to each database,
   so the <code class="structfield">database</code> column is meaningful for an advisory lock.
  </p><p>
   <code class="structname">pg_locks</code> provides a global view of all locks
   in the database cluster, not only those relevant to the current database.
   Although its <code class="structfield">relation</code> column can be joined
   against <a class="link" href="catalog-pg-class.html" title="53.11. pg_class"><code class="structname">pg_class</code></a>.<code class="structfield">oid</code> to identify locked
   relations, this will only work correctly for relations in the current
   database (those for which the <code class="structfield">database</code> column
   is either the current database's OID or zero).
  </p><p>
   The <code class="structfield">pid</code> column can be joined to the
   <code class="structfield">pid</code> column of the
   <a class="link" href="monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW" title="28.2.3. pg_stat_activity">
   <code class="structname">pg_stat_activity</code></a>
   view to get more
   information on the session holding or awaiting each lock,
   for example
</p><pre class="programlisting">
SELECT * FROM pg_locks pl LEFT JOIN pg_stat_activity psa
    ON pl.pid = psa.pid;
</pre><p>
   Also, if you are using prepared transactions, the
   <code class="structfield">virtualtransaction</code> column can be joined to the
   <code class="structfield">transaction</code> column of the <a class="link" href="view-pg-prepared-xacts.html" title="54.16. pg_prepared_xacts"><code class="structname">pg_prepared_xacts</code></a>
   view to get more information on prepared transactions that hold locks.
   (A prepared transaction can never be waiting for a lock,
   but it continues to hold the locks it acquired while running.)
   For example:
</p><pre class="programlisting">
SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
    ON pl.virtualtransaction = '-1/' || ppx.transaction;
</pre><p>
  </p><p>
   While it is possible to obtain information about which processes block
   which other processes by joining <code class="structname">pg_locks</code> against
   itself, this is very difficult to get right in detail.  Such a query would
   have to encode knowledge about which lock modes conflict with which
   others.  Worse, the <code class="structname">pg_locks</code> view does not expose
   information about which processes are ahead of which others in lock wait
   queues, nor information about which processes are parallel workers running
   on behalf of which other client sessions.  It is better to use
   the <code class="function">pg_blocking_pids()</code> function
   (see <a class="xref" href="functions-info.html#FUNCTIONS-INFO-SESSION-TABLE" title="Table 9.66. Session Information Functions">Table 9.66</a>) to identify which
   process(es) a waiting process is blocked behind.
  </p><p>
   The <code class="structname">pg_locks</code> view displays data from both the
   regular lock manager and the predicate lock manager, which are
   separate systems; in addition, the regular lock manager subdivides its
   locks into regular and <em class="firstterm">fast-path</em> locks.
   This data is not guaranteed to be entirely consistent.
   When the view is queried,
   data on fast-path locks (with <code class="structfield">fastpath</code> = <code class="literal">true</code>)
   is gathered from each backend one at a time, without freezing the state of
   the entire lock manager, so it is possible for locks to be taken or
   released while information is gathered.  Note, however, that these locks are
   known not to conflict with any other lock currently in place.  After
   all backends have been queried for fast-path locks, the remainder of the
   regular lock manager is locked as a unit, and a consistent snapshot of all
   remaining locks is collected as an atomic action.  After unlocking the
   regular lock manager, the predicate lock manager is similarly locked and all
   predicate locks are collected as an atomic action.  Thus, with the exception
   of fast-path locks, each lock manager will deliver a consistent set of
   results, but as we do not lock both lock managers simultaneously, it is
   possible for locks to be taken or released after we interrogate the regular
   lock manager and before we interrogate the predicate lock manager.
  </p><p>
   Locking the regular and/or predicate lock manager could have some
   impact on database performance if this view is very frequently accessed.
   The locks are held only for the minimum amount of time necessary to
   obtain data from the lock managers, but this does not completely eliminate
   the possibility of a performance impact.
  </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="view-pg-indexes.html" title="54.11. pg_indexes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="views.html" title="Chapter 54. System Views">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="view-pg-matviews.html" title="54.13. pg_matviews">Next</a></td></tr><tr><td width="40%" align="left" valign="top">54.11. <code class="structname">pg_indexes</code> </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 54.13. <code class="structname">pg_matviews</code></td></tr></table></div></body></html>