Re: "Transactions are bad, real bad" - discuss

"--CELKO--" <71062.1056_at_compuserve.com> wrote in message news:c0d87ec0.0304301541.3382b4c4_at_posting.google.com... [snip]
> Some vendors allow you to use an alias on the table in the UPDATE
> clause, but that is proprietary not Standard and has the same
> conceptual problem.
>
> I am happy with the "BEGIN ATOMIC. END;" syntax in the SQL/PSM
> Standard.

Joe. Do you know if the SQL standard mandates when constraints get checked within a BEGIN ATOMIC? I know that in DB2 they are checked for each statement within the BEGIN ATOMIC and cannot (as yet) be deferred to the completion of the BEGIN ATOMIC.

All a bit of a pain if you are trying to insert into two tables that have a One to One-or-Many relationship.

Also, any mandate on the setting of CURRENT TIMESTAMP? DB2 does sensibly keep it the same in a BEGIN ATOMIC.

> I can add exception handlers and get pretty good control
> over the unit of work.

In DB2 it depends if we are talking dynamic BEGIN ATOMICs or BEGIN ATOMICs in stored procedures. In the former I can't, for example, optionally do a ROLLBACK or COMMIT within the BEGIN ATOMIC..

> In theory, the compiler can optimize over
> multiple statements within such a block.

Agreed. Although I might suggest that a complier would find it easier to optimise nested statements rather than blocked statements. Nonetheless, in DB2

    "Dynamic compound statements are compiled by DB2 as one single statement. This statement is effective for short scripts involving little control flow logic but significant dataflow. For larger constructs with requirements for nested complex control flow or condition handling, a better choice is to use SQL procedures. "

OK, so it does not mean that it optimises over the statements (I don't know either way), but it's a first step.

> Of course, we still have SQL
> compiles that don't optimize over SELECT clauses in a UNION [ALL]
> statement ...

Outch!

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Thu May 01 2003 - 15:40:59 CEST