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
