1

Oracle does implicit commits on DDL statements. So you don't have to commit an ALTER statement for instance.

Microsoft SQL requires a commit on DDL statements.

SQL-92 is too old and there is just basic information about transactions and latest SQL Standard Documents cost dollars and I reject to pay for things that should be free (imho).

So I hope someone knows what the standard says and if the standard doesn't clarify this, maybe there is a de-facto standard or a "design recommendation" for DBMS developers.

Thanks a lot.

EDIT:

For clarification: what I want to know is, if a database SHOULD do implicit commits on DDL statements (according to the standard or some kind of de-facto standard / recommendations). I do not want to know, what the actual DBMS do in practise, because I know that already (and it's answered several times here on SO).

UniversE
  • 2,419
  • 17
  • 24
  • *"Oracle does implicit commits on DDL statements. So you don't have to commit an ALTER statement for instance."* Actually, DDL statements are not transacted at all. Implicit commit is not just after but also before. This means that an attempt to execute a DDL statement (even when it fails), will commit any open transaction for the session. – GolezTrol Jan 05 '15 at 12:42
  • 1
    But about standards, see this question: [Authoritative SQL standard documentation](http://stackoverflow.com/questions/6000847/authoritative-sql-standard-documentation). It links to various versions of these documents. – GolezTrol Jan 05 '15 at 12:45
  • possible duplicate of [Is it possible to run multiple DDL statements inside a transaction (within SQL Server)?](http://stackoverflow.com/questions/1043598/is-it-possible-to-run-multiple-ddl-statements-inside-a-transaction-within-sql-s) – GolezTrol Jan 05 '15 at 12:46
  • @GolezTrol - no and no. It is not a duplicate of the other question (in fact it's completely different ;) and no, the statement that "DDL is not transacted at all" is false (in general). This is the case for Oracle DBMS. In Microsoft SQL they are transacted. So you alter a procedure (e.g.), kill your session (without commit) and your procedure remains unchanged. Also an alter table ... add somecolumn ... is rolled back, if you just kill a session in MS SQL. – UniversE Jan 05 '15 at 13:08
  • 1
    If you're trying to make a product that follows a standard, then I'm afraid you'll have to shell out for that standard. There's no way you're going to be able to get everything correct by asking lots of questions on a Q&A site. And if you're not trying to write something that follows the "standard", I'm not sure what the point of the question is. – Damien_The_Unbeliever Jan 05 '15 at 13:15
  • @UniversE My comment was about Oracle, that why I cited your specific remark about Oracle. I'm well aware that different databases work differently. – GolezTrol Jan 05 '15 at 17:29

1 Answers1

1

Thanks to the link provided by @GolezTrol, I was able to read a draft of the SQL:2003 standard.

In 4.33.4 SQL-statements and transaction states it says, that all SQL Schema statements are transaction initiating.

In 4.33.5 SQL-statement atomicity and statement execution contexts it says, that no atomic SQL statement (and SQL Schema statements are considered atomic) may terminate an SQL transaction.

Finally, in 4.35.6 Effects of statements in an SQL-Transaction it says:

The execution of an SQL-statement within an SQL-transaction has no effect on SQL-data or schemas other than the effect stated in the General Rules for that SQL-statement, in the General Rules for Subclause 11.8, "referential constraint definition", in the General Rules for Subclause 11.39, "trigger definition", and in the General Rules for Subclause 11.50, "SQL-invoked routine"

So it seems that an implicit commit within a DDL statement is not permitted by the standard, because it would terminate a transaction and open a new transaction within an atomic statement. It is argueable, if closing a transaction is considered an "effect" on SQL schema or data (4.35.6) - maybe this note is irrelevant for deciding if a commit is allowed implicitly or not.

And in 4.35.1 General description of SQL-transactions it says:

It is implementation-defined whether or not the execution of an SQL-data statement is permitted to occur within the same SQL-transaction as the execution of an SQL-schema statement. If it does occur, then the effect on any open cursor or deferred constraint is implementation-defined. There may be additional implementation defined restrictions, requirements, and conditions. If any such restrictions, requirements, or conditions are violated, then an implementation-defined exception condition or a completion condition warning with an implementation-defined subclass code is raised.

So what happens, if the implementation does not allow data and schema statements in one transaction? Then you are forced to use a COMMIT before and after a group of schema statements. So that would not explain, why each schema statement should be implicitly surrounded by COMMITs.

In general the pages in the standard read like they always take the existance of transacted SQL schema statements for granted.

So my conclusion is, that the standard way is NOT to have implicit COMMITs within an DDL statement.

If you don't mind and if there are no objections / protests against my interpretation of the standard, I will accept what I've found out within the next few days.

UniversE
  • 2,419
  • 17
  • 24