Re: Locking & concurrency - best practices

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