XACT_STATE is a function that you invoke in the CATCH block to get the current transaction state. It returns 0 for no active transaction, 1 for active and committable, and –1 for active but uncommittable. To demonstrate the use of this function and how to determine the transaction state, I’ll use the code in Example 10-3.

BEGIN TRY

  BEGIN TRAN
    INSERT INTO dbo.Employees(empid, empname, mgrid)
      VALUES(3, 'Emp3', 1);
    /* other activity */
  COMMIT TRAN

  PRINT 'Code completed successfully.';

END TRY
BEGIN CATCH

  PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.';

  IF (XACT_STATE()) = -1
  BEGIN
    PRINT 'Transaction is open but uncommittable.';
    /* ...investigate data... */
    ROLLBACK TRAN; -- can only ROLLBACK
    /* ...handle the error... */
  END
  ELSE IF (XACT_STATE()) = 1
  BEGIN
    PRINT 'Transaction is open and committable.';
    /* ...handle error... */
    COMMIT TRAN; -- or ROLLBACK
  END
  ELSE
  BEGIN
    PRINT 'No open transaction.';
    /* ...handle error... */
  END

END CATCH

This code loads a row for employee 3 into the Employees table using an explicit transaction in a TRY block. The CATCH block checks the transaction state before determining a course of action. The first time you run the code in Example 10-3, there should be no errors, because it’s a valid new employee. Run the code a second time, and you will get the following output:

Error: 2627 found.
Transaction is open and committable.

Because a primary key violation is not considered a severe error, it neither completely breaks nor fails the transaction. Rather, the transaction remains open and committable. To see an example where the transaction fails, you can simply set XACT_ABORT to ON, and rerun the code in Example 10-3:

SET XACT_ABORT ON;
-- run code in listing 10-3
SET XACT_ABORT OFF;

This time, you get the output:

Error: 2627 found.
Transaction is open but uncommittable.

When writing exception-handling code in a stored procedure, you might want to choose how to react based on whether or not the procedure was invoked from within an outer explicit transaction. For example, let’s say that upon encountering an error in the stored procedure, you want to undo the procedure’s activity only if the procedure opened the transaction. You don’t want any side effects on an outer transaction. To achieve this, you use save points. Run the code in Example 10-4 to create the usp_AddEmp procedure, which adds a new employee into the Employees table based on user inputs.

IF OBJECT_ID('dbo.usp_AddEmp') IS NOT NULL
  DROP PROC dbo.usp_AddEmp;
GO

CREATE PROC dbo.usp_AddEmp
  @empid AS INT, @empname AS VARCHAR(25), @mgrid AS INT
AS

-- Save tran count aside
DECLARE @tc AS INT;
SET @tc = @@trancount;

-- If tran was already active, create a savepoint
IF @tc > 0
  SAVE TRAN S1;
-- If tran was not active, open a new one
ELSE
  BEGIN TRAN

BEGIN TRY;
  -- Modify data
  INSERT INTO dbo.Employees(empid, empname, mgrid)
     VALUES(@empid, @empname, @mgrid);
  -- If proc opened the tran, it's responsible for committing it
  IF @tc = 0
    COMMIT TRAN;

END TRY
BEGIN CATCH
    PRINT 'Error detected.';
    PRINT CASE XACT_STATE()
      WHEN 0 THEN 'No transaction is open.'
      WHEN 1 THEN 'Transaction is open and committable.'
      WHEN -1 THEN 'Transaction is open and uncommittable.'
    END;
    -- Proc opened tran
    IF @tc = 0
    BEGIN
      -- Can react differently based on tran state (XACT_STATE)
      -- In this case, say we just want to roll back
      IF XACT_STATE() <> 0
      BEGIN
        PRINT 'Rollback of tran opened by proc.';
        ROLLBACK TRAN
      END
    END
    -- Proc didn't open tran
    ELSE
    BEGIN
      IF XACT_STATE() = 1
      BEGIN
        PRINT 'Proc was invoked in an open tran. Roll back only proc''s activity.';
        ROLLBACK TRAN S1
      END
      ELSE IF XACT_STATE() = -1
        PRINT 'Proc was invoked in an open tran, but tran is uncommittable. Deferring
exception handling to caller.'
    END

    -- Raise error so that caller will determine what to do with
    -- the failure in the proc
    DECLARE
      @ErrorMessage  NVARCHAR(400),
      @ErrorSeverity INT,
      @ErrorState    INT;
    SELECT
      @ErrorMessage  = ERROR_MESSAGE(),
      @ErrorSeverity = ERROR_SEVERITY(),
      @ErrorState    = ERROR_STATE();
    RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
GO

The procedure starts by saving the value of @@trancount in the local variable @tc. The @tc variable will tell you whether the procedure was invoked from an outer transaction (@tc > 0) or not (@tc = 0). If invoked from an outer transaction, the procedure will just mark a save point so that it would be able to undo only its own activity upon failure. If no transaction was open, the procedure simply opens a new one.

Then the procedure issues the INSERT statement within a TRY block and commits the transaction if the procedure opened it.

The CATCH block deals separately with a case where the procedure opened the transaction and a case where it didn’t. If the former situation occurs, the code checks whether there’s still a transaction open (XACT_STATE() <> 0) and simply rolls it back. Of course, you might want to react differently based on the transaction state. If the latter situation occurs, the code checks whether the transaction is open and committable, and in such a case rolls it back. If it’s open and not committable, you’re not allowed to roll back a transaction to a save point, so the code constructs an error message and throws it to the caller.

To test the procedure, first clear the Employees table:

TRUNCATE TABLE dbo.Employees;

Next run the following code twice, but not within an explicit transaction:

EXEC usp_AddEmp @empid = 1, @empname = 'Emp1', @mgrid = NULL;

The first run succeeds. The second run produces the following output:

Error detected.
Transaction is open and committable.
Rollback of tran opened by proc.
Msg 50000, Level 14, State 1, Procedure usp_AddEmp, Line 66
Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object
'dbo.Employees'.

Now run it again, but this time within an explicit transaction:

BEGIN TRAN
  EXEC usp_AddEmp @empid = 1, @empname = 'Emp1', @mgrid = NULL;
ROLLBACK

You get the following output:

Error detected.
Transaction is open and committable.
Proc was invoked in an open tran. Roll back only proc's activity.
Msg 50000, Level 14, State 1, Procedure usp_AddEmp, Line 66
Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object
'dbo.Employees'.

This time the procedure identified that it was invoked from an outer transaction, and upon error, rolled back only its own activity (to the savepoint). To demonstrate a failed transaction, set XACT_ABORT ON and try again:

SET XACT_ABORT ON;

BEGIN TRAN
  EXEC usp_AddEmp @empid = 1, @empname = 'Emp1', @mgrid = NULL;
ROLLBACK

SET XACT_ABORT OFF;

You get the following output:

Error detected.
Transaction is open and uncommittable.
Proc was invoked in an open tran, but tran is uncommittable. Deferring exception handling to
 caller.
Msg 50000, Level 14, State 1, Procedure usp_AddEmp, Line 66
Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object
'dbo.Employees'.

This time the procedure identified a failed transaction opened by the caller, so it deferred the exception handling to the caller.

In this final section about exception handling, I’ll demonstrate how you can now trap deadlock errors and apply retry logic for them. In SQL Server 2000, you couldn’t trap a deadlock error with T-SQL. I’ll also show examples that deal with update conflicts detected when you work with the new snapshot isolation level. Here as well, as in a deadlock case, you might want to retry several times before you deem the activity a goner.

I’ll use simple T1 and T2 tables, which you create and populate by running the following code:

IF OBJECT_ID('dbo.T1') IS NOT NULL
  DROP TABLE dbo.T1;
IF OBJECT_ID('dbo.T2') IS NOT NULL
  DROP TABLE dbo.T2;
GO

CREATE TABLE dbo.T1(col1 INT);
INSERT INTO dbo.T1 VALUES(1);

CREATE TABLE dbo.T2(col1 INT);
INSERT INTO dbo.T2 VALUES(1);

We will look at two processes that access tables T1 and T2. Those processes will always succeed individually, but will cause a deadlock if run concurrently, because they access T1 and T2 in opposite orders.

First examine the code in Example 10-5, and for now, ignore the mention of the snapshot isolation level. Focus only on the deadlock treatment here.

SET NOCOUNT ON;
USE testdb;
GO

-- SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SET LOCK_TIMEOUT 30000;

DECLARE @retry AS INT, @i AS INT, @j AS INT, @maxretries AS INT;
SELECT @retry = 1, @i = 0, @maxretries = 3;

WHILE @retry = 1 AND @i <= @maxretries
BEGIN
  SET @retry = 0;
  BEGIN TRY
    BEGIN TRAN
      SET @j = (SELECT SUM(col1) FROM dbo.T1);
      WAITFOR DELAY '00:00:05';
      UPDATE dbo.T1 SET col1 = col1 + 1;
      WAITFOR DELAY '00:00:05';
      SET @j = (SELECT SUM(col1) FROM dbo.T2);
    COMMIT TRAN
    PRINT 'Transaction completed successfully.';
  END TRY
  BEGIN CATCH
    -- Lock timeout
    IF ERROR_NUMBER() = 1222
    BEGIN
      PRINT 'Lock timeout detected.';
      IF XACT_STATE() <> 0 ROLLBACK;
    END
    -- Deadlock / Update conflict
    ELSE IF ERROR_NUMBER() IN (1205, 3960)
    BEGIN
      PRINT CASE ERROR_NUMBER()
              WHEN 1205 THEN 'Deadlock'
              WHEN 3960 THEN 'Update conflict'
            END + ' detected.';
      IF XACT_STATE() <> 0 ROLLBACK;
      SELECT @retry = 1, @i = @i + 1;
      IF @i <= @maxretries
      BEGIN
        PRINT 'Retry #' + CAST(@i AS VARCHAR(10)) + '.'
        WAITFOR DELAY '00:00:05';
      END
    END
    ELSE
    BEGIN
      PRINT 'Unhandled error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
        + ', ' + ERROR_MESSAGE();
      IF XACT_STATE() <> 0 ROLLBACK;
    END
  END CATCH
END

IF @i > @maxretries
  PRINT 'Failed ' + CAST(@maxretries AS VARCHAR(10)) + ' retries.';

The TRY block essentially runs a SELECT against T1, waits 5 seconds, runs an UPDATE against T1, waits 5 seconds, and then issues a SELECT against T2. Notice that the code runs in a loop, with a condition based on two flags: retry required (@retry = 1), and number of retries is smaller than or equal to a given maximum (@i <= @maxretries). The @retry flag is initialized with 1, @i with 0, and @maxretries with 3, so of course, the code runs at least once.

The TRY block then sets @retry to 0 so that if all goes well, there won’t be another retry. If an error is generated, and it’s one you want to apply retry logic to (for example, deadlock, update conflict), in the CATCH block you set the @retry flag to 1, increment @i, and enter a small delay. That’s basically it.

Examine the CATCH block and you will see different treatment for different errors. In a case of a lock timeout error (1222), there’s no point in a retry, so you simply roll back the transaction if one is open. As for deadlocks and update conflicts, you do apply retry after rolling back the existing transaction (if one is open). Run the code in Example 10-5 (calling the connection: connection 1). The code should complete after about 10 seconds with no problem, producing the following output:

Transaction completed successfully.

The code in Example 10-6 is similar to the one in Example 10-5, only with a reversed access order to the tables.

SET NOCOUNT ON;
USE testdb;
GO

SET LOCK_TIMEOUT 30000;

DECLARE @retry AS INT, @i AS INT, @j AS INT, @maxretries AS INT;
SELECT @retry = 1, @i = 0, @maxretries = 3;

WHILE @retry = 1 AND @i <= @maxretries
BEGIN
  SET @retry = 0;
  BEGIN TRY
    BEGIN TRAN
      SET @j = (SELECT SUM(col1) FROM dbo.T2);
      WAITFOR DELAY '00:00:05';
      UPDATE dbo.T2 SET col1 = col1 + 1;
      WAITFOR DELAY '00:00:05';
      SET @j = (SELECT SUM(col1) FROM dbo.T1);
    COMMIT TRAN
    PRINT 'Transaction completed successfully.';
  END TRY
  BEGIN CATCH
    -- Lock timeout
    IF ERROR_NUMBER() = 1222
    BEGIN
      PRINT 'Lock timeout detected.';
      IF XACT_STATE() <> 0 ROLLBACK;
    END
    -- Deadlock / Update conflict
    ELSE IF ERROR_NUMBER() IN (1205, 3960)
    BEGIN
      PRINT CASE ERROR_NUMBER()
              WHEN 1205 THEN 'Deadlock'
              WHEN 3960 THEN 'Update conflict'
            END + ' detected.';
      IF XACT_STATE() <> 0 ROLLBACK;
      SELECT @retry = 1, @i = @i + 1;
      IF @i <= @maxretries
      BEGIN
        PRINT 'Retry #' + CAST(@i AS VARCHAR(10)) + '.'
        WAITFOR DELAY '00:00:05';
      END
    END
    ELSE
    BEGIN
      PRINT 'Unhandled error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10))
        + ', ' + ERROR_MESSAGE();
      IF XACT_STATE() <> 0 ROLLBACK;
    END
  END CATCH
END

IF @i > @maxretries
  PRINT 'Failed ' + CAST(@maxretries AS VARCHAR(10)) + ' retries.';

Open a new connection (call it connection 2), and have the code from Example 10-6 ready to run. Run the code in both connections, and try to start the second very close to the first (within 5 seconds). One of them will finish successfully, while the other will face a deadlock and enter retry logic. In my case, it was connection 2, which generated the following output:

Deadlock detected.
Retry #1.
Transaction completed successfully.

Naturally, once connection 2 was deadlocked and released the locks, connection 1 could finish and release its own locks. Connection 2 waited a bit, tried again, and then was successful. To demonstrate exceeding the maximum number of retries you specified, open a third connection (call it connection 3) and have the following code ready in it:

SET NOCOUNT ON;
USE testdb;
GO

SET LOCK_TIMEOUT 30000;
DECLARE @j AS INT;

BEGIN TRAN

  UPDATE dbo.T2 SET col1 = col1 + 1;
  UPDATE dbo.T2 SET col1 = col1 + 1;
  UPDATE dbo.T2 SET col1 = col1 + 1;

  WAITFOR DELAY '00:00:05';

  WHILE 1 = 1
  BEGIN
    SET @j = (SELECT SUM(col1) FROM dbo.T1);
    WAITFOR DELAY '00:00:01';
  END

This code will keep an exclusive lock on T2 as soon as the first UPDATE takes place, and then in a loop, every second, it will request a shared lock on T1 to read. If you run it concurrently with the code in connection 1, you should get repeated deadlocks in connection 1. So first run the code in connection 1, and then immediately start the code in connection 3. After about a minute, you should get the following output in connection 1:

Deadlock detected.
Retry #1.
Deadlock detected.
Retry #2.
Deadlock detected.
Retry #3.
Deadlock detected.
Failed 3 retries.

Don’t forget to stop the activity in connection 3 and roll back the transaction when you’re done:

ROLLBACK

Update conflicts can occur when a transaction running in the snapshot isolation level reads a row at one point in time and then tries to modify the row at a later point. If SQL Server identifies that another transaction changed the row in between those two points in time, a update conflict occurs. Your transaction might be making calculations based on values it got by first reading the data, and later using the result of the calculation to update the data. If someone modifies the data between the time you first read it and the time you modify it, your modification might not be logically valid anymore.

One feature of the new snapshot isolation level is that it detects such update conflicts for you automatically. This allows you to use optimistic concurrency control. If you read and then modify a resource, and no one else modified the resource in between, the modification will work smoothly. But if someone did modify the resource in between, when you try to modify the resource SQL Server will detect the conflict and terminate your transaction. Typically, you want to retry the transaction in such a case. The logic is so similar to retrying in a case of a deadlock that there’s nothing really much to add.

To work with the snapshot isolation level, you must first enable it for the current database:

ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON;

Go to connection 1, and remove the comment characters (the two hyphens) from the statement that sets the isolation level to snapshot.

Now open a fourth connection (call it connection 4), and have the following code ready:

SET NOCOUNT ON;
USE testdb;
GO

SET LOCK_TIMEOUT 30000;

WHILE 1 = 1
BEGIN
  UPDATE dbo.T1 SET col1 = col1 + 1;
  WAITFOR DELAY '00:00:01';
END

This code simply issues an UPDATE against T1 every second. Now run the code in connection 1, and immediately start the code in connection 4 as well. The code in connection 1 keeps reading from T1, waiting 5 seconds, and then writing to T1. Because connection 4 changes T1 every second, connection 1 will encounter plenty of update conflicts. Feel free to stop the execution in connection 4 from time to time to see that connection 1 at some point will finish successfully. If you just allow connection 4 to keep running, after three retries connection 1 will produce the following output:

Update conflict detected.
Retry #1.
Update conflict detected.
Retry #2.
Update conflict detected.
Retry #3.
Update conflict detected.
Failed 3 retries.

Don’t forget to stop the activity in connection 4 when you’re done. At this point, you can close all connections.