Multiple Batches in a single Transaction - Brian Love - Google Developer Expert and Software Engineer in Bend, Oregon

Goal Create a migration script that uses a transaction to roll back the changes in case one of the sql statements fail.

Problem If you alter a table and add a column, and then attempt to use the new column in SQL statements that follow, you will discover that you get an error that indicates that the column does not exist. This is because the statements are in the same batch.

Sample problem code Here is a little snippet showing TSQL that contains the problem. This is a classic example of adding a bit field to an existing table, setting the values all to true (1), and then updating the bit field to not allow NULLs.

BEGIN TRANSACTION

ALTER TABLE Employees ADD Active bit;

UPDATE Employees SET Active = 1;

ALTER TABLE Employees ALTER COLUMN Active bit NOT NULL;

COMMIT TRANSACTION;

So, what are we left to do? Well, we need to create a single transaction with multiple batches, separated by the GO keyword.

Batches

Batches is an important concept in SQL Server. A batch consists of the series of SQL statements that are being executed in the same execution plan. As defined by TechNet:

A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution. SQL Server compiles the statements of a batch into a single executable unit, called an execution plan. The statements in the execution plan are then executed one at a time.

The use of the GO keyword in TSQL separates your script into multiple batches. This solves the problem with the migration script where we are adding a column and updating the column in the same transaction.

But, there is a catch. If an error occurs in a batch, the script will continue to the next batch, and compile and execute it. So, I needed a way to know in the next batch if an error had occurred in the previous batch.

To test this, run the following code:

BEGIN TRANSACTION

-- Batch 0
BEGIN TRY

  ALTER TABLE Employees ADD Active bit;

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  ROLLBACK TRANSACTION;
END CATCH;
GO

-- Batch 1
BEGIN TRY
  UPDATE Employees SET Active = 1;
  ALTER TABLE Employees ALTER COLUMN Active bit NOT NULL;
END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  ROLLBACK TRANSACTION;
END CATCH;
GO

COMMIT TRANSACTION;

In the sample code above I am throwing an exception in the first batch, but let’s assume you have some error here instead. The problem with this code is that the second batch is still executed when an error occurred in the first batch. Further, the statement to commit the transaction is also attempting to execute. So, we get the following errors in SQL Server Management Studio.

Error Number: 50000
Line Number: 5
We are raising an error on purpose to test.
Msg 208, Level 16, State 1, Line 4
Invalid object name 'Employees'.
Msg 3902, Level 16, State 1, Line 2
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Solution

The solution is to use the SET XACT_ABORT ON command. This instructs SQL Server to roll back the transaction when an error occurs in one of the SQL statements. With this set to on, we can then use the XACT_STATE() method to determine the status of our transaction.

The XACT_STATE() returns the following integer values:

-1 when an error has occurred in our transaction,
0 when the transaction does not exist or it has been rolled back, and
1 when the transaction is active (and no errors have occurred).

We can test the value of the transaction state using XACT_STATE() in each of the batches. Here is the final solution code.

SET XACT_ABORT ON;
GO

BEGIN TRANSACTION

-- Batch 0
BEGIN TRY
  RAISERROR('We are raising an error on purpose to test.', 18, 0);
  ALTER TABLE Employees ADD Active bit;
END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  ROLLBACK TRANSACTION;
END CATCH;
GO

-- Batch 1
BEGIN TRY
  -- Rollback transaction if error occurred
  IF (XACT_STATE()) = -1
  BEGIN
    RAISERROR('The transaction is in an uncommittable state. Rolling back transaction.', 18, 3);
  END;

  -- Do not continue if the transaction was rolled back
  IF (XACT_STATE()) = 0
  BEGIN
    RAISERROR('The transaction was rolled back.', 18, 1);
  END;

  -- Set all employees to Active
  UPDATE Employees SET Active = 1;

  -- Do not allow Active to be null
  ALTER TABLE Employees ALTER COLUMN Active bit NOT NULL;

END TRY
BEGIN CATCH
  PRINT 'Error Number: ' + str(error_number()) ;
  PRINT 'Line Number: ' + str(error_line());
  PRINT error_message();
  IF (XACT_STATE()) <> 0
  BEGIN
    PRINT 'Rolling Back Transaction...';
    ROLLBACK TRANSACTION;
  END;
END CATCH;
GO

-- Commit transaction
IF XACT_STATE() = 1
BEGIN
  COMMIT TRANSACTION;
  PRINT 'Transaction committed.';
END;

The solution above uses the XACT_STATE() method in the second batch to determine if we had an error previously in the transaction. This ensures that we can have a single transaction that contains multiple batches that are all rolled back if an error occurs in any of the statements in the transaction.