pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zhijie Hou <houzhijie22@gmail.com>
To: Bertrand Drouvot <bertranddrouvot.pg@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 20:20:45 +0800
Message-ID: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> (raw)
In-Reply-To: <avrOjINXdJs1XiwW@bdtpg>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
	<avrOjINXdJs1XiwW@bdtpg>

Hi,

On Mon, Nov16, 2026 at 05:36 PM Bertrand Drouvot wrote:
>
> 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.

Thanks for reviewing.

>
> === 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?

>
> === 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.

Thanks 2, 3 and 4 look good to me and addressed in V2.


> > 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?

Both sound like useful diagnostic improvements for prepared transactions 
in general (even outside logical replication), so +1 for discussing them 
in a separate thread.

-- 
Best Regards,
Zhijie Hou

Attachments:

v2-0001-Document-prepared-transaction-lock-waits-with-two.patch (text/plain, 3.9K) download From fbb58d42e03bebc13286cc5100db4280dbdf50f0 Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Mon, 16 Nov 2026 19:46:09 +0800 Subject: [PATCH v2] Document prepared transaction lock waits with two_phase subscriptions. When two_phase is enabled for a subscription, a transaction prepared by the apply worker continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED is applied. If another session requests a conflicting lock on a table modified by that prepared transaction, it waits behind the prepared transaction, and subsequent conflicting lock requests by the apply worker on the same table queue behind that session. Because the prepared transaction is not waiting on any lock, this situation is not detected as a deadlock and replication can remain stalled indefinitely. Document this caveat and how to recover from it under CREATE SUBSCRIPTION and PREPARE TRANSACTION. --- doc/src/sgml/ref/create_subscription.sgml | 21 +++++++++++++++++++++ doc/src/sgml/ref/prepare_transaction.sgml | 6 +++++- 2 files changed, 26 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 6248c228a64..c4c6f47fe2a 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -424,6 +424,27 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl are then processed immediately by the subscriber. </para> + <para> + A prepared transaction applied on the subscriber keeps its locks + until the apply worker processes the corresponding + <command>COMMIT PREPARED</command> or <command>ROLLBACK + PREPARED</command>. If another session requests a conflicting lock + on a table modified by such a transaction (for example, with + <command>LOCK</command>, <command>TRUNCATE</command>, or + <command>ALTER TABLE</command>), it has to wait, and later + conflicting lock requests on that table, including the apply + worker's own next change to it, queue behind it. The apply worker + then cannot reach the <command>COMMIT PREPARED</command> or + <command>ROLLBACK PREPARED</command>. This situation is not + detected as a deadlock, so replication can remain stalled + indefinitely. To resolve this, either cancel the waiting session, + or manually roll back the prepared transaction on the subscriber, + disable <literal>two_phase</literal> (see + <link linkend="sql-altersubscription-params-set"><literal>ALTER SUBSCRIPTION ... SET</literal></link>) + until that transaction is committed on the publisher and replicated + again, and then re-enable <literal>two_phase</literal> if required. + </para> + <para> The implementation of two-phase commit requires that replication has successfully finished the initial table synchronization diff --git a/doc/src/sgml/ref/prepare_transaction.sgml b/doc/src/sgml/ref/prepare_transaction.sgml index f4f6118ac31..a9778866496 100644 --- a/doc/src/sgml/ref/prepare_transaction.sgml +++ b/doc/src/sgml/ref/prepare_transaction.sgml @@ -132,7 +132,11 @@ PREPARE TRANSACTION <replaceable class="parameter">transaction_id</replaceable> continues to hold whatever locks it held. The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that - other databases are also prepared to commit. + other databases are also prepared to commit. A transaction prepared by a + logical replication apply worker is normally completed by the + subscription's apply worker; see + <xref linkend="sql-createsubscription-params-with-two-phase"/> for a + lock wait this can cause. </para> <para> -- 2.54.0 (Apple Git-157)

view thread (14+ messages)  latest in thread

Message-ID: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
Permalink:  ../53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com/
Also on:    postgresql.org/message-id/53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com

 ·  · 

reply

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: houzhijie22@gmail.com, bertranddrouvot.pg@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: <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
  References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <avrOjINXdJs1XiwW@bdtpg> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>

* 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