Re: how to find transaction associated with a lock

Si Chen <schen(at)graciousstyle(dot)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