Am 13.09.2013 um 18:47 schrieb David Noel <david.i.noel@gmail.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
criteriais 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
