Re: Why Oracle does not allow rollback of DDL statements?
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 10 Nov 2008 07:06:06 -0500
Message-ID: <6nqmdfFafviU1@mid.individual.net>
>
> No, it does not. Don't try that rubbish with me, I've
> been breathing SS2K5 for the last 14 months. It does not.
> The front end you most likely use to access it does allow that.
> But that is not SQL Server.
That's the first I hear about that. Can you point to any docs explaining the behavior you imply? I'm also told that SQL Server (like DB2) has transactional DDL. Even TRUNCATE table is transactional.
> No it is not. Try having a process that relies on dropping a
> table to succeed and another that relies on a create index on
> that table. Which one is supposed to end first, when?
Oracle has to deal with that today!
No matter how you call it the CREATE statement still needs to be ATMIC. And atomic implies that there is a statement level savepoint. If any of the work against the db schema fails for any reason teh DDL statement needs to be rolled back. And that CREATE INDEX statement must not be permitted until the CREATE TABLE statement successfully COMPLETEs. What if someone else tries to create a table with the same name? If someone else drops the original table conflicting with the table I'm trying to create.
Obviously the table also cannot be used by anyone until that commit happens. Call it a lock or call it a latch, doesn't matter. Oracle has to sort out timing today. The only difference is that it has a statement level view on things rather than a transaction level view.
Date: Mon, 10 Nov 2008 07:06:06 -0500
Message-ID: <6nqmdfFafviU1@mid.individual.net>
Noons wrote:
> Thomas Kellerer wrote,on my timestamp of 10/11/2008 8:02 PM:
>
>> >> >> I was learning SQL Server few months ago and I noticed that SQL >> Server >>>> does allow rollback of DDL statements if I am not mistaken. >>> >>> It does not. >> Yes it does (Note that I do not like SQL Server at all, but that is
>
> No, it does not. Don't try that rubbish with me, I've
> been breathing SS2K5 for the last 14 months. It does not.
> The front end you most likely use to access it does allow that.
> But that is not SQL Server.
That's the first I hear about that. Can you point to any docs explaining the behavior you imply? I'm also told that SQL Server (like DB2) has transactional DDL. Even TRUNCATE table is transactional.
>> There might not be a requirement, but it *is* very handy.
> No it is not. Try having a process that relies on dropping a
> table to succeed and another that relies on a create index on
> that table. Which one is supposed to end first, when?
Oracle has to deal with that today!
No matter how you call it the CREATE statement still needs to be ATMIC. And atomic implies that there is a statement level savepoint. If any of the work against the db schema fails for any reason teh DDL statement needs to be rolled back. And that CREATE INDEX statement must not be permitted until the CREATE TABLE statement successfully COMPLETEs. What if someone else tries to create a table with the same name? If someone else drops the original table conflicting with the table I'm trying to create.
Obviously the table also cannot be used by anyone until that commit happens. Call it a lock or call it a latch, doesn't matter. Oracle has to sort out timing today. The only difference is that it has a statement level view on things rather than a transaction level view.
Cheers
Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto LabReceived on Mon Nov 10 2008 - 06:06:06 CST