The impact of DDL statements on transactions in Oracle



The effect of DDL statements on transactions in 1.Oracle

In Oracle, when executing DDL statements (such as Create Table, create view, and so on), a commit command is issued automatically before execution and a commit or ROLLBACK command is subsequently issued, which means The DDL executes like this pseudo code:

Commit;ddl_statement;

If (Error) Then

Rollback;

Else

Commit; End if;

We analyze the impact of DDL statements on transactions in Oracle by analyzing the following example:

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

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

Since the commit is committed before Oracle executes the CREATE TABLE statement, and the commit command is issued automatically after the CREATE table executes, only the rows inserted in after are rolled back, and the rows inserted into the before are not rolled back, Create The result of the table command is also not rolled back, even if the CREATE TABLE statement fails, and the before insert is committed. If the commit command was last issued because the result of inserting before and create table was previously committed, the commit command affects only the operation that was inserted after.

The impact of DDL statements on transactions in 2.SQL server

In SQL Server, the effect of a DDL statement on a transaction is the same as other DML statements, that is, the commit command is not issued automatically before or after the DML statement is issued.

In SQL Server 2000, for the same example as Oracle above, after the final rollback is issued, the database is rolled back to the state before the before was inserted, that is, the rows inserted before and after are rolled back, and the data table T is not created.

If a commit is last issued, the results of the three operations are all committed.

Reference: http://www.233.com/oracle/zonghe/20101008/103744390.html
http://blog.csdn.net/fw0124/article/details/6227376

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

The effect of DDL statements on transactions in Oracle