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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
