Thread: simple update query stuck

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

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