Re: Small PosgreSQL locking function request - with bounty

Am 13.09.2013 um 18:47 schrieb David Noel <david(dot)i(dot)noel(at)gmail(dot)com>:

>> ...have you used the "for update" clause in your select statements?
>
> Hi Ralf, thanks for the reply. I was unaware of the "for update"
> construct. Thank you!
>
>> My understanding is, that "for update" does what you need.
>
[...]
> My question is: according to the documentation, it seems
> that SELECT FOR UPDATE may still run into the same concurrency issue.
> If two concurrent transactions select the same row, the first will be
> given the lock. The second transaction will encounter the lock and be
> forced to wait. The update from the first transaction will occur, the
> lock will be released, and control will be passed to the second
> transaction. According to the documentation, the row will already have
> been selected, so the transaction, it seems, will continue processing
> the row as if it were marked "Inactive". In essence, the way I read
> it, it won't care that the row had been updated by the first
> transaction, and so essentially I will be running into the same
> problem I'm facing now. Am I reading this correctly?

No, I think it will work. The part "and will then lock and return the updated row" does not mean, that the select criteria is not rechecked.

Let's try it. Two clients:

-- > Client 1

locktest=# create table locktest (id serial primary key, state int2);
CREATE TABLE
locktest=# insert into locktest values (1, 0), (2, 0);
INSERT 0 2
locktest=# select * from locktest;
id | state
----+-------
1 | 0
2 | 0
(2 rows)

locktest=# begin;
BEGIN
locktest=# select * from locktest where state = 0 limit 1 for update;
id | state
----+-------
1 | 0
(1 row)

-------------- WAIT HERE IN CLIENT 1

-- > Client 2

locktest=# begin;
BEGIN
locktest=# select * from locktest where state = 0 limit 1 for update;

------------- Client 2 waits for a lock

-- > Client 1

locktest=# update locktest set state = 1 where id = 1;
UPDATE 1
locktest=# commit;
COMMIT

-- > Client 2

id | state
----+-------
2 | 0
(1 row)

[...]

You only have to take care, that the UPDATE really only updates the selected row.
An URL as a primary key might work, but might not be the best choice.

Ralf