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
