Re: DDL

From: Howard J. Rogers <hjr_at_dizwell.com>
Date: Fri, 19 Nov 2004 07:56:44 +1100
Message-ID: <419d0c8e$0$2681$afc38c87@news.optusnet.com.au>

Richard Foote wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:419c730a$0$25115$afc38c87_at_news.optusnet.com.au...
>

>>DA Morgan wrote:
>>
>>>Howard J. Rogers wrote:
>>>
>>>
>>>>Rahul Jain wrote:
>>>>
>>>>
>>>>>Why is DDL required to be the last statement in a transaction?
>>>>
>>>>
>>>>
>>>>Because there is an implicit commit before and after each and every DDL 
>>>>statement.
>>>>
>>>>Regards
>>>>HJR
>>>
>>>
>>>You are absolutely correct Howard ... but I fail to see what your answer
>>>has to do with the question. But then its late and I'm tired.
>>
>>
>>Well, I'm no developer, but I was always taught that a transaction starts 
>>with some piece of DML and ends when you say 'commit' (or rollback, of 
>>course).
>>
>>Insert money into new bank account;
>>Delete money from old account;
>>Commit both changes;
>>
>>If DDL implies a commit, surely that therefore must count as the 
>>termination of a transaction? And therefore by definition, the DDL 
>>statement becomes the last statement in a transaction.
>>
>>Insert money into new bank account;
>>Delete money from old account;
>>Drop table EMP;
>>
>>...which achieves exactly the same thing as before.
>>
>>Since both you and Richard appear to agree that a piece of DDL is not 
>>necessarily the last statement in a transaction, I guess I must be missing 
>>something. Enlightenment from either would therefore be appreciated.
>>

>
>
> Hi Howard,
>
> Oh how the English language can confuse and deceive !!
>
> "A piece of DDL is not necessarily the last statement in a transaction"
> because the last statement in a transaction is generally a commit or a
> rollback !!
>
> Just like you said !!
>
> The way *I* read the question "Why is DDL required to be the last statement
> in a transaction?" is that DDL is *required* to be the last statement in a
> transaction. I'm simply of the opinion that *no* DDL is required to be the
> last statement in a transaction, and that a piece of DDL is not necessarily
> the last statement in a transaction because a commit or a rollback *is*
> generally the last statement of a transaction.
>
> Now read my earlier response and hopefully it makes sense.
>
> Sometimes the bleedin obvious isn't so bleedin obvious ...
>
> Cheers
>
> Richard

I still don't get the subtlety you see.

All DDL issues a commit. Therefore, taking the word "required" out of the equation, all DDL statements WILL be the last statement in a transaction, whether you like it or not.

Cut and dice it any way you care to, but the second you issue a DDL statement, you've just terminated your transaction.

Is the perceived ambiguity here in the use of the word "required" perhaps? On the grounds that it seems to imply someone or some document demanding that it should be?? I'd say that was irrelevant. It's not that there is some law in the Universe stating "thou shalt issue DDL last". It's that there's a law which states "DDL finishes transactions, so the DDL will logically and inevitably be the last statement in a transaction".

We are not dealing with subjunctives here, in other words ("should"). We are dealing with imperatives ("will").

As the line in a Benjamin Britten opera has it: "It seems we agree but are not of the same opinion"!!

Regards
HJR Received on Thu Nov 18 2004 - 14:56:44 CST