Re: Theoretical Basis for SELECT FOR UPDATE

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.

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

>
> Notice that I only say comma separated updates have the capability to do
> this.

I can concede that the comma separated statement, in some cases (although I find it hard to imagine those cases), can prevent errors caused by sloppy coding practice.

>As a practitioner I find SQL's eagerness to start a transaction
> implicitly extremely irritating and a rich source of performance and
> consistency problems.

Excuse me ? If anything, the default transaction prevents one from introducing consistency problems by eliminating implicit commits (the SQL Server default mode). As to performance problems, could you elaborate on how the default transaction causes those ?

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

>
> Roy
Received on Mon Oct 03 2005 - 20:49:25 CEST