Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uuaja-00DGaJ-1W for pgsql-general@arkaria.postgresql.org; Fri, 05 Sep 2025 18:00:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uuajZ-009Epx-1F for pgsql-general@arkaria.postgresql.org; Fri, 05 Sep 2025 18:00:25 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uuajY-009EnT-II for pgsql-general@lists.postgresql.org; Fri, 05 Sep 2025 18:00:25 +0000 Received: from mail-ed1-x52e.google.com ([2a00:1450:4864:20::52e]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uuajW-000iVV-2q for pgsql-general@postgresql.org; Fri, 05 Sep 2025 18:00:23 +0000 Received: by mail-ed1-x52e.google.com with SMTP id 4fb4d7f45d1cf-62105d21293so2372463a12.1 for ; Fri, 05 Sep 2025 11:00:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757095221; x=1757700021; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=ZFEMQ+HnfYuYCE1EVQ+BLBTIvad4nbd6jCtHBHkYQgA=; b=GrMWAFLDqKSZ8b1xTiexN1Qdm0alOlXnevmNcQzr8NjHtMMT/CMEjYCw15+rKBThso xlbzZo/kAIoSn/QJz1GvpaCmtLSPjBX3lTB0nFBKIxBcPky3S/anTP9jYlHax6wC74en kvmK7J5MzFXbYF96JHPEDr55qYQVfBQfOemJEfnzCl1iKCkTYI/Uw4UBRlFHMBvW2zfM JKS1UURuA1E0LxEEatuJghLZP047MI9otvor3kFFxq72XQEPWk89pcSPgkf4hlDTC4Zf nZ55FaiA6iqgHcJ+gQ4mtHtlxVL8CWATGStD4Uv3xOA5xxftOnepCnERs+JMeAQs1qdB gq6w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757095221; x=1757700021; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=ZFEMQ+HnfYuYCE1EVQ+BLBTIvad4nbd6jCtHBHkYQgA=; b=XRTymxBGtTH2HjhmQjXIZwwFpq4vQ0KucMvnmggLgM4gUG0p6x5ZyAO6KQkmpK4kom Tm6+uo10AkKxvMLrFZPfH2PcOm8IgcDtukEE8D8Vu4q24QAwx1MGTZTkOrrT5rQVZ9Lk cg28KaigEMGSJ6CmxdN6RfwAzLBGizmGRQfANgB2FEaYUdVBpk/3VWqk6ju1itQ8Kl5h 4jV6604qKeW9IjSOkDoE1pFq1LGiVmBkb77aEqtYSLndw2d3xnSUjVBxsQ/gSdxSbAIA q9DYIa15LxwVl7bAh96wRR+20svkCQmc2dSuDtuw1MmmWYzxC1JF9H3aV632EPTDWGeW dEtA== X-Gm-Message-State: AOJu0YwQm2jiWQ8wJIELxQUB3aHDCCf0KMR9tWnoGiZy/afSlp2hgQEI i5ZfHGuOjBQxrV9cPyhDC9VPZVTz0D0D9k4KDJK+bPmh53QYktu/ZQF/gnuvkSI1TedvjoYQANd x4Gmxg7y1LTHuLhkfCmU320dhP6oJRG0= X-Gm-Gg: ASbGncvBu9xgd4oga8uayOJ2E9dBsxgIU1HLV4Qr4XedDxkkUkyAHc5mHOt3rhpXkg1 eQWXqN0febJOiE06r4+kLQRcKMD/zOwcMA7zYA+mtSNPS/n8LOevfsfzCrsEaFv8nvzvArrYKyx vyzeFiht83LlS1agKyFUtLxm0CQ3yTU+UWjGstsdHQw8XQRCEH8lj9WtdFqnf1jIis2IpOZLi/8 WbVXaXYMDit0kfltf0N X-Google-Smtp-Source: AGHT+IEV00B1Gf/nK9ZGLCCX5PdIVfgedFjkDQqSxw+qmEYjOjyFWTjwKRnOhXB6h/kDT2H4a6dpeyNVCvyJnhFheE4= X-Received: by 2002:a05:6402:34d6:b0:61d:9022:b4af with SMTP id 4fb4d7f45d1cf-61d9022b54dmr19096512a12.28.1757095220841; Fri, 05 Sep 2025 11:00:20 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Justin Date: Fri, 5 Sep 2025 14:00:09 -0400 X-Gm-Features: Ac12FXyxCTb-NFTRwJf5BfDU8xKtwk5i5JSA7Nq8f2wk0lPdPnr-lqM9pJ1c8W8 Message-ID: Subject: Re: LWLock SerializableFinishedList To: Alec Cozens Cc: "pgsql-general@postgresql.org" Content-Type: multipart/alternative; boundary="0000000000007993af063e119c82" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007993af063e119c82 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Sep 5, 2025 at 1:02=E2=80=AFPM Alec Cozens = wrote: > Hi > > > > I=E2=80=99m having trouble with PostgreSQL 16.8 on Windows where for mayb= e 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=E2=80=99t seem to cancel the= se > =E2=80=9Cactive=E2=80=9D 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 =E2=80=9Cactive=E2=80=9D but hanging connectio= ns running this > stored procedure or the associated commands generated by npgqql associate= d > with opening, closing and returning connections to the pool. > > > > I can=E2=80=99t find any information about SerializeableFinishedList or w= hy all > the standard timeout parameters seem to have no effect on the cancellatio= n > of these hung connections. The npgsql client configuration talks about > clearing idle connections, but these are not idle, they are active but hu= ng. > > > > 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 --0000000000007993af063e119c82 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

On Fri, Sep 5, 2025= at 1:02=E2=80=AFPM Alec Cozens <acozens@pixelpower.com> wrote:

Hi

=C2=A0

I=E2=80=99m having trouble with PostgreSQL 16.8 on W= indows where for maybe days it all works perfectly until the number of acti= ve connections start increasing, until over say 10 minutes all 97 connectio= ns are active but seemingly waiting on LWLock on SerializableFinishedList. They will remain in this locked state for som= e arbitrary period, up to 1 hour 40 minutes, after which the connections wi= ll all clear apparently simultaneously and the application continues.

=C2=A0

The connections are opened, a few command executed a= nd then closed and returned to the connection pool.

=C2=A0

The application runs on the same server as the postg= resql service.

=C2=A0

Changing statement_timeout to 1 minute doesn=E2=80= =99t seem to cancel these =E2=80=9Cactive=E2=80=9D connections.

=C2=A0

Most of the application runs in READ COMMITTED isola= tion level, but the particular stored procedure that seems to cause the iss= ue runs in SERIALIZED. We end up with =E2=80=9Cactive=E2=80=9D but hanging = connections running this stored procedure or the associated commands generated by npgqql associated with opening, closing and returnin= g connections to the pool.

=C2=A0

I can=E2=80=99t find any information about Serialize= ableFinishedList or why all the standard timeout parameters seem to have no= effect on the cancellation of these hung connections. The npgsql client co= nfiguration talks about clearing idle connections, but these are not idle, they are active but hung.

=C2=A0

Any thoughts on the matter, or what might cause=C2= =A0 a lock on SerializableFinishedList would be much appreciated.=

=C2=A0

Regards,

Alec



I am betting all the waiti= ng sessions are waiting on a COMMIT from another session.=C2=A0 This means = all the other sessions are dependent on row(s) locked/updated by the first = session.=C2=A0 The=C2=A0other 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.=C2=A0

Without re= viewing 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.=C2=A0 Do you have lock= _timeout set?=C2=A0 =C2=A0Sharing the code of the function and what the oth= er sessions are doing,=C2=A0 this way we can attempt to duplicate this beha= vior .


Keep in mind Serializing transactions have quirky behavio= r like this ,=C2=A0 the=C2=A0transaction has to validate that no other upda= te or insert is going to cause a problem with the result.

Thanks
= Justin=C2=A0 =C2=A0
=C2=A0 =C2=A0 =C2=A0
--0000000000007993af063e119c82--