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.

Continues….