From: Zhijie Hou <houzhijie22@gmail.com>
To: Hayato Kuroda (Fujitsu) <kuroda.hayato@fujitsu.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: Tue, 17 Nov 2026 14:58:58 +0800
Message-ID: <51a90195-3a5c-429b-a978-e639df8bc225@gmail.com> (raw)
In-Reply-To: <OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
<avrOjINXdJs1XiwW@bdtpg>
<53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com>
<OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com>
Hi,
On Tue, Nov17, 2026 at 01:25 PM "Hayato Kuroda (Fujitsu)" wrote:
>
> Thanks for posting the patch. I feel the logging should be improved,
> but it might be out-of-scope. Here I focused on the doc patch.
Thanks for the comments.
>
> ```
> + 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.
> ```
>
> Isn't it better to disable the subscription before rolling back? If
> the publisher runs COMMIT/ROLLBACK PREPARED soon, the resumed apply
> worker raises "prepared transaction ... does not exist" error.
Although I think the ERROR won't affect the recoevry steps as the
transaction can be replicated anyway after disabling the two_phase, I
agree that the suggested order looks cleaner, so changed.
Here is the V3 patches for all branches.
--
Best Regards,
Zhijie Hou
Attachments:download all (2 files, .tar.gz)v3-0001-Document-prepared-transaction-lock-waits-with-two-HEAD-PG18.patch (text/plain, 4.0K) download
From d1610a4d7be6cf1f6c8d9ee83b99c80855a19fe6 Mon Sep 17 00:00:00 2001
From: Zhijie Hou <houzhijie22@gmail.com>
Date: Tue, 17 Nov 2026 14:52:29 +0800
Subject: [PATCH v3] 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 | 23 +++++++++++++++++++++++
doc/src/sgml/ref/prepare_transaction.sgml | 6 +++++-
2 files changed, 28 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgmlindex 6248c228a64..1e8bb843410 100644--- a/doc/src/sgml/ref/create_subscription.sgml+++ b/doc/src/sgml/ref/create_subscription.sgml@@ -424,6 +424,29 @@ 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, cancel the waiting session.+ Alternatively, disable the subscription, 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>),+ and re-enable the subscription. Once the transaction is finished+ on the publisher and replicated again, <literal>two_phase</literal>+ can be re-enabled 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.sgmlindex 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)
v3-0001-Document-prepared-transaction-lock-waits-with-two-PG15-PG17.patch (text/plain, 3.5K) download
From e310e98909e4ab8fac999793865d73d9d58831ae Mon Sep 17 00:00:00 2001
From: Zhijie Hou <houzhijie22@gmail.com>
Date: Tue, 17 Nov 2026 13:48:18 +0800
Subject: [PATCH v3] 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 | 16 ++++++++++++++++
doc/src/sgml/ref/prepare_transaction.sgml | 6 +++++-
2 files changed, 21 insertions(+), 1 deletion(-)
diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgmlindex c9c8dd440dc..3d18ad60458 100644--- a/doc/src/sgml/ref/create_subscription.sgml+++ b/doc/src/sgml/ref/create_subscription.sgml@@ -343,6 +343,22 @@ 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 until the waiting session is canceled.+ </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.sgmlindex f4f6118ac31..c3848fbc72c 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 the <literal>two_phase</literal>+ parameter of <xref linkend="sql-createsubscription"/> for a lock wait+ this can cause.
</para>
<para>
--
2.54.0 (Apple Git-157)
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, kuroda.hayato@fujitsu.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: <51a90195-3a5c-429b-a978-e639df8bc225@gmail.com>
References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> <avrOjINXdJs1XiwW@bdtpg> <53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com> <OS7PR01MB18317C2FC8C8A44608F846474F57B2@OS7PR01MB18317.jpnprd01.prod.outlook.com> <51a90195-3a5c-429b-a978-e639df8bc225@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