pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Zhijie Hou <houzhijie22@gmail.com>
To: 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>
Cc: Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
Subject: Document prepared transaction lock waits with two_phase subscriptions
Date: Fri, 13 Nov 2026 11:58:10 +0800
Message-ID: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com> (raw)

Hi,

While reviewing the prepared-transaction locking hazard involving the 
conflict log table [1], we noticed that the issue is not specific to the 
conflict log table and also affects regular user tables.

When two_phase is enabled, a prepared transaction on the publisher is 
also prepared on the subscriber, and like any prepared transaction, it 
continues to hold its locks until COMMIT PREPARED or ROLLBACK PREPARED. 
On the subscriber, if a replicated prepared transaction holds a lock on 
a user table and a local session then tries to acquire a conflicting 
lock on the same table (e.g., via TRUNCATE, VACUUM FULL, or ALTER 
TABLE), the local session will block.

In this state, if there are further replicated changes on the same 
table, the apply worker will also be blocked when acquiring the table 
lock to apply those changes. Because the apply worker cannot advance to 
subsequent transactions, even if COMMIT PREPARED or ROLLBACK PREPARED is 
issued on the publisher, the apply worker will not be able to reach and 
apply it. As a result, replication stalls because the sessions wait for 
each other, and the deadlock detector does not detect this wait cycle 
since the prepared transaction is not waiting on any lock. This is 
similar to the existing caution in the prepared transaction documentation:

"""
   It is unwise to leave transactions in the prepared state for a long 
time ... Keep in mind also that the transaction 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.
"""

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. Please see the attached 
patch for the documentation changes.

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.

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. For example, suppose a prepared transaction holds a 
lock on table t1, a local session acquires AccessExclusiveLock on table 
t2 and then blocks trying to TRUNCATE t1, and the apply worker next 
tries to apply a change to t2. Because the apply worker is waiting on t2 
while the prepared transaction holds the lock on t1, inspecting only 
t2's lock would miss the cycle.

Given these trade-offs, we prefer to document this locking hazard and 
the workarounds for now.

[1] 
https://www.postgresql.org/message-id/CAJpy0uAWb7hs0271M_NxhOpetAfFcQidrUce5xSOL8jU3Hx1iQ%40mail.gma...

-- 
Best Regards,
Zhijie Hou

Attachments:

v1-0001-Document-prepared-transaction-lock-waits-with-two.patch (text/plain, 3.9K) download From a52d72efa325edceb6928e5e84a404d51a4e32eb Mon Sep 17 00:00:00 2001 From: Zhijie Hou <houzhijie22@gmail.com> Date: Fri, 13 Nov 2026 09:58:46 +0800 Subject: [PATCH v1] 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 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 wait cycle is not detected as a deadlock and replication stalls. 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 | 5 ++++- 2 files changed, 25 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 6248c228a64..b2e6b8d9360 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 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> that would release the lock, and the sessions + wait for each other. This is not detected as a deadlock, so + replication stalls. 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..22f4feca64d 100644 --- a/doc/src/sgml/ref/prepare_transaction.sgml +++ b/doc/src/sgml/ref/prepare_transaction.sgml @@ -132,7 +132,10 @@ 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 can be completed only by that 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: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
Permalink:  ../72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com/
Also on:    postgresql.org/message-id/72c9b956-96c3-40a5-99bb-d9b7338c0bab@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, amit.kapila16@gmail.com, nisha.moond412@gmail.com, sawada.mshk@gmail.com, shveta.malik@gmail.com, dilipbalaut@gmail.com, bertranddrouvot.pg@gmail.com
  Subject: Re: Document prepared transaction lock waits with two_phase subscriptions
  In-Reply-To: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@gmail.com>
  References: <72c9b956-96c3-40a5-99bb-d9b7338c0bab@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