Re: simple update query stuck

On Tue, Apr 01, 2014 at 01:37:17PM -0700, Si Chen wrote:
> You are right. That was the problem. I tried the query from
> http://wiki.postgresql.org/wiki/Lock_Monitoring and found a COMMIT
> transaction that was blocking it.
>
> I restarted postgresql again, and (it seems) everything went back to
> normal. Was there another way to unlock the table then?

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.)

I suspect you need to get your locks in a consistent order or you'll
continue to have this problem.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca