Skip site navigation (1) Skip section navigation (2)

Help with pg_locks query

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 (view raw or flat)
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. +

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2011-09-05 15:07:12
Subject: Re: [COMMITTERS] pgsql: Clean up the #include mess a little.
Previous:From: Kevin GrittnerDate: 2011-09-05 14:27:34
Subject: CF 2011-09-15 Call for Reviewers

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