Re: Theoretical Basis for SELECT FOR UPDATE


"vc" <boston103@xxxxxxxxxxx> wrote in message
news:1128437664.015226.159350@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> 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


.

Relevant Pages

  • Re: URGENT deadlock question
    ... I have a deadlock template that I use with the following ... > SQL: Statement Completed ... > "Trace flags remain enabled in the server until disabled by executing ...
    (microsoft.public.sqlserver.server)
  • Re: OO vs. RDB challenge
    ... You've shifted thread-saftey and deadlock prevention to the client, ... SQL can surely do ... Fix yours. ... > 3) have developers that write perfect software ...
    (comp.object)
  • Re: OO vs. RDB challenge
    ... >>More broken code, Kurt? ... expression in SQL queries. ... users can sometimes deadlock each other by requiring access to ... cause the mechanism to write perfect software ...
    (comp.object)
  • Re: Analyzing Error log with Trace Flag 1204 turned on
    ... I have read the appropriate section out of Inside SQL ... up to a deadlock only from the spids involved in the deadlock? ... > resources in Inside SQL Server 2000, and in my ebook on Troubleshooting ... Are they locks currently held or ...
    (microsoft.public.sqlserver.server)
  • False SQL Deadlock Messages?
    ... We started getting large number of deadlock messages on some rather simple ... SQL receive ports. ... To complicate matters the event log message, shown below, shows the schema ...
    (microsoft.public.biztalk.general)