DDL in a Transaction: What Rolls Back and What Blocks

DDL in a transaction works in SQL Server: CREATE, ALTER and DROP roll back like any other change. In some databases a CREATE commits at once and survives a rollback. SQL Server does not work that way. The catch lies elsewhere: an open DDL transaction holds locks that make other sessions wait.

Create a Table and Roll It Back

The demo database is DdlTranDemo. It starts empty. Run it on a test server.

IF DB_ID(N'DdlTranDemo') IS NULL CREATE DATABASE DdlTranDemo;
GO
USE DdlTranDemo;
GO
DROP TABLE IF EXISTS dbo.Seedlings;

The next batch creates a table inside a transaction and adds a row. It checks that the table is visible inside the transaction. Then it rolls back and checks again.

BEGIN TRANSACTION;
CREATE TABLE dbo.Seedlings (SeedlingID int NOT NULL PRIMARY KEY, Name varchar(30) NOT NULL);
INSERT INTO dbo.Seedlings VALUES (1, 'Basil');
SELECT CASE WHEN OBJECT_ID(N'dbo.Seedlings') IS NULL THEN 'missing' ELSE 'visible' END AS InsideTransaction,
       @@TRANCOUNT AS OpenTransactions;
ROLLBACK TRANSACTION;
SELECT CASE WHEN OBJECT_ID(N'dbo.Seedlings') IS NULL THEN 'missing' ELSE 'visible' END AS AfterRollback;

The table was real inside the transaction and gone after the rollback. A permanent table and a visible check show the result without an error message. A temporary table and a failed SELECT prove the same thing, but the check is easier to read.

Now the same batch with COMMIT. The table stays, and so does its row.

BEGIN TRANSACTION;
CREATE TABLE dbo.Seedlings (SeedlingID int NOT NULL PRIMARY KEY, Name varchar(30) NOT NULL);
INSERT INTO dbo.Seedlings VALUES (1, 'Basil');
COMMIT TRANSACTION;
SELECT CASE WHEN OBJECT_ID(N'dbo.Seedlings') IS NULL THEN 'missing' ELSE 'visible' END AS AfterCommit,
       (SELECT COUNT(*) FROM dbo.Seedlings) AS SeedlingRows;

ALTER, CREATE INDEX and DROP Roll Back Too

The test is stronger with a table that holds data. This batch adds a column, creates an index and drops the whole table. A check inside the transaction confirms the drop. After the rollback, everything is back as it was.

BEGIN TRANSACTION;
ALTER TABLE dbo.Seedlings ADD Height int NULL;
CREATE INDEX IX_Seedlings_Name ON dbo.Seedlings (Name);
DROP TABLE dbo.Seedlings;
SELECT CASE WHEN OBJECT_ID(N'dbo.Seedlings') IS NULL THEN 'missing' ELSE 'visible' END AS InsideTransaction;
ROLLBACK TRANSACTION;
SELECT CASE WHEN OBJECT_ID(N'dbo.Seedlings') IS NULL THEN 'missing' ELSE 'visible' END AS TableAfterRollback,
       CASE WHEN COL_LENGTH(N'dbo.Seedlings', N'Height') IS NULL THEN 'missing' ELSE 'present' END AS HeightColumn,
       CASE WHEN EXISTS (SELECT 1 FROM sys.indexes
                         WHERE object_id = OBJECT_ID(N'dbo.Seedlings') AND name = N'IX_Seedlings_Name')
            THEN 'present' ELSE 'missing' END AS NameIndex,
       (SELECT COUNT(*) FROM dbo.Seedlings) AS SeedlingRows;

The table, its row, and its original shape all came back. DDL belongs to the transaction.

Statements That Are Refused

A few statements cannot run inside an explicit transaction at all. CREATE DATABASE and ALTER DATABASE fail with Msg 226. A backup fails with Msg 3021. Each batch below starts a transaction, attempts the statement and rolls back. Run the backup line only inside the transaction, as written. Outside one it would run.

BEGIN TRANSACTION;
CREATE DATABASE DdlTranDemoTwo;
ROLLBACK TRANSACTION;
GO
BEGIN TRANSACTION;
ALTER DATABASE DdlTranDemo SET RECOVERY SIMPLE;
ROLLBACK TRANSACTION;
GO
BEGIN TRANSACTION;
BACKUP DATABASE DdlTranDemo TO DISK = N'NUL';
ROLLBACK TRANSACTION;

The messages read as follows. Nothing is created and nothing is changed.

CREATE DATABASE statement not allowed within multi-statement transaction.
ALTER DATABASE statement not allowed within multi-statement transaction.
Cannot perform a backup or restore operation within a transaction.

What an Open DDL Transaction Holds

A DDL statement takes a schema modification lock on the object, and the lock stays until the transaction ends. The next batch runs ALTER TABLE and reads the lock from sys.dm_tran_locks.

BEGIN TRANSACTION;
ALTER TABLE dbo.Seedlings ADD Height int NULL;
SELECT DISTINCT l.resource_type, l.request_mode, l.request_status
FROM sys.dm_tran_locks AS l
WHERE l.request_session_id = @@SPID AND l.resource_database_id = DB_ID()
  AND l.resource_type = N'OBJECT' AND l.resource_associated_entity_id = OBJECT_ID(N'dbo.Seedlings');
ROLLBACK TRANSACTION;

Sch-M is the mode that conflicts with everything else. While this transaction stays open, a second session that reads the same table waits. A second session that reads sys.objects waits as well. With a lock timeout of 2 seconds, both queries failed with a timeout error. A query on a different table ran normally. This is the second window, run while the first one holds the open transaction.

SET LOCK_TIMEOUT 2000;
GO
SELECT COUNT(*) FROM dbo.Seedlings;
GO
SELECT COUNT(*) FROM sys.objects;

Put GO after SET LOCK_TIMEOUT. In the same batch the query waits instead of failing.

A CREATE TABLE behaves differently. Other tables stay available. Queries on the catalog views, such as sys.objects and sys.tables, wait until the transaction ends. A temporary table moves that effect to tempdb, so the catalog of your own database stays free.

Make a Deployment All or Nothing

A transaction alone does not guarantee that. A plain error does not end the transaction. SQL Server stops that one statement and carries on, and the later COMMIT keeps the rest. The first script below creates a table, then inserts a duplicate key, then commits. The table survives. The second script sets XACT_ABORT ON first, so the same error rolls everything back.

DROP TABLE IF EXISTS dbo.Pots;
BEGIN TRANSACTION;
CREATE TABLE dbo.Pots (PotID int NOT NULL PRIMARY KEY);
INSERT INTO dbo.Pots VALUES (1), (1);
COMMIT TRANSACTION;
GO
SELECT CASE WHEN OBJECT_ID(N'dbo.Pots') IS NULL THEN 'missing' ELSE 'visible' END AS WithoutXactAbort;
GO
DROP TABLE IF EXISTS dbo.Pots;
SET XACT_ABORT ON;
BEGIN TRANSACTION;
CREATE TABLE dbo.Pots (PotID int NOT NULL PRIMARY KEY);
INSERT INTO dbo.Pots VALUES (1), (1);
COMMIT TRANSACTION;
GO
SELECT CASE WHEN OBJECT_ID(N'dbo.Pots') IS NULL THEN 'missing' ELSE 'visible' END AS WithXactAbort;
SET XACT_ABORT OFF;

Both runs print the duplicate key error, Msg 2627. Only the second leaves nothing behind. Put SET XACT_ABORT ON at the top of every deployment script that uses DDL in a transaction.

Keep DDL Transactions Short

You could argue that a transaction around every deployment is the safe habit, because a failure can then undo everything. That holds once XACT_ABORT is on. It comes with one more rule: keep the transaction short. Do not leave a window open with a DDL change in it while you check something or go to lunch. Every session that needs the table, or the catalog, waits until you finish.

Run heavy work, such as an index build on a huge table, in a quiet window. Prepare the script and test it on a copy first. Inside the live transaction, run only the statements that must succeed or fail together. Check the result, and commit as soon as it looks right. The longer the transaction stays open, the longer other sessions wait.

What to Remember

DDL in a transaction is real and reversible. Test the rollback of your own deployment script on a copy first. CREATE DATABASE, ALTER DATABASE and BACKUP are the exceptions, so keep them out of the transaction.

When you finish the demo, drop the database.

USE master;
GO
DROP DATABASE DdlTranDemo;

A transaction is not a safe place to wait, it is a place where every lock stays until you decide.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.