Re: Theoretical Basis for SELECT FOR UPDATE

Roy Hann wrote:
> "vc" <boston103_at_hotmail.com> wrote in message
> news:1128437664.015226.159350_at_f14g2000cwb.googlegroups.com...
> >
> > Roy Hann wrote:
> > [...]
> >
> > > Even the most
> > > crystal clear, linear code can deadlock.
> >
> > Please provide an example.
>
> I can't really believe I have to do this, but here goes (assuming
> serializable isolation):
>
> session 1 session 2
> ========= =========
> select x
> from foo
> where pk=1
> select x
> from foo
> where pk=1
> update foo
> set x=x+1
> where pk=1
> (waits)
> update foo
> set x=x+1
> where pk=1
> (deadlocks)
>

The transactions deadlock because there is a *bug*: the programmer neglected to eliminate non-serializable transaction histories. Any transaction history whose serializability graph has cycles will deadlock. The way to avoid the deadlock is naturally to eliminate non-serializable histories. In SQL Server, one would use the UPDLOCK hint:

begin tran
select x
from foo(UPDLOCK)
where pk=1;

update foo
set x=x+1
where pk=1;
commit;

In Oracle, SELECT FOR UPDATE is your friend.

[...skipped ...] Received on Tue Oct 04 2005 - 21:17:13 CEST