From: | Ralf Schuchardt <rasc(at)gmx(dot)de> |
---|---|
To: | David(dot)I(dot)Noel(at)gmail(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Small PosgreSQL locking function request - with bounty |
Date: | 2013-09-14 09:40:40 |
Message-ID: | 3CEA077E-FB3E-4D4F-9FAF-DB45FA8E32F2@gmx.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Raymond O'Donnell | 2013-09-14 11:52:03 | Re: Hot standby & SR - log shipping required? |
Previous Message | Patrick Dung | 2013-09-14 07:59:11 | Re: Major upgrade of PostgreSQL and MySQL |