From 82119a696e247e6d3f583c6bb89435099e062e71 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Sat, 17 Aug 2002 13:04:19 +0000 Subject: [ Newest version of patch applied.] This patch is an updated version of the lock listing patch. I've made the following changes: - write documentation - wrap the SRF in a view called 'pg_locks': all user-level access should be done through this view - re-diff against latest CVS One thing I chose not to do is adapt the SRF to use the anonymous composite type code from Joe Conway. I'll probably do that eventually, but I'm not really convinced it's a significantly cleaner way to bootstrap SRF builtins than the method this patch uses (of course, it has other uses...) Neil Conway --- doc/src/sgml/monitoring.sgml | 130 ++++++++++++++++++++++++++++++++++++++++++- doc/src/sgml/mvcc.sgml | 12 +++- 2 files changed, 139 insertions(+), 3 deletions(-) (limited to 'doc/src') 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 @@ @@ -543,6 +543,134 @@ FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S; + + + Viewing Locks + + + Another useful tool for monitoring database activity is the + pg_locks 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: + + + + 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 + PostgreSQL backend. + + + + + View the relation in the current database with the most + un-granted locks (which might be a source of contention among + database clients). + + + + + Determine the effect of lock contention on overall database + performance, as well as the extent to which contention varies + with overall database traffic. + + + + + For more information on locking and managing concurrency with + PostgreSQL, refer to the + Administrator's Guide. + + + + + When the pg_locks 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. + + + + + The pg_locks 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). + + + + Lock Status System View + + + + + Column Name + Type + Description + + + + + + relation + oid + The OID of the locked relation. When querying + pg_locks, this column can be joined with the + pg_class system catalog to get more + information on the locked relation. + + + + database + oid + 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 pg_locks, this + column can be joined with the pg_database + system catalog to get more information on the locked object's + database. + + + + backendpid + int4 + The process ID of the + PostgreSQL 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 pg_stat_activity view to access + more information on the backend holding or waiting to hold the + lock. + + + + mode + text + The mode of the lock. For more information on the + different lock modes available in + PostgreSQL, refer to the + User's Guide. + + + + isgranted + text + 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. + + + +
+ @@ -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 ACCESS EXCLUSIVE lock and later acquire - ACCESS SHARE lock on the same table.) Nonconflicting + ACCESS SHARE 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, ACCESS EXCLUSIVE 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. + + To examine a list of the currently outstanding locks in a + database server, use the pg_locks system + view. For more information on monitoring the status of the lock + manager subsystem, refer to the Administrator's + Guide. + + Table-level lock modes -- cgit v1.2.3