diff options
Diffstat (limited to 'doc/src/sgml/html/view-pg-locks.html')
| -rw-r--r-- | doc/src/sgml/html/view-pg-locks.html | 255 |
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 << 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 |