public inbox for [email protected]
help / color / mirror / Atom feedFrom: Justin <[email protected]>
To: Alec Cozens <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: LWLock SerializableFinishedList
Date: Fri, 5 Sep 2025 14:00:09 -0400
Message-ID: <CALL-XeMUhHnqZx083rWx14xX2XjqBeq7h94TOfXBtoaSGzhxWg@mail.gmail.com> (raw)
In-Reply-To: <LO2P123MB17113181847230A9D67C8602CD03A@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
References: <LO2P123MB17113181847230A9D67C8602CD03A@LO2P123MB1711.GBRP123.PROD.OUTLOOK.COM>
On Fri, Sep 5, 2025 at 1:02 PM Alec Cozens <[email protected]> wrote:
> Hi
>
>
>
> I’m having trouble with PostgreSQL 16.8 on Windows where for maybe days it
> all works perfectly until the number of active connections start
> increasing, until over say 10 minutes all 97 connections are active but
> seemingly waiting on LWLock on SerializableFinishedList. They will remain
> in this locked state for some arbitrary period, up to 1 hour 40 minutes,
> after which the connections will all clear apparently simultaneously and
> the application continues.
>
>
>
> The connections are opened, a few command executed and then closed and
> returned to the connection pool.
>
>
>
> The application runs on the same server as the postgresql service.
>
>
>
> Changing statement_timeout to 1 minute doesn’t seem to cancel these
> “active” connections.
>
>
>
> Most of the application runs in READ COMMITTED isolation level, but the
> particular stored procedure that seems to cause the issue runs in
> SERIALIZED. We end up with “active” but hanging connections running this
> stored procedure or the associated commands generated by npgqql associated
> with opening, closing and returning connections to the pool.
>
>
>
> I can’t find any information about SerializeableFinishedList or why all
> the standard timeout parameters seem to have no effect on the cancellation
> of these hung connections. The npgsql client configuration talks about
> clearing idle connections, but these are not idle, they are active but hung.
>
>
>
> Any thoughts on the matter, or what might cause a lock on
> SerializableFinishedList would be much appreciated.
>
>
>
> Regards,
>
> Alec
>
>
I am betting all the waiting sessions are waiting on a COMMIT from another
session. This means all the other sessions are dependent on row(s)
locked/updated by the first session. The other sessions have to wait to
know if they have a SERIALIZATION conflict.
The reason statement_timeout is not working is because all the queries
executed and completed.
Without reviewing the code and logic based on the description of events I
am betting all the sessions got to the COMMIT stage and are waiting on
another Session.
Would be nice to know what pg_locks showed. Do you have lock_timeout set?
Sharing the code of the function and what the other sessions are doing,
this way we can attempt to duplicate this behavior .
Keep in mind Serializing transactions have quirky behavior like this ,
the transaction has to validate that no other update or insert is going to
cause a problem with the result.
Thanks
Justin
view thread (2+ 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]
Subject: Re: LWLock SerializableFinishedList
In-Reply-To: <CALL-XeMUhHnqZx083rWx14xX2XjqBeq7h94TOfXBtoaSGzhxWg@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