SELECT FOR UPDATE on rows that don't exist

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Joe <[email protected]>
To: [email protected]
Subject: SELECT FOR UPDATE on rows that don't exist
Date: Wed, 23 Mar 2022 12:52:05 -0700
Message-ID: <CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.gmail.com> (raw)

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

view thread (3+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: SELECT FOR UPDATE on rows that don't exist
  In-Reply-To: <CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox