From: | Joe <symphony(dot)red+pg(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | SELECT FOR UPDATE on rows that don't exist |
Date: | 2022-03-23 19:52:05 |
Message-ID: | CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-03-23 22:18:56 | Re: SELECT FOR UPDATE on rows that don't exist |
Previous Message | Jean Baro | 2022-03-17 01:21:18 | Re: Why aren't people talking about OrioleDB (new Storage Engine for PG)? |