public inbox for [email protected]
help / color / mirror / Atom feed(docs): add missing info about ShareLocks
4+ messages / 2 participants
[nested] [flat]
* (docs): add missing info about ShareLocks
@ 2025-11-22 08:07 Alpha Shuro <[email protected]>
2025-11-22 12:07 ` Re: (docs): add missing info about ShareLocks Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Alpha Shuro @ 2025-11-22 08:07 UTC (permalink / raw)
To: [email protected]
Hello
I had a production incident a few weeks ago while using deferred indexes, where the Postgres docs lead me down the wrong path of investigation due to missing details. Specifically, the docs implied that a `ShareLock` was only acquired when creating indexes, but only after looking at the code did I learn that this lock is also acquired when transactions are waiting for other transactions to complete. I think this would be helpful to someone who might find themselves on the same path in the future, and as I understand it this mailing list is the way to submit patches to the docs?
Attachments:
[application/octet-stream] 0001-document-ShareLock-behaviour-when-using-a-deferred-u.patch (1.2K, 2-0001-document-ShareLock-behaviour-when-using-a-deferred-u.patch)
download | inline diff:
From 80741b41d6ff09fdd395775f87b99098b589c16e Mon Sep 17 00:00:00 2001
From: Alpha Shuro <[email protected]>
Date: Wed, 15 Oct 2025 17:55:05 +0200
Subject: [PATCH] document ShareLock behaviour when using a deferred unique
index
The logs that were printed by Postgres
when enforcing deferred unique indexes were misleading.
This change should make it easier to understand or investigate
when users see the `waits for ShareLock` log entry
---
doc/src/sgml/mvcc.sgml | 5 +++++
1 file changed, 5 insertions(+)
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 049ee75a4ba..4e36c59776a 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -1016,6 +1016,11 @@ ERROR: could not serialize access due to read/write dependencies among transact
<para>
Acquired by <command>CREATE INDEX</command>
(without <option>CONCURRENTLY</option>).
+
+ It is also acquired when enforcing a DEFERRED UNIQUE INDEX:
+ If a transaction detects another transaction that might cause
+ a potential conflict, it waits for the other transaction to complete,
+ by acquiring a ShareLock on the other transaction's transaction id.
</para>
</listitem>
</varlistentry>
--
2.51.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (docs): add missing info about ShareLocks
2025-11-22 08:07 (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
@ 2025-11-22 12:07 ` Laurenz Albe <[email protected]>
2025-11-27 11:33 ` Re: (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Laurenz Albe @ 2025-11-22 12:07 UTC (permalink / raw)
To: Alpha Shuro <[email protected]>; [email protected]
On Sat, 2025-11-22 at 09:07 +0100, Alpha Shuro wrote:
> I had a production incident a few weeks ago while using deferred indexes, where
> the Postgres docs lead me down the wrong path of investigation due to missing details.
> Specifically, the docs implied that a `ShareLock` was only acquired when creating
> indexes, but only after looking at the code did I learn that this lock is also
> acquired when transactions are waiting for other transactions to complete.
> I think this would be helpful to someone who might find themselves on the same path
> in the future, and as I understand it this mailing list is the way to submit patches
> to the docs?
No, that is wrong. This section is about table locks, and a lock on a transaction ID
should, if anywhere, be documented elsewhere. Actually, there is already something
about transaction ID locks in https://www.postgresql.org/docs/current/xact-locking.html
Perhaps you could improve that short documentation?
Yours,
Laurenz Albe
PS: There are also SHARE locks on rows.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (docs): add missing info about ShareLocks
2025-11-22 08:07 (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
2025-11-22 12:07 ` Re: (docs): add missing info about ShareLocks Laurenz Albe <[email protected]>
@ 2025-11-27 11:33 ` Alpha Shuro <[email protected]>
2025-11-27 13:49 ` Re: (docs): add missing info about ShareLocks Laurenz Albe <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Alpha Shuro @ 2025-11-27 11:33 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: [email protected]
Ah thanks for pointing this out, I've moved it to the xact-locking page instead
> Den 22. nov. 2025 kl. 13.07 skrev Laurenz Albe <[email protected]>:
>
> On Sat, 2025-11-22 at 09:07 +0100, Alpha Shuro wrote:
>> I had a production incident a few weeks ago while using deferred indexes, where
>> the Postgres docs lead me down the wrong path of investigation due to missing details.
>> Specifically, the docs implied that a `ShareLock` was only acquired when creating
>> indexes, but only after looking at the code did I learn that this lock is also
>> acquired when transactions are waiting for other transactions to complete.
>> I think this would be helpful to someone who might find themselves on the same path
>> in the future, and as I understand it this mailing list is the way to submit patches
>> to the docs?
>
> No, that is wrong. This section is about table locks, and a lock on a transaction ID
> should, if anywhere, be documented elsewhere. Actually, there is already something
> about transaction ID locks in https://www.postgresql.org/docs/current/xact-locking.html
>
> Perhaps you could improve that short documentation?
>
> Yours,
> Laurenz Albe
>
> PS: There are also SHARE locks on rows.
Attachments:
[application/octet-stream] document-ShareLock-when-using-deferred-unique.patch (661B, 2-document-ShareLock-when-using-deferred-unique.patch)
download | inline diff:
diff --git a/doc/src/sgml/xact.sgml b/doc/src/sgml/xact.sgml
index 3aa7ee1383e..d753869cc88 100644
--- a/doc/src/sgml/xact.sgml
+++ b/doc/src/sgml/xact.sgml
@@ -102,6 +102,13 @@
of multixact IDs (<literal>mxid</literal>; see <xref
linkend="vacuum-for-multixact-wraparound"/>).
</para>
+
+ <para>
+ If there is a DEFERRED UNIQUE constraint on a row inserted in a transaction,
+ and there are multiple concurrent transactions inserting the same unique key,
+ the earlier transactions will wait for later transactions by
+ acquiring a <structfield>ShareLock</structfield> on the transaction ID.
+ </para>
</sect1>
<sect1 id="subxacts">
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: (docs): add missing info about ShareLocks
2025-11-22 08:07 (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
2025-11-22 12:07 ` Re: (docs): add missing info about ShareLocks Laurenz Albe <[email protected]>
2025-11-27 11:33 ` Re: (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
@ 2025-11-27 13:49 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2025-11-27 13:49 UTC (permalink / raw)
To: Alpha Shuro <[email protected]>; +Cc: [email protected]
On Thu, 2025-11-27 at 12:33 +0100, Alpha Shuro wrote:
> Ah thanks for pointing this out, I've moved it to the xact-locking page instead
I think that is too specific. You can see a session waiting for a SHARE
lock on a transaction whenever it is waiting for a row lock, since row
locks are not permanently stored in the shared memory lock table.
Rather than adding a paragraph about the specific incident that happened
to you, you should describe the general mechanism.
Some reading material:
https://www.cybertec-postgresql.com/en/row-locks-in-postgresql/
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2025-11-27 13:49 UTC | newest]
Thread overview: 4+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-22 08:07 (docs): add missing info about ShareLocks Alpha Shuro <[email protected]>
2025-11-22 12:07 ` Laurenz Albe <[email protected]>
2025-11-27 11:33 ` Alpha Shuro <[email protected]>
2025-11-27 13:49 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox