Impact of DDL statements on transactions in Oracle

Impact of DDL statements on transactions in Oracle
Zookeeper

1. Impact of DDL statements on transactions in Oracle

In Oracle, when executing DDL statements (such as Create Table and Create View), a Commit command is automatically issued before execution, and a Commit or Rollback command is then issued, that is, DDL is executed like the following pseudo code:

Commit; DDL_Statement;

If (Error) then

Rollback;

Else

Commit; End if;

The following example shows the impact of DDL statements on transactions in Oracle:

Insert into some_table values ('before'); Creaate table T (x int );

Insert into some_table values ('after'); Rollback;

Since the statement is submitted before Oracle executes the Create table statement, and the Commit command is automatically issued After the Create table statement is executed, only the row inserted After is rolled back, the line inserted to Before will not be rolled back, and the result of the Create table command will not be rolled back. Even if the Create table statement fails, the Before insertion will be submitted. If the final Commit command is issued, the Insert Before and Create table operations are submitted Before, so the Commit command only affects the Insert After operation.

2. Impact of DDL statements on transactions in SQL Server

In SQL Server, DDL statements have the same effect on transactions as other DML statements. That is to say, no Commit command is automatically issued before or after a DML statement is issued.

In SQL Server 2000, the database rolls back to the status Before the Before is inserted after Rollback is triggered for the same Oracle example above, that is, the rows inserted to Before and After will be rolled back, and the data table T will not be created.

If a Commit operation is triggered, all the results of the three operations are submitted.