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
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>42.37.pg_locks</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="catalogs.html" title="Chapter42.System Catalogs">
<link rel="prev" href="view-pg-indexes.html" title="42.36.pg_indexes">
<link rel="next" href="view-pg-prepared-xacts.html" title="42.38.pg_prepared_xacts">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="view-pg-locks"></a>42.37.<code class="structname">pg_locks</code></h2></div></div></div>
<a name="id831336"></a><p> The view <code class="structname">pg_locks</code> provides access to
information about the locks held by open transactions within the
database server. See <a href="mvcc.html" title="Chapter12.Concurrency Control">Chapter12, <i>Concurrency Control</i></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 transaction. Thus, the same
lockable object may
appear many times, if multiple transactions 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,
and general database objects (identified by class OID and object OID,
in the same way as in <code class="structname">pg_description</code> or
<code class="structname">pg_depend</code>). Also, the right to extend a
relation is represented as a separate lockable object.
</p>
<div class="table">
<a name="id831391"></a><p class="title"><b>Table42.37.<code class="structname">pg_locks</code> Columns</b></p>
<div class="table-contents"><table summary="pg_locks Columns" border="1">
<colgroup>
<col>
<col>
<col>
<col>
</colgroup>
<thead><tr>
<th>Name</th>
<th>Type</th>
<th>References</th>
<th>Description</th>
</tr></thead>
<tbody>
<tr>
<td><code class="structfield">locktype</code></td>
<td><code class="type">text</code></td>
<td></td>
<td> type of the lockable object:
<code class="literal">relation</code>,
<code class="literal">extend</code>,
<code class="literal">page</code>,
<code class="literal">tuple</code>,
<code class="literal">transactionid</code>,
<code class="literal">object</code>, or
<code class="literal">userlock</code>
</td>
</tr>
<tr>
<td><code class="structfield">database</code></td>
<td><code class="type">oid</code></td>
<td><code class="literal"><a href="catalog-pg-database.html" title="42.15.pg_database"><code class="structname">pg_database</code></a>.oid</code></td>
<td> OID of the database in which the object exists, or
zero if the object is a shared object, or
NULL if the object is a transaction ID
</td>
</tr>
<tr>
<td><code class="structfield">relation</code></td>
<td><code class="type">oid</code></td>
<td><code class="literal"><a href="catalog-pg-class.html" title="42.12.pg_class"><code class="structname">pg_class</code></a>.oid</code></td>
<td> OID of the relation, or NULL if the object is not
a relation or part of a relation
</td>
</tr>
<tr>
<td><code class="structfield">page</code></td>
<td><code class="type">integer</code></td>
<td></td>
<td> page number within the relation, or NULL if the object
is not a tuple or relation page
</td>
</tr>
<tr>
<td><code class="structfield">tuple</code></td>
<td><code class="type">smallint</code></td>
<td></td>
<td> tuple number within the page, or NULL if the object is not a tuple
</td>
</tr>
<tr>
<td><code class="structfield">transactionid</code></td>
<td><code class="type">xid</code></td>
<td></td>
<td> ID of a transaction, or NULL if the object is not a transaction ID
</td>
</tr>
<tr>
<td><code class="structfield">classid</code></td>
<td><code class="type">oid</code></td>
<td><code class="literal"><a href="catalog-pg-class.html" title="42.12.pg_class"><code class="structname">pg_class</code></a>.oid</code></td>
<td> OID of the system catalog containing the object, or NULL if the
object is not a general database object
</td>
</tr>
<tr>
<td><code class="structfield">objid</code></td>
<td><code class="type">oid</code></td>
<td>any OID column</td>
<td> OID of the object within its system catalog, or NULL if the
object is not a general database object
</td>
</tr>
<tr>
<td><code class="structfield">objsubid</code></td>
<td><code class="type">smallint</code></td>
<td></td>
<td> For a table column, this is the column number (the
<code class="structfield">classid</code> and <code class="structfield">objid</code> refer to the
table itself). For all other object types, this column is
zero. NULL if the object is not a general database object
</td>
</tr>
<tr>
<td><code class="structfield">transaction</code></td>
<td><code class="type">xid</code></td>
<td></td>
<td> ID of the transaction that is holding or awaiting this lock.
</td>
</tr>
<tr>
<td><code class="structfield">pid</code></td>
<td><code class="type">integer</code></td>
<td></td>
<td> Process ID of the server process holding or awaiting this
lock. Null if the lock is held by a prepared transaction.
</td>
</tr>
<tr>
<td><code class="structfield">mode</code></td>
<td><code class="type">text</code></td>
<td></td>
<td>name of the lock mode held or desired by this process (see <a href="explicit-locking.html#locking-tables" title="12.3.1.Table-Level Locks">Section12.3.1, “Table-Level Locks”</a>)</td>
</tr>
<tr>
<td><code class="structfield">granted</code></td>
<td><code class="type">boolean</code></td>
<td></td>
<td>true if lock is held, false if lock is awaited</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 transaction. False indicates that this transaction is
currently waiting to acquire this lock, which implies that some other
transaction is holding a conflicting lock mode on the same lockable object.
The waiting transaction will sleep until the other lock is released (or a
deadlock situation is detected). A single transaction can be waiting to
acquire at most one lock at a time.
</p>
<p> Every transaction holds an exclusive lock on its transaction ID for its
entire duration. If one transaction finds it necessary to wait specifically
for another transaction, it does so by attempting to acquire share lock on
the other transaction ID. 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 transaction is waiting for a
row-level lock, it will usually appear in the view as waiting for the
transaction ID of the current holder of that row lock.
</p>
<p> If user-defined locks are in use, they are displayed using the columns
for general database objects. However, the actual meaning of the lock
fields in such cases is up to the user.
</p>
<p> When the <code class="structname">pg_locks</code> view is accessed, the
internal lock manager data structures are momentarily locked, and
a copy is made for the view to display. This ensures that the
view produces a consistent set of results, while not blocking
normal lock manager operations longer than necessary. Nonetheless
there could be some impact on database performance if this view is
read often.
</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 <code class="structname">pg_class</code>.<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> If you have enabled the statistics collector, the
<code class="structfield">pid</code> column can be joined to the
<code class="structfield">procpid</code> column of the
<code class="structname">pg_stat_activity</code> view to get more
information on the session holding or waiting to hold the lock.
Also, if you are using prepared transactions, the
<code class="structfield">transaction</code> column can be joined to the
<code class="structfield">transaction</code> column of the
<code class="structname">pg_prepared_xacts</code> 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.)
</p>
</div></body>
</html>
|
