Re: How to identify the source of a deadlock?

Stefan Froehlich wrote:
> There is an application A doing some things in a database. In the middle
> of the program, application B is called which does some other things.
> Now for some reason application B hangs for certain inputs and I have to
> find out the reason for this. The sequence is:
>
> | A: BEGIN
> | A: [does some things]
> | A: [calls B]
> |
> | B: BEGIN
> | B: [does some things]
> | B: update bmeproduct set manufacturerpid='40913', leadtime='1' where idproduct=9681
> | B: [waits forever]
>
> When I look at pg_stat_activity, I can see the update statement with
> "waiting == 't'" for process B, which would not change for at least
> several hours. And I can see "<IDLE> in transaction" for process A
> which, of course, I know by the nature of the program.
>
> However, to eliminate the problem I would need the actual source of the
> lock. Program A does not ever touch the table "bmeproduct" (it does only
> things in a completely different part of the database - or at least: it
> _should_ do), so the lock must be introduces via some foreign key. But:
> is there any chance to find out WHICH table (or even record) is the
> cause of the trouble? This would be so helpful...

The pg_locks catalog has the information you want.

Maybe the Wiki article can help you:
http://wiki.postgresql.org/wiki/Lock_Monitoring

Yours,
Laurenz Albe