By
Amit Chaudhary
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 the
sqlcmd
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 as
Wrapper.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.
