TRY/CATCH, THROW and XACT_ABORT

T-SQL's error handling is easy to write and easy to get subtly wrong, because by default an error does not roll back the transaction it happens in.

Statement errors and XACT_ABORT

With the default SET XACT_ABORT OFF, many run-time errors (a constraint violation, a duplicate key, a divide by zero) abort only the statement that failed. The transaction stays open and committable, holding every earlier change. Code that carries on to COMMIT commits half the work: the debit without the credit. Not every error behaves this way: some, such as a failed conversion of 'abc' to INT, doom the transaction even with the setting off, and few people can list which ones do. That uncertainty is reason enough to turn the setting on.

With SET XACT_ABORT ON, a run-time error aborts the batch and dooms the transaction. Inside a TRY block, control passes to CATCH and XACT_STATE() returns -1: the transaction can only be rolled back.

XACT_STATE() returns 1 (an open, committable transaction), 0 (no transaction) or -1 (open but doomed). @@TRANCOUNT only says whether a transaction is open, not whether it can commit.

1CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL CHECK (balance >= 0));2INSERT INTO accounts VALUES (1, 100), (2, 0);

1DECLARE @log TABLE (setting NVARCHAR(20), error_number INT, xact_state INT, account_2_inside_tx INT);2DECLARE @mode INT = 0;3 4WHILE @mode <= 15BEGIN6  IF @mode = 0 SET XACT_ABORT OFF; ELSE SET XACT_ABORT ON;7  BEGIN TRY8    BEGIN TRAN;9      UPDATE accounts SET balance = balance + 500 WHERE id = 2;  -- succeeds10      UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- violates the CHECK11    COMMIT;12  END TRY13  BEGIN CATCH14    INSERT INTO @log15    VALUES (CASE @mode WHEN 0 THEN N'XACT_ABORT OFF' ELSE N'XACT_ABORT ON' END,16            ERROR_NUMBER(), XACT_STATE(),17            CASE WHEN XACT_STATE() = 1 THEN (SELECT balance FROM accounts WHERE id = 2) END);18    IF @@TRANCOUNT > 0 ROLLBACK;19  END CATCH;20  SET @mode += 1;21END;22 23SELECT l.setting, l.error_number, l.xact_state, l.account_2_inside_tx,24       (SELECT balance FROM accounts WHERE id = 2) AS account_2_after_rollback25FROM @log AS l26ORDER BY l.setting DESC;

The pattern

CREATE OR ALTER PROCEDURE dbo.transfer @from INT, @to INT, @amount INT
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE accounts SET balance = balance - @amount WHERE id = @from;
      UPDATE accounts SET balance = balance + @amount WHERE id = @to;
    COMMIT;
  END TRY
  BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;          -- re-raise the original error to the caller
  END CATCH;
END;

Swallowing the error in CATCH — logging it and returning normally — tells the caller everything worked.

What TRY/CATCH does not catch

• Errors with severity 10 or lower (those are messages).
• Compile errors and name-resolution errors in the same batch or scope. In a procedure or sp_executesql called from inside the TRY, they happen one level down and are caught.
• Errors that close the connection, or an attention signal sent by a client timeout. A client timeout leaves an open transaction behind unless XACT_ABORT is on, which is one more reason to set it.

THROW against RAISERROR

• THROW; with no arguments, inside CATCH, re-raises the original error with its original number. RAISERROR cannot do that.
• THROW 50001, N'message', 1; raises a custom error. Custom numbers must be 50000 or above, and severity is always 16.
• The statement before THROW must end with a semicolon.

1CREATE TABLE accounts (id INT PRIMARY KEY, balance INT NOT NULL);2INSERT INTO accounts VALUES (1, 30);3GO4CREATE PROCEDURE dbo.withdraw @id INT, @amount INT5AS6BEGIN7  SET NOCOUNT ON;8  SET XACT_ABORT ON;9  IF (SELECT balance FROM accounts WHERE id = @id) < @amount10    THROW 50001, N'Insufficient funds.', 1;11  UPDATE accounts SET balance = balance - @amount WHERE id = @id;12END;

1DECLARE @log TABLE (error_number INT, error_message NVARCHAR(200), error_procedure NVARCHAR(100), error_severity INT);2BEGIN TRY3  EXEC dbo.withdraw @id = 1, @amount = 50;4END TRY5BEGIN CATCH6  INSERT INTO @log VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_SEVERITY());7END CATCH;8SELECT l.*, (SELECT balance FROM accounts WHERE id = 1) AS balance_now FROM @log AS l;

Common mistakes

• No XACT_ABORT ON. The first example shows the transaction still committable after a failed update.
• CATCH without THROW. The error disappears and the caller reports success.
• ROLLBACK without checking @@TRANCOUNT. Rolling back when no transaction is open is itself an error (3903).
• One TRY around a whole loop when each item should succeed or fail on its own: the first failure ends the loop.

What interviewers ask

• What does SET XACT_ABORT ON change? A run-time error aborts the batch and dooms the transaction, so partial work cannot be committed.
• THROW or RAISERROR? THROW re-raises the original error and is simpler; RAISERROR formats messages and supports low severities.
• What does XACT_STATE() = -1 mean? The transaction is uncommittable; the only option is ROLLBACK.