This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel  |  Unsupported versions: 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3

26.3. Viewing Locks

Another useful tool for monitoring database activity is the pg_locks system table. It 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 session.

  • Determine the relation in the current database with the most ungranted 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.

Details of the pg_locks view appear in Section 44.48. For more information on locking and managing concurrency with PostgreSQL, refer to Chapter 13.

Comments


June 16, 2010, 8:46 a.m.

Useful query :
------------
It was initially written by Tomasz Drobiszewski.
I've found it in the same chapter of Postgres 8.2 documentation.

select pg_stat_activity.datname,
pg_class.relname,
pg_locks.transactionid,
pg_locks.mode,
pg_locks.granted,
pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid
from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid order by query_start;

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group