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 <br...@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
-- cannot be a temporary view because other sessions must see it
DROP VIEW IF EXISTS lockview CASCADE;
CREATE VIEW lockview AS
SELECT pid, virtualtransaction AS vxid, locktype AS lock_type,
mode AS lock_mode, granted,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname,
page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
-- no need to show self-vxid locks
virtualtransaction IS DISTINCT FROM virtualxid
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;
DROP VIEW IF EXISTS lockview1 CASCADE;
CREATE VIEW lockview1 AS
SELECT pid, vxid, lock_type, lock_mode, granted, xid_lock, relname
FROM lockview
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;
DROP VIEW IF EXISTS lockview2 CASCADE;
CREATE VIEW lockview2 AS
SELECT pid, vxid, lock_type, page, tuple, classid, objid, objsubid
FROM lockview
-- granted is first
-- add non-display columns to match ordering of lockview
ORDER BY 1, 2, granted DESC, vxid, xid_lock::text, 3, 4, 5, 6, 7, 8;
DROP TABLE IF EXISTS lockdemo;
CREATE TABLE lockdemo (col int);
INSERT INTO lockdemo VALUES (1);
-- do two UPDATEs to cause a wait
CREATE VIEW lockinfo_hierarchy AS
WITH RECURSIVE lockinfo1 AS (
SELECT '1', pid, vxid, granted, xid_lock, lock_type, relname,
page, tuple
FROM lockview
WHERE xid_lock IS NOT NULL AND
relname IS NULL AND
granted
UNION ALL
SELECT '2', lockview.pid, lockview.vxid, lockview.granted,
lockview.xid_lock,
lockview.lock_type, lockview.relname, lockview.page,
lockview.tuple
FROM lockinfo1 JOIN lockview ON (lockinfo1.xid_lock =
lockview.xid_lock)
WHERE lockview.xid_lock IS NOT NULL AND
lockview.relname IS NULL AND
NOT lockview.granted AND
lockinfo1.granted),
lockinfo2 AS (
SELECT '3', pid, vxid, granted, xid_lock, lock_type, relname,
page, tuple
FROM lockview
WHERE lock_type = 'tuple' AND
granted
UNION ALL
SELECT '4', lockview.pid, lockview.vxid, lockview.granted,
lockview.xid_lock,
lockview.lock_type, lockview.relname, lockview.page,
lockview.tuple
FROM lockinfo2 JOIN lockview ON (
lockinfo2.lock_type = lockview.lock_type AND
lockinfo2.relname = lockview.relname AND
lockinfo2.page = lockview.page AND
lockinfo2.tuple = lockview.tuple)
WHERE lockview.lock_type = 'tuple' AND
NOT lockview.granted AND
lockinfo2.granted
)
SELECT * FROM lockinfo1
UNION ALL
SELECT * FROM lockinfo2;
-- try several updates
BEGIN WORK;
SELECT ctid, xmin, * FROM lockdemo;
UPDATE lockdemo SET col = 4;
SELECT ctid, xmin, * FROM lockdemo;
SELECT pg_backend_pid();
SELECT txid_current();
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 5; SELECT pg_sleep(0.300);
COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 6; SELECT pg_sleep(0.300);
COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 7; SELECT pg_sleep(0.300);
COMMIT;' | sed 's/^/\t/g' &
SELECT pg_sleep(0.100);
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM pg_locks;' | sed 's/^/\t/g'
COMMIT;
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
SELECT pg_sleep(0.300);
DELETE FROM lockdemo;
INSERT INTO lockdemo VALUES (1);
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
