Help with pg_locks query

Started by Bruce Momjianover 14 years ago1 messageshackers
Jump to latest
#1Bruce Momjian
bruce@momjian.us

I am writing a talk about the lock manager for PG Open and I would like
suggestions on how to improve a query in my talk. The query creates a
lockinfo_hierarchy view of a recursive query on other views. The output
shows the locks held and the locks being waited for:

\! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g'
SELECT * FROM lockinfo_hierarchy;
?column? | pid | vxid | granted | xid_lock | lock_type | relname | page | tuple
----------+-------+--------+---------+----------+---------------+----------+------+-------
1 | 24860 | 2/3106 | t | 828 | transactionid | | |
1 | 24864 | 3/42 | t | 829 | transactionid | | |
1 | 24868 | 4/78 | t | 830 | transactionid | | |
1 | 24872 | 5/22 | t | 831 | transactionid | | |
2 | 24864 | 3/42 | f | 828 | transactionid | | |
3 | 24864 | 3/42 | t | | tuple | lockdemo | 0 | 1
4 | 24868 | 4/78 | f | | tuple | lockdemo | 0 | 1
4 | 24872 | 5/22 | f | | tuple | lockdemo | 0 | 1
(8 rows)

The SQL needed to reproduce this output is attached, and must be run
in your personal database, e.g. postgres.

What this output shows are four transactions holding locks on their own
xids, transaction 3/42 waiting for 828 to complete, and 3/42 holding a
row lock that 4/78 and 5/22 are waiting on.

When there are multiple waiters, one transaction waits on the real xid
and the others sleep waiting to be woken up later.

Is there any better way to show this? (The first column is just there
for debugging so you can see what part of the query generated the row.)

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Attachments:

/rtmp/pg_locks.sqltext/plainDownload