> 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.