Re: Theoretical Basis for SELECT FOR UPDATE

Roy Hann wrote:
> "vc" <boston103_at_hotmail.com> wrote in message
[...]
> > You may have a point about the serialization failure, but popular
> > databases like SQL Server/Sybase or Oracle either do not implement
> > serialization failure, or rollback only the statement that caused the
> > error.
>
> ??Huh?!
>
> > (SQL Server simply dead-locks when a serialization failure
> > happens so probably you can say it does implement it). The user can of
> > course detect the error and rollback the entire transaction.
>
> This is wildly wrong. Deadlock *is* a serialization failure.

I apologize for the sloppy wording in my previous message.

I did not claim that the deadlock is not a serialization failure. It surely is. What I should have said is that there is no "serialization failure" error message (as required by the SQL-92 standard) produced by say Sybase. But you are right of course, the message is irrelevant, the substance is.

Regarding the question whether the entire transaction is aborted or only the statement that cause a deadlock, Sybase and SQL Server rollback the entire transaction automatically and the user has no control over it.

Oracle, on the other hand, rolls back only the current statement and it's up to the user to decide whether or not to roll back the whole transaction. I should have stated this more clearly.

>There is no
> valid response to it other than a rollback. The system doesn't give the
> programmer (or "user" if you will) a choice about rolling back.

It does in Oracle, and it does not in SQL Server. Whether having this kind of control is good or not is a separate subject.

> It just
> happens, and you have to know it happened and know what to do about it. And
> I say I *can't* know what to do about it.

See above.

[...]
> > Also, you did not answer how one is supposed to rollback explicitly
> > in the comma delimited sequence.
>
> I can't think why I would ever want to. I want to roll back only if there
> is a constraint violation or there is a serialization failure, and that is
> all. In both cases the rollback would be implicit, as it should be.

You may have a point here.

[...]
> > > A bigger problem however is that by default you are entitled to
> > > read-consistency within a transaction.
> >
> > Apparently, you've meant the READ COMMITTED i.l., the default i.l.
>
> I am entitled to read-consistency with an isolation level of REPEATABLE READ
> and SERIALIZABLE.

What's that supposed to mean ? As I said before, by default, the transaction starts in RC not in RR or SERIALIZABLE, unless the programmer set the level explicitely. Are you saying that the implicit transaction starts with the i.l. other than RC ?

Besides, what do you mean by "read consistency" ? The RR il does not prevent all the anomalies.

[...] Received on Tue Oct 04 2005 - 12:46:46 CEST