diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/monitoring.sgml | 130 | ||||
| -rw-r--r-- | doc/src/sgml/mvcc.sgml | 12 |
2 files changed, 139 insertions, 3 deletions
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 393b832ad40..1b063129fc7 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.10 2002/08/04 19:51:30 momjian Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.11 2002/08/17 13:04:14 momjian Exp $ --> <chapter id="monitoring"> @@ -543,6 +543,134 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; </sect2> </sect1> + + <sect1 id="monitoring-locks"> + <title>Viewing Locks</title> + + <para> + Another useful tool for monitoring database activity is the + <literal>pg_locks</literal> system catalog. This allows the + database administrator to view information about the outstanding + locks in the lock manager. For example, this capability can be + used to: + <itemizedlist> + <listitem> + <para> + View all the locks currently outstanding, all the locks on + relations in a particular database, all the locks on a + particular relation, or all the locks held by a particular + <productname>PostgreSQL</productname> backend. + </para> + </listitem> + <listitem> + <para> + View the relation in the current database with the most + un-granted locks (which might be a source of contention among + database clients). + </para> + </listitem> + <listitem> + <para> + Determine the effect of lock contention on overall database + performance, as well as the extent to which contention varies + with overall database traffic. + </para> + </listitem> + </itemizedlist> + + For more information on locking and managing concurrency with + <productname>PostgreSQL</productname>, refer to the + <citetitle>Administrator's Guide</citetitle>. + </para> + + <note> + <para> + When the <literal>pg_locks</literal> view is accessed, an + exclusive lock on an internal lock manager data structure must be + acquired to ensure that the data produced by the view is + consistent. The lock held on this structure conflicts with normal + database operations, and can therefore have an effect on overall + database performance. Nevertheless, the performance impact of + accessing this view should be minimal in most situations. + </para> + </note> + + <para> + The <literal>pg_locks</literal> view contains one row per + lock. This means that if there are multiple locks on a single + relation (which may or may not conflict with one another), a + single relation may show up many times. Furthermore, only + table-level locks are displayed (not row-level ones). + </para> + + <table> + <title>Lock Status System View</title> + + <tgroup cols="3"> + <thead> + <row> + <entry>Column Name</entry> + <entry>Type</entry> + <entry>Description</entry> + </row> + </thead> + + <tbody> + <row> + <entry><structfield>relation</structfield></entry> + <entry><type>oid</type></entry> + <entry>The OID of the locked relation. When querying + <literal>pg_locks</literal>, this column can be joined with the + <literal>pg_class</literal> system catalog to get more + information on the locked relation.</entry> + </row> + + <row> + <entry><structfield>database</structfield></entry> + <entry><type>oid</type></entry> + <entry>The OID of the database in which the locked relation + exists. If the lock is on a globally-shared object, this value + will be 0. When querying <literal>pg_locks</literal>, this + column can be joined with the <literal>pg_database</literal> + system catalog to get more information on the locked object's + database.</entry> + </row> + + <row> + <entry><structfield>backendpid</structfield></entry> + <entry><type>int4</type></entry> + <entry>The process ID of the + <productname>PostgreSQL</productname> backend that has + acquired or is attempting to acquire the lock. If you have + enabled the statistics collector, this column can be joined + with the <literal>pg_stat_activity</literal> view to access + more information on the backend holding or waiting to hold the + lock.</entry> + </row> + + <row> + <entry><structfield>mode</structfield></entry> + <entry><type>text</type></entry> + <entry>The mode of the lock. For more information on the + different lock modes available in + <productname>PostgreSQL</productname>, refer to the + <citetitle>User's Guide</citetitle>.</entry> + </row> + + <row> + <entry><structfield>isgranted</structfield></entry> + <entry><type>text</type></entry> + <entry>A boolean column indicating whether or not this + particular lock has been granted. If the lock has not been + granted, the backend atempting to acquire it will sleep until + the lock is released (or a deadlock situation is detected). A + single backend can be waiting to acquire at most one lock at + any given time.</entry> + </row> + </tbody> + </tgroup> + </table> + </sect1> </chapter> <!-- Keep this comment at the end of the file diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index d6c59fd665b..0a7f2f8fa94 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -1,5 +1,5 @@ <!-- -$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.26 2002/08/05 19:43:31 petere Exp $ +$Header: /cvsroot/pgsql/doc/src/sgml/mvcc.sgml,v 2.27 2002/08/17 13:04:14 momjian Exp $ --> <chapter id="mvcc"> @@ -408,7 +408,7 @@ ERROR: Can't serialize access due to concurrent update modes on the same table at the same time. (However, a transaction never conflicts with itself --- for example, it may acquire <literal>ACCESS EXCLUSIVE</literal> lock and later acquire - <literal>ACCESS SHARE</literal> lock on the same table.) Nonconflicting + <literal>ACCESS SHARE</literal> lock on the same table.) Non-conflicting lock modes may be held concurrently by many transactions. Notice in particular that some lock modes are self-conflicting (for example, <literal>ACCESS EXCLUSIVE</literal> cannot be held by more than one @@ -417,6 +417,14 @@ ERROR: Can't serialize access due to concurrent update Once acquired, a lock mode is held till end of transaction. </para> + <para> + To examine a list of the currently outstanding locks in a + database server, use the <literal>pg_locks</literal> system + view. For more information on monitoring the status of the lock + manager subsystem, refer to the <citetitle>Administrator's + Guide</citetitle>. + </para> + <variablelist> <title>Table-level lock modes</title> <varlistentry> |