On Jan 14, 2008 4:31 PM, Adam Rich <adam.r@xxxxxxxxxxxxxxxxx> wrote: > > You should be able to do "select for update" on both parent and child > > records and get the effect you desire. > > > > I don't think that will work. Let me demonstrate: > (this is simplified, but sufficient to make my point) > > -- Connection 1 -- > begin trans; > > select * from parent_tbl > where id=1 for update; > > select count(*) into myvar > from data_tbl where fk=1; > > -- connection 2 runs here (see below) -- > > if (myvar < 3) then > update parent_tbl > set status=1 where id=1; > else > update parent_tbl > set status=2 where id=1; > end if; > > commit; > > -- Connection 2 -- > > begin trans; > insert into data_tbl (fk, data) values (1, 'foo'); > insert into data_tbl (fk, data) values (1, 'bar'); > insert into data_tbl (fk, data) values (1, 'baz'); > commit; > > -- End example -- > > In what way would you use "FOR UPDATE" on data_tbl > to ensure parent_tbl doesn't end up with the wrong > status ? AFAIK, "FOR UPDATE" locks only the rows > returned, and does nothing to prevent new inserts. > using a "serialized" isolation doesn't seem appropriate > either. As far as I can tell, the only options are > locking the entire data_tbl at the start of both > connections (which unfortunately also blocks all > other transactions with id/fk != 1), or using > advisory locks. Advisory locks would work here (better that than table lock), but I don't think that's the right approach. Transaction 2 should simply do a select * from parent_tbl where id=1 for update; at the start of the transaction. The idea here is that a property of 'parent_tbl' is the count of _all_ it's data elements. Therefore, locking should be consistently applied at the parent level, so you serialize access to a particular parent. merlin ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
