By: Sergey Gigoyan | Updated: 2016-12-09 | Comments | Transactions
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.
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. 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:
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:
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:
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 StepsCheck out this related information:
Last Updated: 2016-12-09
About the author
View all my tips