From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org> |
Subject: | Help with pg_locks query |
Date: | 2011-09-05 14:30:32 |
Message-ID: | 201109051430.p85EUWU26292@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 3.6 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2011-09-05 15:07:12 | Re: [COMMITTERS] pgsql: Clean up the #include mess a little. |
Previous Message | Kevin Grittner | 2011-09-05 14:27:34 | CF 2011-09-15 Call for Reviewers |