Re: Theoretical Basis for SELECT FOR UPDATE

From: Roy Hann <specially_at_processed.almost.meat>
Date: Tue, 4 Oct 2005 17:11:52 +0100
Message-ID: <H-mdnXP0aYd8NN_enZ2dnUVZ8qWdnZ2d_at_pipex.net>

"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)

> > Deadlocks can always occur and the
> > application always needs to be ready to handle them.
>
> No argument about that.
>
> > > What's unreasonable
> > > about restarting a transaction that failed due to a deadlock ?
> >
> > I'm not getting through at all here.
> >
> > Please give me the benefit of the doubt and look closely at what I am
> > asking. The problem is not that I don't understand what SQL wants me to
do.
> > The problem is that (in general) I just can't restart the transaction
> > because (in general) I have no idea where it began and I have no idea
what
> > it includes.
>
> I find it hard to accept the argument that you, as the application
> author, have no clue where you transactions start and end, and what
> statements it consists of, that you do not know how to structure SQL
> statements into stored procedures or some Java classes representing
> individual transactions.

It's this "you" business that's the problem. There is no "you". There is a large, non-cooperating team of developers spread out across the decades. I have no idea what they have done and where they've done it, and I consume most of my working day trying to find out, and I am still not certain.

That's what happens in the real world when you're given nothing but half-baked tools that assume everyone is going to be super-competent and never touch the exposed wires just because you said not to.

> > It began implicitly sometime in the past and it has done who
> > knows what since then. I might hope I can guess when it (should have)
> > started, but I can't *really* know, except in special cases, because
that is
> > how SQL is designed.
>
> See above.

I've seen above, and it give me the heebies every time.

Roy Received on Tue Oct 04 2005 - 18:11:52 CEST