public inbox for [email protected]
help / color / mirror / Atom feedFrom: Edwin UY <[email protected]>
To: [email protected]
Cc: Tom Lane <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: [email protected]
Subject: Re: How to check if session is a hung thread/session
Date: Tue, 12 Nov 2024 09:14:24 +1300
Message-ID: <CA+wokJ8jMkhXCnHHVN7Ky9+aCazxRDHkfCio8WAUg69Co7+EcA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CA+wokJ_QejkPWUym3njMQCkeFQ_kRcRdah5S2eseocwsHLc-4w@mail.gmail.com>
<CAKFQuwZunKoiM4AO0BBAbRc73_unJoMJA5WmPFWYnUWj+s0Q_Q@mail.gmail.com>
<CA+wokJ96V5UuHhPStt+eMigkXsn7RJbMaiL-aK1K=S5aFQg66g@mail.gmail.com>
<[email protected]>
<CA+wokJ8VFQ1AkaqaCuND9A-+Xgq1rhdXioXNzQm7CSGPA5x2EQ@mail.gmail.com>
<[email protected]>
Hi Michel,
Thanks for the explanation. Didn't know about this default setting.
It doesn't happen all the time. It is reported in the log every 2-3 hours
and once or two at most during that time but it doesn't necessarily affect
the whole application.
This table is a Mule flow sort of table where there's 3 flows that check on
this table for things to process and then continue on further. 2 of them
goes fine and only one of them is failing intermittently.
[25751]:DETAIL: Process holding the lock: 22297. Wait queue: 25751.
[25751]:CONTEXT: while locking tuple (1,17) in relation "[table_name]"
[25751]:STATEMENT: [select SQL statement] for update
[25751]:LOG: process 25751 acquired ShareLock on transaction 114953443
after 4756.967 ms
On Tue, Nov 12, 2024 at 5:09 AM <[email protected]> wrote:
> Hi,
>
>
>
> The log you expose doesn’t mean a dead lock! It comes from setting
> deadlock_timeout parameter. Its value is 1 second by default. When a
> session waits for a lock more than this threshold then a test to see if we
> are in a deadlock condition is triggered. It is simply that we are waiting
> to obtain a lock for longer that the value of deadlock_timeout. Any way it
> could be interpreted as a performance problem when we are frequently
> waiting more than 1 sec for a lock…
>
>
>
> Regards
>
>
>
> *Michel SALAIS*
>
> *De :* Edwin UY <[email protected]>
> *Envoyé :* lundi 11 novembre 2024 07:45
> *À :* Tom Lane <[email protected]>
> *Cc :* David G. Johnston <[email protected]>;
> [email protected]
> *Objet :* Re: How to check if session is a hung thread/session
>
>
>
> Thanks Tom,
>
>
>
> OK, I've decided to 'painfully' look at the PostgreSQL RDS logs and it is
> showing something like below.
>
> There seems to be a locking/deadlock issue of some sort somewhere.
>
> I have checked the other days' log prior to the patching and these appear
> to be a 'normal' occurrence for this database and it wasn't affecting the
> application nonetheless.
>
> After the patching, it starts affecting the application. Not sure what
> else I can check on the Aurora PostgreSQL RDS end. I may request them to
> restart the app server.
>
>
>
> [25751]:LOG: process 25751 still waiting for ShareLock on transaction
> 114953443 after 1000.054 ms
>
> [25751]:DETAIL: Process holding the lock: 22297. Wait queue: 25751.
>
> [25751]:CONTEXT: while locking tuple (1,17) in relation "[table_name]"
>
> [25751]:STATEMENT: [SQL_STATEMENT] for update
> [25751]:LOG: process 25751 acquired ShareLock on transaction 114953443
> after 4756.967 ms
> [25751]:CONTEXT: while locking tuple (1,17) in relation " [table_name] "
>
>
>
>
>
> On Mon, Nov 11, 2024 at 5:45 PM Tom Lane <[email protected]> wrote:
>
> Edwin UY <[email protected]> writes:
> > I thought it could be the backend has sent something back to the client
> but
> > it never received it and it just kept on doing the same at some
> intervals.
>
> Your pg_stat_activity output shows the backend is idle, meaning it's
> waiting for a client command. While the session has been around for
> days, we can see the last client command was about 47 minutes ago,
> from your "now() - pg_stat_activity.query_start AS duration" column.
> I see no reason to think there is anything interesting here at all,
> except for a client that is sitting doing nothing for long periods.
>
> regards, tom lane
>
>
view thread (10+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: How to check if session is a hung thread/session
In-Reply-To: <CA+wokJ8jMkhXCnHHVN7Ky9+aCazxRDHkfCio8WAUg69Co7+EcA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox