Subject: RE: LWLock SerializableFinishedList

From: Alec Cozens <[email-masked]>
Date: Mon, 8 Sep 2025 10:27:31 +0000

Thanks Justin,

We’ll try the lock_timeout configuration. We originally discarded it’s use because statement_timeout seemed to cover it according to the documentation.
Attached are some of information from the pg_locks and pg_stat_activity we are logging when this crisis happens.

The stored procedures in the activity information are called using SERIALIZED isolation I believe (still investigating). They will also have been called without an explicit transaction. Do you think that it is worth using explicit transactions for calling these stored procedures?

Also reviewing if we really need SERIALIZED and could instead use READ COMMITTED. Would that be likely to mitigate against this happening?

There are lots of stuff sent by npgsql attempting to clear and reset the pooled connection which are also being stalled on this lock.

Once this situation is in play, it seems to spread to all the databases we use in the application. Is it expected that this lock specific to a database or the cluster?

So far we are unable to replicate this error, and it only happens at a particular installation.

We also get some occurrences of

could not truncate directory "pg_serial": apparent wraparound

in the postgres logs. I’ve always ignored this as I believed it to be benign or incorrectly reported.

Thanks for your help.

Regards,
Alec

From: Justin <[email-masked]>
Sent: 05 September 2025 19:00
To: Alec Cozens <[email-masked]>
Cc: [email-masked]
Subject: Re: LWLock SerializableFinishedList

** This email is from an EXTERNAL sender **

On Fri, Sep 5, 2025 at 1:02 PM Alec Cozens <[email-masked]<mailto:[email-masked]>> 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

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

______________________________________________________________________
This email has been scanned by the Symantec Email Security.cloud service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________

---

From: Justin <[email-masked]>
Date: Tue, 9 Sep 2025 15:42:30 -0400

Also reviewing if we really need SERIALIZED and could instead use READ
COMMITTED. Would that be likely to mitigate against this happening?

PostgreSQL can NOT  go below READ COMMITTED in transaction isolation
levels.  Read Committed is the default mode for all transactions in
PostgreSQL

https://www.postgresql.org/docs/17/transaction-iso.html

Unless there is a very specific need for serializing transactions such as
financial calculations or updating and calculating  the remaining number of
tickets to sell for a concert,  Serialization adds a lot of overhead for
not much gain..

thanks