Re: simple update query stuck

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:
> Probably you could have killed one of the queries. But it sounds like
> what's happening is that you have multiple queries that are all trying
> to update the same rows in a different order. It may be that none of
> these is strictly deadlocked, in that no query is waiting on a lock
> that another query has, but rather is waiting on a lock that another
> query will release only when _it_ gets a lock that another query has
> and so on. (Maybe things have gotten better, but in my experience
> it's possible to set up a chain of locks such that it doesn't look
> like a deadlock to the detector, but the lock chain is such that no
> query will ever be able to release.)

AFAIK the deadlock detector will catch any case where the waits-for
loop is entirely internal to the database; if you've got a reproducible
case where it doesn't, I'd like to see it. However, it's certainly true
that you can get deadlock situations when some of the blocking conditions
exist outside the database --- that is, to the server it looks like some
transaction(s) are blocked on another session that is idle-in-transaction,
but the client attached to that session is somehow waiting for another
one of the clients, in a way that isn't visible to the deadlock detector.
One way for that to happen without any external interconnections is if the
client is waiting for a NOTIFY that will never arrive because the would-be
sender is blocked.

regards, tom lane