Re: LWLock SerializableFinishedList

On Fri, Sep 5, 2025 at 1:02 PM Alec Cozens <acozens(at)pixelpower(dot)com> 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