Re: Tracking down a deadlock

On Sat, May 02, 2009 at 11:48:21AM -0400, Tom Lane wrote:
> Bill Moseley <moseley(at)hank(dot)org> writes:
> > Not getting any nibbles, so allow me to try a short question:
> > If I have a deadlock situation (that will be reported as such by
> > Postgresql once the deadlock_timeout passes), does pg_stat_activity
> > show the queries that are blocking each other?
>
> In 8.2 or later it should do so; in prior versions there could be some
> lag involved. Another thing to keep in mind is that backends like to
> cache copies of the pg_stat_activity view --- if you are watching it
> to see what is happening, your view is only current as of the start
> of your current transaction. Or you can do pgstat_clear_snapshot()
> to force collection of new info.

Thanks Tom,

I'm not clear how to run pgstat_clear_snapshot(), but I looked again and before
I ran my test script and pg_stat_activity doesn't list any queries waiting.
So, I don't believe it's showing stale data.

Then when I run the test script (which runs the same transaction in two
processes at the same time) and get a deadlock the same query is shown twice
both with "waiting" set true:

UPDATE account set foo = 123 where id = $1

And if I remove that update from the transaction I no longer have the deadlock.
So, it seems like that is the problem update.

Is postgresql telling me that it's deadlocked on two transactions trying to run
that same update?

There are no other updates to that account table in the transaction, so I'm
confused how that is causing a deadlock.

Is there something else I can do to understand what exactly is the reason for
the deadlock?

Thanks,

--
Bill Moseley.
moseley(at)hank(dot)org
Sent from my iMutt