Am 13.09.2013 um 18:47 schrieb David Noel <david.i.noel@xxxxxxxxx>: >> ...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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
