Обсуждение: how to find transaction associated with a lock

Поиск
Список
Период
Сортировка

how to find transaction associated with a lock

От
Si Chen
Дата:
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)



Re: how to find transaction associated with a lock

От
Tom Lane
Дата:
Si Chen <schen@graciousstyle.com> writes:
> Does anyone know of a way to go from transactions identifiers to the
> actual transaction--which tables, which statements, etc. etc.?

See pg_stat_activity.

The pg_locks entries you are looking at do *not* represent table locks
of any kind.  This:
> NULL NULL 3366868 27149 ExclusiveLock TRUE
simply says that transaction 3366868 is still running.  This:
> NULL NULL 3366868 1069 ShareLock FALSE
says that process 1069 has elected to wait until transaction 3366868
finishes.  Normally the reason for doing that would be that transaction
3366868 holds a row lock (SELECT FOR UPDATE lock) on some row that
process 1069's transaction wants to acquire row lock on.

However, I suspect what you are really wishing is to find out the
individual row that is being contended for, and there is not enough
information in the system views to do that (mainly because we do not
keep per-row locking information in shared memory).

            regards, tom lane