DDL commands in Transactions in SQL Server versus Oracle（转载）

Problem

Transactions are widely used in database development, especially when a database supports an application where a lot of correlated changes are needed. A transaction is a group of SQL statements that are all committed together (all changes done by these statements become a permanent part of the database) or none of these statements are committed (all these changes are rolled back). More often statements included in transaction are DML (Data Manipulation Language) statements, such as INSERT, UPDATE, DELETE and so on. But what about DDL (Data Definition Language) statements? Is it possible to include DDL commands such as CREATE, ALTER, DROP, etc., in a transaction? In this tip we are going to answer these questions for both - MS SQL Server and Oracle databases.

Solution

The approaches to use DDL commands within transactions are quite different in Microsoft SQL Server vs. Oracle. Let’s discuss this for each RDBMS separately.

DDL and Transactions in Microsoft SQL Server

Generally it is possible to include DDL statements in one transaction in Microsoft SQL Server. However, there are exceptions: some DDL statements are not allowed in transactions, for example CREATE/ALTER/DROP DATABASE commands, CREATE/ALTER/DROP FULLTEXT INDEX and so on. When including DDL statements in a transaction, like DML commands they all are either committed or rolled back. This means that it is possible to ROLLBACK a created table or ROLLBACK truncated data. Let’s prepare a database for testing and see an example:

USE master
GO

CREATE DATABASE TestDB
GO

USE TestDB
GO

CREATE TABLE TableA
(
 ID INT NOT NULL PRIMARY KEY,
 Value CHAR(1)
)

INSERT INTO TableA(ID, Value)
VALUES (1,'A'),(2,'B'),(3, 'C')

CREATE TABLE TableB
(
 ID INT NOT NULL PRIMARY KEY,
 Value CHAR(1)
)

INSERT INTO TableB(ID, Value)
VALUES (1,'X'),(2,'Y'),(3, 'Z')

We have two tables with data in our TestDB database. Now let’s start a transaction and do some DDL changes in the TestDB database:

USE TestDB
GO

BEGIN TRANSACTION

 TRUNCATE TABLE TableA

 DROP TABLE TableB

 CREATE TABLE TableC(ID INT)

ROLLBACK


SELECT * FROM TableA
SELECT * FROM TableB
SELECT * FROM TableC

We can see that after the rollback there is no TableC in the database:

TableA and TableB exist and contain data:

This means that all these changes made by the DDL commands, that are included in the transaction, have been rolled back. So, we can include DDL commands (with some exceptions) in transactions in MS SQL Server.

DDL and Transactions in Oracle

Unlike SQL Server, transactions in Oracle are always implicit. This means that a logical transaction starts in the event of a data change in the database. The other big difference from SQL Server is that in Oracle DDL commands automatically commit transactions (xnerv: So it’s not weird that Oracle bought MySQL since InnoDB has the same behavior… Just a joke : ). Every new database connection opens a new transaction and an explicit COMMIT command is needed to make them a permanent part of database (as mentioned above DDL commands automatically COMMIT a transaction and in this case an explicit COMMIT is not needed). Commands issued after a COMMIT open a new transaction and so on. Let’s look at an example:

CREATE TABLE TableA (Value INT);

INSERT INTO TableA(Value) VALUES(1);
INSERT INTO TableA(Value) VALUES(2);
INSERT INTO TableA(Value) VALUES(3);

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

COMMIT;

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

In this example TableA is created and after that 3 rows are inserted. We can see that there is one active transaction after the INSERT statements and after the COMMIT there are no active transactions:

In the next script we’ll issue a DELETE command and after that we create a new table. After creating TableB, we delete one more row from TableA and then issue a ROLLBACK command:

DELETE FROM TableA WHERE Value=2;

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

CREATE TABLE TableB (Value INT);

DELETE FROM TableA WHERE Value=3;

ROLLBACK;

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

SELECT * FROM TableA;
SELECT * FROM TableB;

SELECT * FROM V$TRANSACTION WHERE STATUS='ACTIVE';

As a result, we can see that although a ROLLBACK is issued, TableB is created and the row with Value=2 from TableA is deleted. The reason is that the CREATE TABLE command which is a DDL command commits the transaction. However, the second DELETE statement has been rolled back, because it is issued after the DDL and before the ROLLBACK command.

So, now it becomes obvious that DDL commands in Oracle cannot be rolled back and included in one transaction.

Create multiple tables in a single transaction in Oracle

Sometimes it is necessary to create more than one table in a single transaction. So, how can we solve this problem in Oracle? We can do that by using a CREATE SCHEMA statement. This statement allows us to include multiple CREATE TABLE or CREATE VIEW statements as well as multiple GRANT statements in a single transaction in your own schema. If all statements in the CREATE SCHEMA are executed successfully the transaction is committed. In case of an error, all commands included in the CREATE SCHEMA statement are rolled back. It is important to note that CREATE SCHEMA statement does not create a schema. It’s used to create tables, views or grant privileges in one transaction. In the example below, we are using the CREATE SCHEMA statement to create two tables in one transaction. Before executing this statement it is essential to make sure that you are creating objects in your own schema and have necessary permissions to issue the statements:

1
2
3

CREATE SCHEMA AUTHORIZATION MyUser
CREATE TABLE TableC (Value INT)
CREATE TABLE TableD (Value INT);

Two tables has been successfully created:

In the next example we are trying to create two tables:

1
2
3

CREATE SCHEMA AUTHORIZATION MyUser
CREATE TABLE TableC (Value INT)
CREATE TABLE TableE (Value INT);

The transaction is rolled back (because TableC already exists), therefore TableE has not been created:

Conclusion

In conclusion, the SQL Server and Oracle database engines manage transactions and DDL commands differently. As we can see in this tip SQL Server allows us to include multiple DDL commands in a single transaction in contrast to Oracle. The latter commits transactions when a DDL command is issued, so it is not possible to combine DDL statements in one transaction. However, in Oracle it is possible to issue multiple table and view creation statements, as well as multiple grant statements in a single transaction by using the CREATE SCHEMA statement.

Next Steps

Check out this related information:

本文地址：http://xnerv.wang/ddl-commands-in-transactions-in-sql-server-versus-oracle/
转载自：DDL commands in Transactions in SQL Server versus Oracle