MIME-Version: 1.0
From: Joe <symphony.red+pg@gmail.com>
Date: Wed, 23 Mar 2022 12:52:05 -0700
Message-ID: 
 <CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.gmail.com>
Subject: SELECT FOR UPDATE on rows that don't exist
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000c7a4205dae81169"
Archived-At: 
 <https://www.postgresql.org/message-id/CAD9Bb3s3MN-iebRetMhNGs%3DsjnDwK4jw4BYG8%2BW9LeBbhi6HyA%40mail.gmail.com>
Precedence: bulk

--0000000000000c7a4205dae81169
Content-Type: text/plain; charset="UTF-8"

Hi,
I'd like to implement a distributed mutex, and was thinking of this:
CREATE TABLE locks (section VARCHAR PRIMARY KEY, holder VARCHAR);

And then do:
BEGIN;
SELECT * FROM locks WHERE section = $1 FOR UPDATE;
INSERT INTO locks (section, holder) VALUES ($1, $2);  # *1

... critical section ...

DELETE FROM locks WHERE section = $1 and holder = $2; #*2
COMMIT;

A few questions:
1) What are the semantics of SELECT FOR UPDATE when the row doesn't exist
yet?

Without #*1, a simple experiment shows that two processes can be in the
critical section at the same time. Add #*1 seems to achieve the desired
behavior, but is it really? I didn't find much on the web (it looks like
MySQL locks the index meaning the INSERT wouldn't be necessary). If
Postgresql was also locking the index, the INSERT would not add anything,
but the experiment without the INSERT would have worked. If it's the row
being locked, since the row doesn't exist outside the transaction, the
second process shouldn't be able to see it and wouldn't block waiting for
the first transaction.

2) The DELETE @ #2 is so that the row is never present when not executing
in the critical section mainly so that #1 can be a simple insert rather
than an upsert. Is there a more standard pattern for this?

3)  Using the DB as a distributed mutex seems like a common application but
nothing came up in various DB and PostgreSQL books I consulted or on the
web. Is this a bad idea, or are there gotchas I'm missing?

Thanks!
Joe

--0000000000000c7a4205dae81169
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div>I&#39;d like to implement a distributed=
 mutex, and was thinking of this:<br>CREATE TABLE locks (section VARCHAR PR=
IMARY KEY, holder VARCHAR);<br><br>And then do:<br>BEGIN;<br>SELECT * FROM =
locks WHERE section =3D $1 FOR UPDATE;<br>INSERT INTO locks (section, holde=
r) VALUES ($1, $2); =C2=A0# *1</div><div><br></div><div>... critical sectio=
n ...</div><div><br></div><div>DELETE FROM locks WHERE section =3D $1 and h=
older =3D $2; #*2<br></div><div>COMMIT;</div><div>=C2=A0</div><div>A few qu=
estions:</div><div>1) What are the semantics of SELECT FOR UPDATE when the =
row doesn&#39;t exist yet?</div><div><br></div><div>Without #*1, a simple e=
xperiment shows that two processes can be in the critical section at the sa=
me time. Add #*1 seems to achieve the desired behavior, but is it really? I=
 didn&#39;t find much on the web (it looks like MySQL locks the index meani=
ng the INSERT wouldn&#39;t be necessary). If Postgresql was also locking th=
e index, the INSERT would not add anything, but the experiment without the =
INSERT would have worked. If it&#39;s the row being locked, since the row d=
oesn&#39;t exist outside the transaction, the second process shouldn&#39;t =
be able to see it and wouldn&#39;t block waiting for the first transaction.=
</div><div><br></div><div>2) The DELETE @ #2 is so that the row is never pr=
esent when not executing in the critical section mainly so that #1 can be a=
 simple insert rather than an upsert. Is there a more standard pattern for =
this?</div><div><br></div><div>3)=C2=A0 Using the DB as a distributed mutex=
 seems like a common application but nothing came up in various DB and Post=
greSQL books I consulted or on the web. Is this a bad idea, or are there go=
tchas I&#39;m missing?</div><div><br></div><div>Thanks!</div><div>Joe <br><=
/div></div>

--0000000000000c7a4205dae81169--