Re: Oracle transactions and DDL statements.

peter.koch.larsen_at_gmail.com wrote:
> DA Morgan skrev:
>

[]
> I will recommend that you have a talk with one of the people from that
> group (I assume you are not part of it) and ask one from the staff
> there for a motivation to have a transaction that contains
> DDL-statements not to be a transaction (at least it violates at least
> one of the ACID rules). You will find there is none - except perhaps
> for a pragmatic "it is to difficult to implement" or "it is
> historically so" argument. (Those arguments will come from Oracle, of
> course).

It may be a pragmatic decision, but ORACLE is not the only DBMS to do it this way, contrary to MS SQL and others that do not follow this decision.

Even if DDL could be mixed with DML, I would not create applications that way. The potential for errors seems too great.

IIRC, yours was a distributes update. I know from earlier work I've done that one model to use is called a two phase commit. (ORACLE uses this internally for some distributed transactions.) All of your DB applications would have to follow this protocol, but the idea is: stage the changes at each node, when all nodes confirm that they have met all conditions to successfully complete the transaction, only then does a final commit go out to all nodes. I've obviously left a lot of details out (I last looked in detail at this about 10 years ago.) But it should give you a more robust, flexible approach.

HTH,
  ed Received on Fri May 12 2006 - 15:12:13 CDT