Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server
Article

SQL Server Transactions and Error Handling

Rate me:
Please Sign up or sign in to vote.
4.80/5 (99 votes)
1 Jul 20036 min read 951.9K   2   196   41
SQL Server Transactions and Error Handling

Introduction

The examples used in this article uses the Pubs database that comes as a sample database when you install SQL Server. If you need to rebuild the Pubs database, follow the steps to install a fresh copy :

  1. Run the osql command prompt utility and detach the Pubs database from SQL Server by using the sp_detach_db system stored procedure.
    osql -U sa -P "" -Q "exec sp_detach_db 'Pubs'" 
  2. Delete the database files for pubs database (pubs.mdf, pubs_log.ldf). These files are located in the \Data directory.

  3. Re-creating the Pubs database requires the Instpubs.sql script to be executed. Run the script from the command line (if the .sql files are in a different directory, adjust the path accordingly). You can also run this script file from the Query Analyzer.
    osql -U sa -P "" -i
         "C:\Program Files\Microsoft SQL Server\MSSQL\Install\InstPubs.sql"
    

    (The osql utility uses case-sensitive options. If neither the -U or -P options are used, SQL Server 2000 attempts to connect using Windows Authentication Mode. More information about the osql Utility can be found in the Sql Server Books Online)

Transactions

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction

If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Here is an example of a transaction :

SQL
USE pubs

DECLARE @intErrorCode INT

BEGIN TRAN
    UPDATE Authors
    SET Phone = '415 354-9866'
    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers
    SET city = 'Calcutta', country = 'India'
    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR
    IF (@intErrorCode <> 0) GOTO PROBLEM
COMMIT TRAN

PROBLEM:
IF (@intErrorCode <> 0) BEGIN
PRINT 'Unexpected error occurred!'
    ROLLBACK TRAN
END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.

Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.

A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the

@@TRANCOUNT 
automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.

When you explicitly begin a transaction, the

@@TRANCOUNT 
automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions,
COMMIT 
always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between
COMMIT 
and ROLLBACK is the key to handling errors in nested transactions.

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.
Figure 2: A single ROLLBACK always rolls back the entire transaction.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically; COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.

Here is an example of a nested transaction :

SQL
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    COMMIT TRAN nested
                   -- Does nothing except decrement the value of @@TRANCOUNT

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
                   -- The value of @@TRANCOUNT is 1
ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0 
-- because ROLLBACK TRAN always rolls back all transactions and sets 
-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM titleauthor

In this example we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses the effects of the

DELETE 
titleauthor command.

Here is another similar example of nested transaction :

SQL
USE pubs
SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0
BEGIN TRAN
    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1
    DELETE sales
    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
               -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
    ROLLBACK TRAN
  
    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.

IF (@@TRANCOUNT > 0) BEGIN
    COMMIT TRAN -- Never makes it here cause of the ROLLBACK
    SELECT 'After COMMIT TRAN', @@TRANCOUNT
END

SELECT TOP 5 au_id FROM titleauthor

In this example, execution never reaches the out

COMMIT TRAN 
because the ROLLBACK TRAN reverses all transactions currently in progress and sets @@TRANCOUNT to 0. Unless ROLLBACK TRAN is called with a save point,
ROLLBACK 
TRAN 
always rolls back all transactions and sets
@@TRANCOUNT 
to 0, regardless of the context in which it's called.

SAVE TRAN and Save Points

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the

@@TRANCOUNT 
value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by @@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRAN without a specific name will always roll back the entire transaction.

The following script demonstrates how savepoints can be used :

SQL
USE pubs
SELECT 'Before BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 0

BEGIN TRAN main
    SELECT 'After BEGIN TRAN main', @@TRANCOUNT
   -- The value of @@TRANCOUNT is 1
    DELETE sales
    SAVE TRAN sales  -- Mark a save point
    SELECT 'After SAVE TRAN sales', @@TRANCOUNT
   -- The value of @@TRANCOUNT is still 1

    BEGIN TRAN nested
        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT
        -- The value of @@TRANCOUNT is 2
        DELETE titleauthor
        SAVE TRAN titleauthor  -- Mark a save point
        SELECT 'After SAVE TRAN titleauthor', @@TRANCOUNT
        -- The value of @@TRANCOUNT is still 2
    ROLLBACK TRAN sales

    SELECT 'After ROLLBACK TRAN sales', @@TRANCOUNT
     -- The value of @@TRANCOUNT is still 2

    SELECT TOP 5 au_id FROM titleauthor

IF (@@TRANCOUNT > 0) BEGIN
    ROLLBACK TRAN
    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT
    -- The value of @@TRANCOUNT is 0 because 
    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT 
    -- to 0.
END
    
SELECT TOP 5 au_id FROM titleauthor

Error Handling

The examples presented here are specific to stored procedures as they are the desired method of interacting with a database. When an error is encountered within a stored procedure, the best you can do is halt the sequential processing of the code and either branch to another code segment in the procedure or return processing to the calling application. The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the

@@ERROR 
value. Most of the time, you'll want to test for changes in
@@ERROR 
right after any INSERT, UPDATE, or
DELETE 
statement.

SQL
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11), 
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) BEGIN
        PRINT 'Unexpected error occurred!'
        ROLLBACK TRAN
        RETURN 1
    END

COMMIT TRAN

RETURN 0

This kind of solution contains substantial repetition especially if your business logic requires more than two Transact-SQL statements to be implemented. A more elegant solution is to group codes into a generic error handling procedure:

SQL
CREATE PROCEDURE addTitle(@title_id VARCHAR(6), @au_id VARCHAR(11),
                          @title VARCHAR(20), @title_type CHAR(12))
AS

BEGIN TRAN
    INSERT titles(title_id, title, type)
    VALUES (@title_id, @title, @title_type)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

    INSERT titleauthor(au_id, title_id)
    VALUES (@au_id, @title_id)

    IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
RETURN 1

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralTranscations Pin
Member 858344113-Jul-17 1:23
Member 858344113-Jul-17 1:23 
PraiseGreat Article Pin
Member 132498308-Jun-17 21:52
Member 132498308-Jun-17 21:52 
GeneralMy vote of 5 Pin
Member 938649121-Feb-17 8:58
Member 938649121-Feb-17 8:58 
GeneralMy vote of 1 Pin
Member 994559524-Jan-17 18:35
Member 994559524-Jan-17 18:35 
GeneralNice article Pin
Member 81170394-Nov-14 2:57
Member 81170394-Nov-14 2:57 
GeneralMessage Closed Pin
7-Oct-14 10:08
Member 110594567-Oct-14 10:08 
GeneralNice Article Pin
Vishal_00715-Sep-14 22:40
Vishal_00715-Sep-14 22:40 
GeneralMy vote of 3 Pin
Piyush K Patel27-Jan-14 23:00
professionalPiyush K Patel27-Jan-14 23:00 
GeneralMy vote of 5 Pin
Jigar Sangoi15-Sep-13 3:10
Jigar Sangoi15-Sep-13 3:10 
GeneralMy vote of 5 Pin
silvercr0w13-Aug-13 7:00
silvercr0w13-Aug-13 7:00 
GeneralMy vote of 5 Pin
seanmir25-Dec-12 0:06
seanmir25-Dec-12 0:06 
GeneralMy vote of 5 Pin
Kushal Patel27-Oct-12 19:30
Kushal Patel27-Oct-12 19:30 
QuestionNice articule Pin
ingcarlosmoya16-Oct-12 7:25
ingcarlosmoya16-Oct-12 7:25 
GeneralMy vote of 5 Pin
Tinoy Jameson Malayil13-Sep-12 20:03
Tinoy Jameson Malayil13-Sep-12 20:03 
GeneralMy vote of 5 Pin
Photon_2-Jul-12 18:52
Photon_2-Jul-12 18:52 
GeneralMy vote of 5 Pin
Mukul0038-May-12 9:59
professionalMukul0038-May-12 9:59 
GeneralVery Good explanation Pin
Mukul0038-May-12 9:59
professionalMukul0038-May-12 9:59 
GeneralMy vote of 5 Pin
ProEnggSoft5-Mar-12 15:00
ProEnggSoft5-Mar-12 15:00 
GeneralMy vote of 5 Pin
zerocool182-Feb-12 1:57
zerocool182-Feb-12 1:57 
GeneralMy vote of 5 Pin
HoyaSaxa931-Feb-12 8:03
HoyaSaxa931-Feb-12 8:03 
QuestionThank you Pin
Ranganath Prasad11-Dec-11 21:58
Ranganath Prasad11-Dec-11 21:58 
GeneralMy vote of 5 Pin
Prasanth S23-Sep-11 22:38
Prasanth S23-Sep-11 22:38 
GeneralMy vote of 5 Pin
zhouwwwjing5-Apr-11 0:34
zhouwwwjing5-Apr-11 0:34 
GeneralMy vote of 4 Pin
smnabil30-Nov-10 23:42
smnabil30-Nov-10 23:42 
GeneralMy vote of 4 Pin
deepak maurya19-Aug-10 1:34
deepak maurya19-Aug-10 1:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.