Transactions in SQL Server
Part two of a four-part series of blogs

This blog examines how to use Transactions in Microsoft SQL Server to allow you to control when data changes are committed to the database. You'll also discover how you can rollback transactions, effectively giving you the chance to undo changes to your data.

  1. Transactions in SQL Server
  2. Errors within Transactions (this blog)
  3. Nesting Transactions
  4. Transaction Savepoints

This blog is part of a complete SQL Server Tutorial.

Posted by Andrew Gould on 11 October 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Errors within Transactions

What happens when an error occurs while you have a transaction open?  This part of the series shows you how to use error handling to control what happens and also explains how the automatic rollback feature works.

Using Transactions with Error Handling

In the real world you are likely to use transactions with an error-handling routine such as a TRY/CATCH block. The code below shows an example of how this might work:

--Start our error handling code


--Start a new transaction

BEGIN TRAN MyTransaction

--Make some changes to data

--Commit the transaction

COMMIT TRAN MyTransaction

PRINT 'Transaction succeeded!'



--If we end up here something went wrong

--Undo the changes we made


PRINT 'Transaction rolled back'


In the basic example above we begin a transaction and then attempt to modify some data within the TRY block. If we successfully reach the end of this block we commit our transaction. If something goes wrong we enter the CATCH block and rollback the transaction instead.

Automatic Rollback of Transactions

If an error occurs when a transaction is open SQL Server will rollback the transaction automatically.  In the example below we have inserted a record into a table and then tried to store a string of text in a number field.

BEGIN TRAN DeliberateError

--Add a new record into the table

INSERT INTO tblFilm(FilmID, FilmName)

VALUES (999, 'My new film')

--Check that the film has been added

SELECT * FROM tblFilm WHERE FilmName = 'My new film'

--Cause an error

UPDATE tblFilm

SET FilmRunTimeMinutes = 'some text'

WHERE FilmName = 'My new film'

COMMIT TRAN DeliberateError

When the code above is executed we see the following messages:

Error message

Our code generates an error.

If we look at the Results tab of the window we can see that our new film was added to the table before the error occurred:

Film exists

The new film was successfully inserted into the table.


If we try to find the film in the table after this however, we discover that it is no longer there:

Film does not exist

The film is no longer in the table.

The reason the film is no longer in the table is because our transaction was automatically rolled back when the error occurred. This happens even though we don't have an explicit rollback statement in our code.

Although you could rely on the automatic rollback feature to ensure you don't end up with incomplete records it's still preferable to use TRY and CATCH blocks to handle the errors more elegantly.

This blog has 0 threads Add post