summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/view-pg-locks.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/view-pg-locks.html')
-rw-r--r--doc/src/sgml/html/view-pg-locks.html255
1 files changed, 255 insertions, 0 deletions
diff --git a/doc/src/sgml/html/view-pg-locks.html b/doc/src/sgml/html/view-pg-locks.html
new file mode 100644
index 0000000..1dfd859
--- /dev/null
+++ b/doc/src/sgml/html/view-pg-locks.html
@@ -0,0 +1,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> \ No newline at end of file