how to find transaction associated with a lock - Mailing list pgsql-admin
| From | Si Chen |
|---|---|
| Subject | how to find transaction associated with a lock |
| Date | |
| Msg-id | 41001296.3090609@graciousstyle.com Whole thread Raw |
| Responses |
Re: how to find transaction associated with a lock
|
| List | pgsql-admin |
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)
pgsql-admin by date: