Re: Theoretical Basis for SELECT FOR UPDATE

From: Roy Hann <specially_at_processed.almost.meat>
Date: Mon, 3 Oct 2005 19:29:58 +0100
Message-ID: <j7WdneR1LplO5dzenZ2dnUVZ8qadnZ2d_at_pipex.net>


"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. Nor that any given rollback will roll back all and only what I expect. 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?

Notice that I only say comma separated updates have the capability to do this. As a practitioner I find SQL's eagerness to start a transaction implicitly extremely irritating and a rich source of performance and consistency problems. 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.

Roy Received on Mon Oct 03 2005 - 20:29:58 CEST

Original text of this message