From: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
To: Zhijie Hou <houzhijie22@gmail.com>
Cc: pgsql-hackers@lists.postgresql.org <pgsql-hackers@lists.postgresql.org>
Cc: amit.kapila16@gmail.com <amit.kapila16@gmail.com>
Cc: Nisha Moond <nisha.moond412@gmail.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: shveta malik <shveta.malik@gmail.com>
Cc: Dilip Kumar <dilipbalaut@gmail.com>
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
Date: Mon, 16 Nov 2026 09:36:12 +0000
Message-ID: <avrOjINXdJs1XiwW@bdtpg> (raw)
In-Reply-To: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
Hi,
On Fri, Nov 13, 2026 at 11:58:10AM +0800, Zhijie Hou wrote:
>
> To recover from this state, the user can either cancel the waiting local
> session on the subscriber so the apply worker can proceed, or manually roll
> back the prepared transaction on the subscriber, disable two_phase until
> that transaction is replicated again after being committed on the publisher,
> and then re-enable two_phase if required. Considering the existing caution
> about prepared transactions and the available workarounds, we suggest
> documenting this locking hazard and how to resolve it for logical
> replication as well.
Yeah, +1 for documenting it.
> Please see the attached patch for the documentation changes.
=== 1
I wonder if it would also be worth mentioning a nonzero lock_timeout as a possible
mitigation too?
=== 2
+ logical replication apply worker can be completed only by that worker, see
s/can be completed only by that worker/is normally completed by the subscription's apply worker/?
=== 3
+ <command>ALTER TABLE</command>), it has to wait, and later lock
+ requests on that table
s/later lock requests/later conflicting lock requests/?
=== 4
+ The apply worker then cannot reach
+ the <command>COMMIT PREPARED</command> or <command>ROLLBACK
+ PREPARED</command> that would release the lock, and the sessions
+ wait for each other. This is not detected as a deadlock, so
+ replication stalls.
what about?
or <command>ROLLBACK PREPARED</command>. This situation is not detected as a
deadlock, so replication can remain stalled indefinitely.
> Apart from documenting this, Amit, Nisha, and I also discussed off-list
> whether the apply worker could detect and resolve this wait cycle
> automatically. One option we considered is to have the apply worker walk its
> lock blocking chain once a lock wait exceeds deadlock_timeout. Starting from
> the lock it is waiting for, the worker would trace conflicting lock holders
> and conflicting waiters ahead of it in the wait queue (similar to how
> pg_blocking_pids() finds blockers) to see whether the chain leads back to a
> prepared transaction of the same subscription, and if so, cancel the session
> waiting on that prepared transaction (or raise an error if the worker is
> directly blocked by its own prepared transaction). However, this effectively
> introduces a separate wait-graph traversal alongside the existing deadlock
> detector, which seems overly complex for this scenario.
Yeah, I agree. Teaching the lock manager about this logical dependency seems too
complex.
> We also thought about a much simpler check where, on timeout, the apply
> worker only inspects the lock it is currently waiting on and treats it as a
> wait cycle if a prepared transaction of the same subscription also holds a
> lock on that same table. Unfortunately, that does not cover multi-table
> cases.
Agreed. FWIW, lock_timeout on the local session handles this multi-table case too.
> Given these trade-offs, we prefer to document this locking hazard and the
> workarounds for now.
Another options I could see (because the doc changes explain the workarounds)
would be to help diagnose:
1/ improve log_lock_waits to identify a prepared transaction holding a lock by
its XID, instead of reporting it as process 0.
2/ improve the existing pg_locks example query so that it identifies locks
held by both newly prepared and recovered prepared transactions.
Thoughts?
Regards,
--
Bertrand Drouvot
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: bertranddrouvot.pg@gmail.com, houzhijie22@gmail.com, amit.kapila16@gmail.com, nisha.moond412@gmail.com, sawada.mshk@gmail.com, shveta.malik@gmail.com, dilipbalaut@gmail.com
Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
In-Reply-To: <avrOjINXdJs1XiwW@bdtpg>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <avrOjINXdJs1XiwW@bdtpg>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox