Обсуждение: simple update query stuck
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
Do these queries update more than one row? I ran into a similar issue a year ago, where two multi-row updates would deadlock because they processed rows in a different order. I'd love to see UPDATE support ORDER BY to fix this, but it doesn't yet. (If I ever try contributing to Postgres, this is a feature I'd love to add. It seems like it might be an easy one for a newbie to tackle.) Paul On Tue, Apr 1, 2014 at 12:51 PM, Si Chen <sichen@opensourcestrategies.com> wrote: > Hello, > > I'm using postgresql 9.0.13, and I have a simple query that seems to be > stuck. I did a > postgres=# select procpid, query_start, waiting, current_query from > pg_stat_activity; > > procpid | query_start | waiting | > current_query > > 32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > > 32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE > public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, > THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, > LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE > GL_ACCOUNT > _ID=$9 AND ORGANIZATION_PARTY_ID=$10 > > As you can see this is a pretty simple update query, but it's been running > 24 minutes in one thread and nearly 10 minutes in the other thread. What's > also strange is it's not trigger a transaction timeout either. > > I've already re-started the database recently, and there's not too many > threads executing -- just 38 threads total. Is it possible that the table > is corrupted or needs repair? > > -- > Si Chen > Open Source Strategies, Inc. > sichen@opensourcestrategies.com > http://www.OpenSourceStrategies.com > LinkedIn: http://www.linkedin.com/in/opentaps > Twitter: http://twitter.com/opentaps > -- _________________________________ Pulchritudo splendor veritatis.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen Sent: Tuesday, April 01, 2014 3:51 PM To: pgsql-general@postgresql.org Subject: [GENERAL] simple update query stuck Hello, I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck. I did a postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity; procpid | query_start | waiting | current_query 32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2,THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8WHERE GL_ACCOUNT _ID=$9 AND ORGANIZATION_PARTY_ID=$10 32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2,THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8WHERE GL_ACCOUNT _ID=$9 AND ORGANIZATION_PARTY_ID=$10 As you can see this is a pretty simple update query, but it's been running 24 minutes in one thread and nearly 10 minutesin the other thread. What's also strange is it's not trigger a transaction timeout either. I've already re-started the database recently, and there's not too many threads executing -- just 38 threads total. Is itpossible that the table is corrupted or needs repair? -- Si Chen Open Source Strategies, Inc. sichen@opensourcestrategies.com http://www.OpenSourceStrategies.com LinkedIn: http://www.linkedin.com/in/opentaps Twitter: http://twitter.com/opentaps Both queries are "waiting". Your table must be locked. Check pg_locks. Regards, Igor Neyman
Could they both be trying to update the same row, resulting in a deadlock?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Tuesday, April 01, 2014 3:51 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] simple update query stuck
Hello,
I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck. I did a
postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity;
procpid | query_start | waiting | current_query
32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT
_ID=$9 AND ORGANIZATION_PARTY_ID=$10
32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT
_ID=$9 AND ORGANIZATION_PARTY_ID=$10
As you can see this is a pretty simple update query, but it's been running 24 minutes in one thread and nearly 10 minutes in the other thread. What's also strange is it's not trigger a transaction timeout either.
I've already re-started the database recently, and there's not too many threads executing -- just 38 threads total. Is it possible that the table is corrupted or needs repair?
--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Si Chen
Sent: Tuesday, April 01, 2014 3:51 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] simple update query stuckBoth queries are "waiting". Your table must be locked.Hello,
I'm using postgresql 9.0.13, and I have a simple query that seems to be stuck. I did a
postgres=# select procpid, query_start, waiting, current_query from pg_stat_activity;
procpid | query_start | waiting | current_query
32605 | 2014-04-01 12:39:46.957666-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT
_ID=$9 AND ORGANIZATION_PARTY_ID=$10
32685 | 2014-04-01 12:25:10.378481-07 | t | UPDATE public.GL_ACCOUNT_ORGANIZATION SET ROLE_TYPE_ID=$1, FROM_DATE=$2, THRU_DATE=$3, POSTED_BALANCE=$4, LAST_UPDATED_STAMP=$5, LAST_UPDATED_TX_STAMP=$6, CREATED_STAMP=$7, CREATED_TX_STAMP=$8 WHERE GL_ACCOUNT
_ID=$9 AND ORGANIZATION_PARTY_ID=$10
As you can see this is a pretty simple update query, but it's been running 24 minutes in one thread and nearly 10 minutes in the other thread. What's also strange is it's not trigger a transaction timeout either.
I've already re-started the database recently, and there's not too many threads executing -- just 38 threads total. Is it possible that the table is corrupted or needs repair?
--
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
Check pg_locks.
Regards,
Igor Neyman
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps
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@crankycanuck.ca
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.
Andrew Sullivan <ajs@crankycanuck.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
On Tue, Apr 01, 2014 at 07:00:16PM -0400, Tom Lane wrote: > 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. I bet the case I was thinking of was the NOTIFY example. That never occurred to me as an explanation, but now that you mention it, it seems quite likely to me. More generally (and for the OP's problem), my experience is that lots of updates against the same rows in an unpredictable order is an excellent way to run into trouble, and long-running transactions are a major source of these problems. Without a more detailed report about what is going on in the present case, I don't think it's going to be possible to diagnose better than has been done. A -- Andrew Sullivan ajs@crankycanuck.ca
On Tue, Apr 01, 2014 at 07:00:16PM -0400, Tom Lane wrote:I bet the case I was thinking of was the NOTIFY example. That never
> 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.
occurred to me as an explanation, but now that you mention it, it
seems quite likely to me.
More generally (and for the OP's problem), my experience is that lots
of updates against the same rows in an unpredictable order is an
excellent way to run into trouble, and long-running transactions are a
major source of these problems. Without a more detailed report about
what is going on in the present case, I don't think it's going to be
possible to diagnose better than has been done.--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Si Chen
Open Source Strategies, Inc.
sichen@opensourcestrategies.com
http://www.OpenSourceStrategies.com
LinkedIn: http://www.linkedin.com/in/opentaps
Twitter: http://twitter.com/opentaps