Data Definition Language in Transactions (version 6.5)

Data definition language (DDL) statements can appear within a user transaction. This allows the new CREATE SCHEMA statement to function. You can build a batch of schema-building statements and they will all be committed simultaneously. This eases the strict instruction order that was required by earlier releases.

The following statements can appear in transactions:

A database object (including a global temporary table) cannot be dropped while it is in use. If the object is in use and a drop is attempted, the transaction will be rolled back. The only exception to this is the implicit drop that is done to a global-temporary object when the creating connection disconnects.

The SELECT INTO statement is now supported within a transaction. This means that tables can be created by using SELECT INTO within a transaction. The data is removed and the table is dropped if the transaction is rolled back.

User transactions are not rolled back when a batch contains a syntax or compile-time error. A batch with this type of error will not execute. This situation can cause unexpected behavior if the BEGIN TRANSACTION statement was executed in a previous batch, because the transaction will be active even though the rest of the batch will not run.

If an uncommitted procedure or trigger is executed and causes a rollback, it will drop itself but continue executing the cached copy. This copy will be removed when the procedure or trigger completes execution.

The sp_bindrule, sp_unbindrule, sp_bindefault, and sp_unbindefault stored procedures can also be used within a transaction whether or not the rule or table has been modified within that transaction or within another transaction.

All resources (such as pages, extents, and locks) used by a transaction are freed only at the end of the transaction. For DDL statements on large tables, this can affect a significant number of pages.

Important While implicit drops of temporary tables can occur successfully at any time, the resources for the temporary table (including locks on system tables in tempdb) are not freed until the end of the transaction.

Use caution when you create temporary tables inside a transaction. Until the transaction commits or rolls back, exclusive locks are held on several system tables in the tempdb database to ensure recovery. These locks are held even if the temporary tables are dropped, and they can block other users' attempts to create temporary tables or other objects in tempdb.

This caution also applies to stored procedures that create temporary tables. If the stored procedure is executed within a transaction, the locks are held on the system tables until the transaction completes, even though the temporary table is automatically dropped when the procedure completes execution.

When invoked inside a user transaction, SELECT INTO has the ACID transaction properties (atomicity, consistency, isolation, durability): either the transaction is completely committed or it is completely rolled back.

Examples

A.    Create a Table and Insert Values in One Transaction

This example creates and fills a table in a single transaction. The table is created and filled with data simultaneously. Either the table is not created and the transaction is rolled back, or the table is created, filled with data, and then committed.

BEGIN TRAN
CREATE TABLE pay_type_table (pay_id int, pay_type varchar(20))
INSERT pay_type_table (1, 'Cash')
INSERT pay_type_table (2, 'Check')
INSERT pay_type_table (3, 'Credit Card')
INSERT pay_type_table (4, 'Electronic Wire')
INSERT pay_type_table (5, 'Banker Draft')
COMMIT TRAN
  

B.    Demonstrate Exclusive Locks on a Temporary Table in a Transaction

This example attempts a second session to access a newly created but uncommitted object; it results in the second session waiting until the creating transaction is complete. If the first transaction results in the object being removed and also creates another object by the same name and the waiting session is attempting to open the object by name, the waiting session will open the second (and newest) object.

This table illustrates the relationship between the two connections.

BEGIN TRAN
CREATE TABLE test (c1 int)
INSERT test VALUES (1)
go

SELECT * FROM test

DROP TABLE test
go
CREATE TABLE test (c1 int, c2 int)
INSERT test VALUES (1,2)
go
COMMIT TRAN

Results Set
c1    c
------------
1     2
(1 row(s) affected)

C.    Use SELECT INTO Within a Transaction

Before running this example, sp_dboption select into\bulk copy must be set to TRUE.

The SELECT INTO statement creates a new table called authors2, into which the records in the authors table are copied. After the transaction is rolled back the authors2 table has been dropped and the final SELECT statement causes an error.

GO
BEGIN TRANSACTION
SELECT * INTO authors2 FROM authors
SELECT * FROM authors2
ROLLBACK TRANSACTION 
SELECT * FROM authors2
GO