Обсуждение: How can you have an Exclusive Lock on nothing? please help me understand this lock monitoring query output
How can you have an Exclusive Lock on nothing? please help me understand this lock monitoring query output
От
Aleksey Tsalolikhin
Дата:
Hi. I use the following query (from http://wiki.postgresql.org/wiki/Lock_Monitoring) to monitor locks; and I've got an ExlusiveLock that does not have a relation name associated with it. What is locked with the Exclusive Lock in this case, please? (it's between "d" and "e" tables below) psql -U postgres -d ddcKeyGen -c 'select pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,50), 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;' Output: a_index | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 b_index | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 c_index | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 d | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 | | ExclusiveLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 e | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 f_index | | ShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 g_index | | AccessShareLock | t | user | | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 Thanks, Aleksey
Aleksey Tsalolikhin <atsaloli.tech@gmail.com> writes: > Hi. I use the following query (from > http://wiki.postgresql.org/wiki/Lock_Monitoring) > to monitor locks; and I've got an ExlusiveLock that does not have a relation > name associated with it. What is locked with the Exclusive Lock in this case, > please? (it's between "d" and "e" tables below) Try printing all fields from the pg_lock view and you may be enlightened. Transaction IDs are one example of a lock that is not on a relation. Also be aware that if you are viewing a lock that is for some other DB besides the one you're sitting in, the pg_class join will give nothing. Also be aware that > psql -U postgres -d ddcKeyGen -c 'select > pg_class.relname,pg_locks.transactionid, pg_locks.mode, pg_locks.granted, > pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,50), > 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;' > > > > Output: > > a_index | | AccessShareLock > | t | user | > | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 > b_index | | AccessShareLock > | t | user | > | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 > c_index | | AccessShareLock > | t | user | > | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 > d | | > AccessShareLock | t | user | > | 2011-04-14 17:36:01.257669-07 | > 00:21:28.847825 | 16798 > | > | ExclusiveLock | t | user | > | 2011-04-14 17:36:01.257669-07 | > 00:21:28.847825 | 16798 > e | | > AccessShareLock | t | user | > | 2011-04-14 17:36:01.257669-07 | > 00:21:28.847825 | 16798 > f_index | | ShareLock > | t | user | > | 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 > g_index | | AccessShareLock | t > | user | | > 2011-04-14 17:36:01.257669-07 | 00:21:28.847825 | 16798 > > > Thanks, > Aleksey -- Jerry Sievers Postgres DBA/Development Consulting e: gsievers19@comcast.net p: 305.321.1144
On Fri, Apr 15, 2011 at 2:12 AM, Aleksey Tsalolikhin <atsaloli.tech@gmail.com> wrote: > Hi. I use the following query (from > http://wiki.postgresql.org/wiki/Lock_Monitoring) > to monitor locks; and I've got an ExlusiveLock that does not have a relation > name associated with it. What is locked with the Exclusive Lock in this case, > please? (it's between "d" and "e" tables below) Locks can be held on databases, relations, rows and also show for transactions. So the absence of a relation name just means it is one of the other kinds of lock. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services