Implementing Transactions in SQL Server – Part II

In Implementing Transactions Part I I briefly described the role of Transactions in SQL Server and outlined a very basic implementation. In this second part, I will explain how a DBA can best implement Transactions in scripts that are to be deployed on production databases.

One of the regular tasks of a DBA is to generate database schema change scripts, and then deploy the scripts to SQL databases. If an organization is not using a third-party tool, as is common, then Database Professional (which is part of Visual Studio from Microsoft) is normally used to accomplish this task. In many companies, the process of generating schema change scripts is a daily routine. Create DB scripts are generated on from TFS, a schema compare is performed between the previous and the latest build, and the schema update script is generated. The only issue is they do not run as a single transaction. The reason is that the scripts that are generated do not have explicit transactions defined. Unfortunately it is not simply a matter of defining a transaction using BEGIN TRANSACTION, and based on the @@TRANCOUNT variable in the end of the script either perform a Rollback or a Commit. This is due to the fact that after every DDL statement, DBPro inserts a GO statement, causing each statement to run as a batch, and explicit transactions do not span multiple batches. A sample script that is generated by DBPro would be similar to this:

PRINT N'Creating [dbo].[test1]'
GO
CREATE TABLE [dbo].[Test1]
(
[Col1] [bigint] NOT NULL IDENTITY(1, 1),
[Col2] [int] NOT NULL,
[Col3] [varchar] (50) NOT NULL,
[Col4] [varchar] (50) NOT NULL
)
GO
PRINT N'Creating primary key [PK_Test1] on [dbo].[Test1]'
GO
ALTER TABLE [dbo].[Test1] ADD CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  ([Col1])
GO
PRINT N'Creating [dbo].[usp_SP1]'
GO
CREATE PROCEDURE [dbo].[usp_SP1]
AS
…..
ALTER TABLE [dbo].[Test2] ADD [Col1] VarChar(100)NULL
GO

If there are no syntax errors in any of the DDL statements, then all the statements will run successfully. If, however, any of the statements results in an error then we have issues. Let’s say, the sample script above fails at the following statement because the table dbo.Test2 doesn’t yet exist on the database:

ALTER
TABLE [dbo].[Test2]
ADD [Col1] VarChar(100)NULL
GO

In this case, all the statements before this statement in the script would have run successfully, and committed the schema changes on the database, and the remaining statements after this statement including this one will not update the database leaving it in an unstable state. One solution is to fix this statement and run only the remainder of the script. This is a manual step, and is fine if scripts are deployed manually to the database. But what if there are a series of scripts being deployed at a time using an automated process?

There are several options to address this:

Option 1 : Remove all the GO statements from the scripts and then wrap the entire script within a single explicit Transaction. This involves manually editing the files which may not be feasible if the files are very large in size with numerous GO statements.

Option 2 : Implement transactions. Didn’t I mention earlier it is not possible because of the GO statements? Actually it is possible with a little bit of tweak, and the use of a SET option in the script. In SQL Server, there is a SET option called SET XACT_ABORT. This option specifies whether SQL Server automatically terminates and rolls back a Transaction if a T-SQL statement raises a runtime error. The default option is OFF. But, if it is set to ON, the entire transaction is terminated and rolled back.

To avail of this the above above sample script can be rewritten as below:

:On Error Exit
SET XACT_ABORT ON
GO
Begin Transaction
      PRINT N'Creating [dbo].[test1]'
      GO
      CREATE TABLE [dbo].[Test1]
      (
      [Col1] [bigint] NOT NULL IDENTITY(1, 1),
      [Col2] [int] NOT NULL,
      [Col3] [varchar] (50) NOT NULL,
      [Col4] [varchar] (50) NOT NULL
      )
      GO
      PRINT N'Creating primary key [PK_Test1] on [dbo].[Test1]'
      GO
      ALTER TABLE [dbo].[Test1] ADD CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  ([Col1])
      GO
      PRINT N'Creating [dbo].[usp_SP1]'
      GO
      CREATE PROCEDURE [dbo].[usp_SP1]
      AS
      …..
      ALTER TABLE [dbo].[Test2] ADD [Col1] VarChar(100)NULL
      GO
If Xact_State()=1
Begin
      Print 'Committing Tranaction...'
      Commit tran
End
Else If Xact_State()=-1
Begin
      Print 'Rolling Back Transaction...'
      RollBack Tran
End

Please note the first four lines and the last ten lines in the script.

:On Error Exit

This command causes sqlcmd to exit the sql script upon encountering an error.

SET
XACT_ABORT ON

With this statement, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

Begin Transaction

This statement defines an explicit transaction for the entire sql script.

If Xact_State()=1 

This statement checks for any committable transactions at the end of the script. If there are any, then Xact_State() will be 1, and the transaction will be committed in the IF block.

If Xact_State()=-1 

This statement checks if the transaction introduced in the script is in an ‘uncommittable’ state. By uncommittable, I mean if any error was encountered during the execution of the script then the script cannot continue execution, and all changes introduced in the database up to the point of failure in the script need to be rolled back. This rollback is done in this IF block.

Please note that scripts in which we introduce transactions in this manner, can only be run from the command prompt via SQL Server’s command-line utility sqlcmd. If this script is run from Management Studio, then it is possible that it will run only partially and commit only those changes where there are no errors in the script. This is because there is a difference in the behavior of execution of sql scripts when they are run from the SQL Server Management Studio, and when they are run from the command prompt since SSMS uses the .NET Framework SqlClient for execution in regular and SQLCMD mode in Query Editor . Whereas, sqlcmd when run from the command line uses the OLE DB provider. Since different default options may apply, there are different behaviors when executing the same query in SSMS in SQLCMD Mode and when exuting the query using thesqlcmd utility

This solution is good for implementing transactions in individual scripts when they are called from sqlcmd. But what if there is a requirement to run a series of such scripts which should all either commit or all roll back it is not a good solution.

We have to tweak this approach a little bit to satisfy this requirement. For this purpose we could not define the transactions in individual scripts as I have explained earlier, but call these scripts from another sql script I call asWrapper.sql, then define the transaction in this wrapper and call the wrapper from sqlcmd.  The script would like this:

:On Error Exit
SET XACT_ABORT ON
GO
Begin Transaction
:r script_1.sql
:r script_2.sql
:r script_3.sql
If Xact_State()=1
Begin
       Print 'Committing Tranaction...'
              Commit tran
End
Else If Xact_State()=-1
Begin
       Print 'Rolling Back Transaction...'
              RollBack Tran
End

Throughout this example the concept has remained the same. The only difference is how and where to define the transaction, and how to call the scripts.