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

how to find transaction associated with a lock

From: Si Chen <schen(at)graciousstyle(dot)com>
To: pgsql-general(at)postgresql(dot)org, pgsql-novice(at)postgresql(dot)org,pgsql-admin(at)postgresql(dot)org
Subject: how to find transaction associated with a lock
Date: 2004-07-22 19:16:38
Message-ID: 41001296.3090609@graciousstyle.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-generalpgsql-novice
First of all, my apologies for posting it to 3 lists: I'm not sure if 
this is an admin question, or I only have this question because I'm a 
novice, or some combination thereof.

I have a table which appears to be locked on an INSERT.  Looking in 
pg_locks, I get the result (shown below).  I can use the relation.oid 
and the database.oid to get to the pg_class and pg_database and find out 
which tables are locked.  But the transactions that have exclusive locks 
marked against them, they have transaction identifiers only.  How do I 
use these transactions identifiers to find which tables are currently 
under exclusive lock?  It seems that there is a transaction_id 
associated with tables, but that involves going table by table through 
(500+) tables and does not seem too practical.

Does anyone know of a way to go from transactions identifiers to the 
actual transaction--which tables, which statements, etc. etc.?

Thanks in advance,

Si Chen


>  select * from pg_locks

relation database transaction pid mode granted
NULL NULL 3422568 21440 ExclusiveLock TRUE
125703 123502 NULL 2188 AccessShareLock TRUE
124863 123502 NULL 2188 AccessShareLock TRUE
125435 123502 NULL 2188 AccessShareLock TRUE
125435 123502 NULL 2188 RowShareLock TRUE
124205 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 27149 RowShareLock TRUE
125684 123502 NULL 21440 AccessShareLock TRUE
125684 123502 NULL 21440 RowShareLock TRUE
125089 123502 NULL 2188 AccessShareLock TRUE
125273 123502 NULL 27149 AccessShareLock TRUE
250381 123502 NULL 27149 AccessShareLock TRUE
250381 123502 NULL 27149 RowExclusiveLock TRUE
124518 123502 NULL 5510 AccessShareLock TRUE
124518 123502 NULL 5510 RowExclusiveLock TRUE
250389 123502 NULL 1069 AccessShareLock TRUE
124066 123502 NULL 27149 AccessShareLock TRUE
124066 123502 NULL 27149 RowExclusiveLock TRUE
124300 123502 NULL 5510 AccessShareLock TRUE
125081 123502 NULL 1069 AccessShareLock TRUE
NULL NULL 3377982 21292 ShareLock FALSE
NULL NULL 3419769 12638 ShareLock FALSE
NULL NULL 3440992 23620 ExclusiveLock TRUE
125081 123502 NULL 27149 AccessShareLock TRUE
250409 123502 NULL 27149 AccessShareLock TRUE
125341 123502 NULL 21292 AccessShareLock TRUE
124879 123502 NULL 2188 AccessShareLock TRUE
124818 123502 NULL 21440 AccessShareLock TRUE
NULL NULL 3387552 21292 ExclusiveLock TRUE
124518 123502 NULL 27149 AccessShareLock TRUE
124518 123502 NULL 27149 RowExclusiveLock TRUE
125643 123502 NULL 8649 AccessShareLock TRUE
125443 123502 NULL 27149 AccessShareLock TRUE
250395 123502 NULL 1069 AccessShareLock TRUE
124820 123502 NULL 27149 AccessShareLock TRUE
125317 123502 NULL 21292 AccessShareLock TRUE
125038 123502 NULL 27149 AccessShareLock TRUE
125643 123502 NULL 1069 AccessShareLock TRUE
125317 123502 NULL 5510 AccessShareLock TRUE
NULL NULL 3366868 8649 ShareLock FALSE
124209 123502 NULL 27149 AccessShareLock TRUE
NULL NULL 3366868 5510 ShareLock FALSE
250381 123502 NULL 2188 AccessShareLock TRUE
250381 123502 NULL 2188 RowExclusiveLock TRUE
125353 123502 NULL 27149 AccessShareLock TRUE
125435 123502 NULL 27149 AccessShareLock TRUE
125435 123502 NULL 27149 RowShareLock TRUE
123916 123502 NULL 2188 AccessShareLock TRUE
250395 123502 NULL 8649 AccessShareLock TRUE
125337 123502 NULL 27149 AccessShareLock TRUE
250413 123502 NULL 2188 AccessShareLock TRUE
250413 123502 NULL 2188 RowExclusiveLock TRUE
250393 123502 NULL 27149 AccessShareLock TRUE
250393 123502 NULL 27149 RowExclusiveLock TRUE
124462 123502 NULL 27149 AccessShareLock TRUE
124462 123502 NULL 27149 RowShareLock TRUE
125038 123502 NULL 8649 AccessShareLock TRUE
123781 123502 NULL 2188 AccessShareLock TRUE
NULL NULL 3366868 2188 ShareLock FALSE
NULL NULL 3389227 8649 ExclusiveLock TRUE
125341 123502 NULL 5510 AccessShareLock TRUE
NULL NULL 3419769 21440 ShareLock FALSE
NULL NULL 3366868 21307 ShareLock FALSE
124277 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 2188 RowExclusiveLock TRUE
124935 123502 NULL 2188 AccessShareLock TRUE
250413 123502 NULL 27149 AccessShareLock TRUE
250413 123502 NULL 27149 RowExclusiveLock TRUE
123701 123502 NULL 21440 AccessShareLock TRUE
123701 123502 NULL 21440 RowExclusiveLock TRUE
NULL NULL 3366868 27149 ExclusiveLock TRUE
124883 123502 NULL 27149 AccessShareLock TRUE
124300 123502 NULL 27149 AccessShareLock TRUE
124300 123502 NULL 27149 RowExclusiveLock TRUE
125273 123502 NULL 8649 AccessShareLock TRUE
NULL NULL 3419769 2188 ExclusiveLock TRUE
131092 123502 NULL 5510 AccessShareLock TRUE
124462 123502 NULL 2188 AccessShareLock TRUE
124462 123502 NULL 2188 RowShareLock TRUE
250381 123502 NULL 21307 AccessShareLock TRUE
NULL NULL 3366868 1069 ShareLock FALSE
125643 123502 NULL 27149 AccessShareLock TRUE
16757 123502 NULL 23620 AccessShareLock TRUE
250389 123502 NULL 2188 AccessShareLock TRUE
250389 123502 NULL 2188 RowShareLock TRUE
250389 123502 NULL 2188 RowExclusiveLock TRUE
124818 123502 NULL 12638 AccessShareLock TRUE
125081 123502 NULL 8649 AccessShareLock TRUE
NULL NULL 3366872 21307 ExclusiveLock TRUE
123745 123502 NULL 27149 AccessShareLock TRUE
124578 123502 NULL 2188 AccessShareLock TRUE
124578 123502 NULL 2188 RowShareLock TRUE
124828 123502 NULL 2188 AccessShareLock TRUE
124813 123502 NULL 12638 AccessShareLock TRUE
124813 123502 NULL 12638 RowExclusiveLock TRUE
125113 123502 NULL 27149 AccessShareLock TRUE
124813 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 21307 AccessShareLock TRUE
250389 123502 NULL 21307 RowExclusiveLock TRUE
250393 123502 NULL 21307 AccessShareLock TRUE
250391 123502 NULL 21307 AccessShareLock TRUE
124813 123502 NULL 21440 AccessShareLock TRUE
124813 123502 NULL 21440 RowShareLock TRUE
131092 123502 NULL 21292 AccessShareLock TRUE
124518 123502 NULL 21292 AccessShareLock TRUE
124518 123502 NULL 21292 RowExclusiveLock TRUE
125273 123502 NULL 1069 AccessShareLock TRUE
124883 123502 NULL 2188 AccessShareLock TRUE
123697 123502 NULL 27149 AccessShareLock TRUE
124781 123502 NULL 27149 AccessShareLock TRUE
NULL NULL 3419997 12638 ExclusiveLock TRUE
250393 123502 NULL 8649 AccessShareLock TRUE
250393 123502 NULL 8649 RowExclusiveLock TRUE
NULL NULL 3379382 1069 ExclusiveLock TRUE
250393 123502 NULL 1069 AccessShareLock TRUE
250393 123502 NULL 1069 RowExclusiveLock TRUE
125038 123502 NULL 1069 AccessShareLock TRUE
NULL NULL 3377982 5510 ExclusiveLock TRUE
124300 123502 NULL 21292 AccessShareLock TRUE
125419 123502 NULL 27149 AccessShareLock TRUE
250385 123502 NULL 27149 AccessShareLock TRUE
250389 123502 NULL 8649 AccessShareLock TRUE

124 row(s)



Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2004-07-22 20:45:03
Subject: Re: how to find transaction associated with a lock
Previous:From: Tom LaneDate: 2004-07-22 18:13:52
Subject: Re: Starting ProgreSQL server

pgsql-admin by date

Next:From: Tom LaneDate: 2004-07-22 20:19:53
Subject: Re: [HACKERS] Point in Time Recovery
Previous:From: Tom LaneDate: 2004-07-22 18:24:07
Subject: Re: Invalid page header

pgsql-general by date

Next:From: Tom LaneDate: 2004-07-22 20:45:03
Subject: Re: how to find transaction associated with a lock
Previous:From: Steve WamplerDate: 2004-07-22 18:04:23
Subject: Re: Best way to recover from crash?

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