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.4 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 << 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.4 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>
|
