Re: Locking & concurrency - best practices

On Jan 14, 2008 4:31 PM, Adam Rich <adam(dot)r(at)indigodynamic(dot)com> 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