Andrew Sullivan-8 wrote
> 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?
http://www.postgresql.org/docs/9.2/interactive/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE
I am curious why your original scan of pg-stat-query did not show this
offending session/transaction...did you omit it by chance?
> 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.
It sounds more like a long-running transaction (or something effectively
similar) is blocking updates of the target table through normal locking.
To be honest we seem to be lacking enough information, including the query
and locks state of the server during the stall, to make an informed guess as
to the real cause.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/simple-update-query-stuck-tp5798237p5798261.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
