Document AccessExclusive lock behaviour on standbys

Started by Anthonin Bonnefoy6 months ago1 messages

anthonin.bonnefoy@datadoghq.com

6 months ago

1 attachment(s)

Hi,

When a table is locked with AccessExclusive locks, the table is also
locked on standbys as the lock is propagated through the WAL and
replayed by the recovery process. This seems to surprise users as they
assume the lock is restricted to the primary and don't expect the
table to be unreadable on standbys.

I usually point to some comments in lock.c to explain this behaviour
but showing C code is not the most user friendly. There are some
mentions about this scattered in the hot-standby documentation but
users are more likely going to look at the explicit locking
documentation.

The provided patch adds more details on this in the table-level locks
documentation, making it explicit that the tables will be locked on
standbys and for how long.

Regards,
Anthonin Bonnefoy

Attachments:

v01-0001-Document-AccessExclusive-lock-behaviour-on-stand.patchapplication/octet-stream; name=v01-0001-Document-AccessExclusive-lock-behaviour-on-stand.patchDownload

From f2b044f16116ed27e8f8a08d6f605a07a457759f Mon Sep 17 00:00:00 2001
From: Anthonin Bonnefoy <anthonin.bonnefoy@datadoghq.com>
Date: Tue, 15 Jul 2025 09:00:40 +0200
Subject: Document AccessExclusive lock behaviour on standbys

Access exclusive lock is the only lock propagated through the WAL and
replayed on standbys. This can lead to confusing situations as some
users assume the lock is restricted on the primary and don't expect the
locked table to be unreadable on the standbys.

This patch adds more details on the expected behaviour of Access
Exclusive lock in the table-level locks documentation.
---
 doc/src/sgml/mvcc.sgml | 8 ++++++++
 1 file changed, 8 insertions(+)

diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 049ee75a4ba..60ccb0a2774 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1101,6 +1101,14 @@ ERROR:  could not serialize access due to read/write dependencies among transact
        <command>SELECT</command> (without <option>FOR UPDATE/SHARE</option>)
        statement.
       </para>
+      <para>
+       <literal>ACCESS EXCLUSIVE</literal> is the only lock recorded in the WAL.
+       When replayed on standbys, the table will be locked by the recovery process.
+       Thus, tables locked with <literal>ACCESS EXCLUSIVE</literal> won't be readable
+       on standbys. This lasts until the blocking transaction ends (i.e., the
+       transaction's <command>COMMIT</command> or <command>ROLLBACK</command>
+       is replayed on the standby).
+      </para>
      </tip>
 
    <para>
-- 
2.50.1