Backing Out Of A SQL Transaction

When writing MS SQL statements, sometimes it’s necessary to have several queries that work together in unison.  Whether it’s updating, deleting or inserting multiple rows into multiple, different tables, it’s nice to be able to run a series of SQL commands as one single step.  This is where running a batch transaction comes in handy, as the following set of sample code demonstrates.

BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('This is a sample value.');
INSERT INTO TestTable2 (Field1, Field2) values ('Performing a test insert', 'Into SQL');
INSERT INTO TestTable3 (Field1) values ('All of these inserts should be successful.');
COMMIT TRANSACTION BigGroupInsert;

As you can see, this transaction will perform all of the Insert statements specified.  If there is an error processing one of the statements, like one of the values being to large for the target column, an error will occur for just that statement and all of the rest of the SQL commands will process.  That includes commands that were intended to occur after the failed step, not just the ones before.

But it’s possible this is not the correct course of action to take.  What if the entire batch needs to either perform all of the changes correctly or not make any changes at all?  It could be bad for data integrity if some tables have the updated rows, but related data is missing from other tables because of a SQL error.  Well fear not, there is a solution.  Add the command SET XACT_ABORT ON before the BEGIN TRANSACTION command to have SQL back out all changes in the Transaction if any error occurs.  This way the transaction works as a single unit and you can be guaranteed that that the entire transaction either worked or it didn’t, and there is nothing in between.

SET XACT_ABORT ON;
BEGIN TRANSACTION BigGroupInsert;
INSERT INTO TestTable1 (Field1) values ('If one of these values was too long.');
INSERT INTO TestTable2 (Field1, Field2) values ('Then this whole transaction', 'should fail');
INSERT INTO TestTable3 (Field1) values ('And none of these values should be inserted.');
COMMIT TRANSACTION BigGroupInsert;

For more information about SET XACT_ABORT, please refer to the MSDN article.

One thought on “Backing Out Of A SQL Transaction

  1. Pingback: SQL Transactions in .NET « Software Development for Enterprise Content Management

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s