Re: Theoretical Basis for SELECT FOR UPDATE

From: vc <boston103_at_hotmail.com>
Date: 3 Oct 2005 18:38:20 -0700
Message-ID: <1128389900.766332.93980@g43g2000cwa.googlegroups.com>

Roy Hann wrote:
> "vc" <boston103_at_hotmail.com> wrote in message
> news:1128365365.731587.247830_at_f14g2000cwb.googlegroups.com...
> >
> > Roy Hann wrote:
> > > "vc" <boston103_at_hotmail.com> wrote in message
> > > news:1128362658.869251.34550_at_g49g2000cwa.googlegroups.com...
> > >
> > > > Moreover, I bet that 'update,update; ' has to use the same mechanism
> > > > behinds the scenes, namely deferrable constraints (what else ?), in
> > > > order to be possible.
> > > >
> > > > All in all, the comma separated statements do not appear to bring much
> > > > new to the table in comparison to the commit-delimited old style
> > > > transactions except for perhaps more concise notation.
> > >
> > > On the contrary. It (has the capabability to) make the entire
> transaction
> > > evident and explicit. When I confront the usual unfamiliar SQL
> application,
> > > with tiny individually meaningless updates sprayed liberally around the
> > > place, I have no confidence at all that any given commit that I might
> add
> > > leaves the database in a consistent state.
> >
> > Is not it rather a matter of style and programming discipline ? You
> > can arguably as easily produce spaghetti code with comma-separated
> > transactions as with the old style stuff. The difference, as I said,
> > is just notation, and it's not very substantial.

>

> There is a world of difference. For a start you can't create spaghetti
> code. You would surely be able to write ugly code, and foolish code, and
> wrong code. But you couldn't loop and branch and do tailspins and all the
> other nutty crap I spend my life trying to disentangle and simplify. (I'm
> mostly a performance tuner.)

>

OK, apparently you are talking about coding style issues. I am still not convinced that there is much difference between s1,s2; and s1;s2;commit, but so be it, let's skip it.

[...]

>


> > >Nor that any given rollback will

> > > roll back all and only what I expect.

> >

> > Speaking of which,  how does one rollback with new style transactions ?

> >  Or one is not supposed to unless an error occurs ?

> >

> > > Where does the transaction begin?  Am

> > > I entitled to expect to commit here?  If a serialization failure occurs,

> > > what--exactly--have I lost, and what do I have to try to redo?

> >

> > When any failure occurs, including serialization, the statement is
> > rolled back automatically.

>

> Well let me be a bit more precise. I am assuming ANSI/ISO SQL92 behaviour.
> In SQL92 a serialization failure does not merely roll back the last
> statement, it rolls back the entire transaction. Every error check needs to
> be capable of distinguishing serialization failure from a "normal" error so
> that the entire transaction can be re-tried.

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. (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.

>But where the heck does the

> transaction begin; what do I have to re-try? I might think I know which SQL
> statement initiated the transaction that just failed, but since transactions
> start implicitly I'm really just rolling the dice.

That's a strange question indeed. You designed and wrote the application so you are supposed to know how the application was structured into transactions, no ? It's like asking, how can I figure out this tree traversal algorithm if all I see is a bunch of IFs and WHILEs and some assignments.

Also, you did not answer how one is supposed to rollback explicitely in the comma delimited sequence.

[...]
> > Excuse me ? If anything, the default transaction prevents one from
> > introducing consistency problems by eliminating implicit commits (the
> > SQL Server default mode).

>

> I know nothing about SQL Server. Other products don't behave that way by
> default. It certainly doesn't sound like a good idea to me. If that is
> your point of reference then I can see how implictly starting a transaction
> would seem to be an improvement. But it's only less bad, not good.

>

> The reason implicitly started transactions cause consistency problems is
> because you have no idea what you are committing nor what you are rolling
> back.

Well, it seems as if the applications you are dealing with are just a random collection of SQL statements without any structure. It's very easy to know where the transaction begins (with the first executable statement) and where it ends (with a commit or a rollback). That's all there is to it, really simple. If you wish to indicate where in a piece of code the transaction begins, you can use a) comments b)db proprietary delimiters, 'begin transaction ' in SQL Server or 'set transaction' in Oracle. Besides, how different is the new syntax where the transaction also begins with the first executable statement: update,delete,delete,insert..; ? What's the difference from update;delete;delete,insert..;commit; ?

>You can't know (in general) where the transaction began. Ending a
> transaction is a shot in the dark. Into a crowded room.

Does not make sense. Please see above.

>

> > As to performance problems, could you
> > elaborate on how the default transaction causes those ?

>

> Most products prevent you from altering the isolation level or locking
> strategy in mid-transaction.

And for a good reason: to prevent chaos. Why would you want to change the locking stategy or isolation level in the middle of a transaction ?  Assuming you might have a valid reason for doing so, you can issue SQL statements directly to a database bypassing whatever object oriented 'adapters' you might have in between (assuming you do know what you are doing, of course).

>If a transaction starts and you don't know it,

> you will try to (for example) relax the isolation level to improve
> concurrency and it won't work.

How can you not know when a transaction starts, as the application author ? 'Relaxing' the i.l. also requires very careful design and understanding the concurrency mechanism, not just pushing buttons randomly.

> And even if it does work today, there is no
> saying some bozo won't stick a new statement in after the last commit and
> the attempt to change the isolation level will start failing. It happens
> all the time. I would be astonished to find any real world application in
> existence where stuff like that doesn't happen in fact.

No comments.

>

> 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.

>If the particular product you are

> working with enforces that through locking, and you start a transaction
> implicitly, you can easily do it accidentally. And if you don't realize
> you've started it,

If someone 'does not realize he has started it', he'd better read a manual or something, it's, like ABC of transaction management.

>you won't know to commit/rollback when you no longer
> require read-consistency.

You commit when it makes sense to commit from the transactional point of view, in order to ensure data consistency, not to improve performance. Admittedly, people do that (release locks by committing prematurely), especially with locking databases, but it does not make it right, does it ?

[...]
> > > Even if a DBMS allowed complex updates to be
> > > expressed as atomic operations, it would only solve my problem if it
> > > *insisted* they be atomic.
> >
> > Please clarify.

>

> If an atomic update has to be written as an atomic statement, then it would
> be possible to go further and insist that a statement *is* a transaction.

But in every database on the earth, a statement (update/delete/insert) *is* already atomic and can be treated as a transaction (when run at an appropriate i.l.). Am I missing something ?

> Once you insist on that, then all the problems about where transactions
> begin and end go away.

Well, I do not think there is any problem with that now. A "for example" would be nice.

>
> Roy
Received on Mon Oct 03 2005 - 20:38:20 CDT