| From: | Zhijie Hou <houzhijie22(at)gmail(dot)com> |
|---|---|
| To: | Bertrand Drouvot <bertranddrouvot(dot)pg(at)gmail(dot)com> |
| Cc: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "amit(dot)kapila16(at)gmail(dot)com" <amit(dot)kapila16(at)gmail(dot)com>, Nisha Moond <nisha(dot)moond412(at)gmail(dot)com>, Masahiko Sawada <sawada(dot)mshk(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-16 12:20:45 |
| Message-ID: | 53ca9f95-aacb-4e9e-9021-e4abe01e000e@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
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
| Attachment | Content-Type | Size |
|---|---|---|
| v2-0001-Document-prepared-transaction-lock-waits-with-two.patch | text/plain | 3.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Markos Fountoulakis | 2026-11-16 12:35:18 | [PATCH] basebackup: use sequential access advice for full-file reads |
| Previous Message | Joao Rito | 2026-11-16 11:39:23 | Re: transaction_timeout is not enforced during ClientWrite |