Re: Document prepared transaction lock waits with two_phase subscriptions

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
Cc: Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com>, Zhijie Hou <houzhijie22(at)gmail(dot)com>, "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>, shveta malik <shveta(dot)malik(at)gmail(dot)com>, Dilip Kumar <dilipbalaut(at)gmail(dot)com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: 2026-11-17 06:49:07
Message-ID: CAD21AoAOuwKeOO4H+YHYS67sPpseYRUXioXdjDNhRESZwiJ=iQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Nov 16, 2026 at 6:54 PM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Mon, Nov 16, 2026 at 7:14 PM Bertrand Drouvot
> <bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
> >
> > On Mon, Nov 16, 2026 at 08:20:45PM +0800, Zhijie Hou wrote:
> > > On Mon, Nov16, 2026 at 05:36 PM Bertrand Drouvot wrote:
> > > >
> > > > === 1
> > > >
> > > > I wonder if it would also be worth mentioning a nonzero lock_timeout
> > > > as a possible mitigation too?
> > >
> > > Before adding this, I'd like to confirm what would be a good way to suggest
> > > users configure lock_timeout here. Setting lock_timeout globally (or at the
> > > database level) would also apply to the logical replication apply worker
> > > itself (potentially causing it to error out on normal lock waits). On the
> > > other hand, setting it per session/transaction before running commands that
> > > take conflicting locks (such as TRUNCATE, ALTER TABLE, LOCK, etc.) avoids
> > > that, though there are many commands that can take conflicting locks so
> > > users would need to remember to set it whenever running them (or set it per
> > > role for administrative sessions). Do you have per-session usage in mind, or
> > > another way of using lock_timeout?
> >
> > I had per-session/transaction usage in mind, before executing commands that might
> > request conflicting table locks. A per role setting also makes sense when a
> > dedicated role is used for administrative commands. I agree that we should not
> > recommend setting it globally or at the database level, as that would also affect
> > the apply workers.
> >
> > What about juse one sentence, something like?
> >
> > "
> > Using a nonzero lock_timeout in sessions or transactions that might request such
> > conflicting locks can prevent them from waiting indefinitely.
> > "
>
> I think configuring such a timeout could be tricky for users
> especially when the commands are from scripts. We don't
> recommend/document such a setting for blocking due to prepared
> transactions, for example, I find the following in the docs: "In cases
> involving a conflicting prepared transaction, it may not be possible
> to make progress until the prepared transaction commits or rolls
> back." for serialization failure handling. So, on similar lines we
> should try to avoid lock_timeout recommendation here but if you or
> others still feel it is helpful then we can consider.
>
> Overall, I agree with you and Hou-san that documenting the lock waits
> due to prepared transactions with two_phase enabled subscriptions is a
> better approach as we do at other places especially for back-branches.
> Even if we decide to do some special lock-detection mechanism for
> prepared transactions (by apply worker) as Hou-San mentioned in his
> first email, it should be for HEAD but considering the documented
> behavior of prepared transactions and no user report, I feel we don't
> need to go there.

+1 for documenting this behavior. I think we can backpatch it.

Regarding the proposed description, I think it would be better to
suggest cancelling the waiting session as the primary way to resolve
the situation, and to mention manually rolling back the prepared
transaction as an alternative. Cancelling the waiting session always
unblocks the apply worker, whereas the latter requires several steps.
On the other hand, manually rolling back the prepared transaction
requires several steps: disabling the subscription, waiting for the
worker to exit, setting two_phase to false, and enabling the
subscription again etc.. Also, I think the description should say to
disable the subscription first, then roll back the prepared
transaction, set two_phase to false, and enable the subscription again
as Kuroda-san suggested. It might also be worth noting that the
transaction is applied again after the waiting command completes.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Fujii Masao 2026-11-17 06:55:14 Re: FATAL: could not send end-of-streaming message to primary: no COPY in progress
Previous Message Etsuro Fujita 2026-11-17 06:45:47 Re: Asynchronous MergeAppend