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….

Pages: 1 2




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |