BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Transactions in SQL Server
- Errors within Transactions (this blog)
- Nesting Transactions
- 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
ROLLBACK TRAN MyTransaction
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
SET FilmRunTimeMinutes = 'some text'
WHERE FilmName = 'My new film'
COMMIT TRAN DeliberateError
When the code above is executed we see the following messages:
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:
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:
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.