Re: Locking & concurrency - best practices

On Jan 14, 2008 3:31 PM, Adam Rich <adam.r@indigodynamic.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;

You're right. with count(*) involved, you won't be able to get an accurate view.

Generally speaking, when you've got to count rows like that, locking
the table is the only thing that works.

That or changing how you process the data.