Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 39 ยท Data Modification and Transactions

Transactions and Error Handling in T-SQL

Group SQL Server statements into transactions with BEGIN TRAN, COMMIT and ROLLBACK, handle errors with TRY...CATCH and THROW, and use XACT_ABORT and savepoints safely.

A transaction is a group of statements that succeed or fail together. Moving money between two accounts is the classic example: subtract from one, add to the other. If the server crashes between the two statements, you must not lose money. SQL Server guarantees this through the ACID properties: Atomic, Consistent, Isolated and Durable. The write-ahead transaction log is what makes it work.

This lesson covers the T-SQL commands, the TRY...CATCH error model and the habits that keep transactions from being left open. Locking and isolation have their own lesson, and the general idea is also in the SQL transactions lesson.

Autocommit, explicit and implicit transactions

By default SQL Server runs in autocommit mode: each statement is its own transaction and commits when it finishes. You group statements yourself with BEGIN TRANSACTION:

SQL
BEGIN TRANSACTION;

UPDATE dbo.Accounts SET Balance = Balance - 100 WHERE AccountId = 1;
UPDATE dbo.Accounts SET Balance = Balance + 100 WHERE AccountId = 2;

COMMIT TRANSACTION;

ROLLBACK TRANSACTION undoes everything since BEGIN. @@TRANCOUNT tells you how many BEGINs are open; it should be 0 when you leave your code. A forgotten open transaction keeps its locks and stops the log from being truncated, so it is one of the most damaging beginner mistakes.

(There is also SET IMPLICIT_TRANSACTIONS ON, where the first data statement silently opens a transaction. It is how some drivers behave; know it exists so you aren't surprised by locks.)

You can try the same commit and rollback ideas in the editor:

SQL Commit and rollback

Press Run (or Ctrl + Enter) to see the result here.

SQL A rolled-back change leaves no trace

Press Run (or Ctrl + Enter) to see the result here.

TRY...CATCH

T-SQL error handling looks like most languages: put risky code in BEGIN TRY, handle failures in BEGIN CATCH.

SQL
BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountId = @From;
    UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountId = @To;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;

    THROW;   -- re-raise the original error to the caller
END CATCH;

Inside CATCH you have these functions: ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE() and ERROR_PROCEDURE(). They only return values inside the CATCH block.

Things to know:

  • TRY...CATCH catches errors of severity 11 to 19 that don't close the connection. Syntax errors in the same batch and some compile errors (such as a missing table, if the plan is compiled earlier) escape it.
  • IF @@TRANCOUNT > 0 ROLLBACK is the standard guard, because the transaction might not have been started yet when the error occurred.
  • A bare THROW; re-raises the original error with its number and line. THROW 50001, 'Insufficient funds', 1; raises your own. The older RAISERROR still works, but THROW is simpler.

XACT_ABORT

Some errors (for example a constraint violation) abort only the single statement, and the batch carries on with the transaction still open. SET XACT_ABORT ON changes this: any run-time error rolls back the whole transaction and ends the batch. Put it at the top of procedures that use transactions:

SQL
SET XACT_ABORT ON;
SET NOCOUNT ON;

With XACT_ABORT ON, a failure also doesn't leave an uncommittable transaction behind when a client times out.

Nested transactions and savepoints

BEGIN TRAN inside a transaction just increments @@TRANCOUNT. Only the outermost COMMIT commits; any ROLLBACK rolls back everything. For a partial rollback, use a savepoint:

SQL
BEGIN TRANSACTION;
    INSERT INTO dbo.Orders (CustomerId) VALUES (7);
    SAVE TRANSACTION AfterOrder;

    INSERT INTO dbo.OrderLines (OrderId, ProductId) VALUES (SCOPE_IDENTITY(), 99);
    -- something went wrong with the line only
    ROLLBACK TRANSACTION AfterOrder;   -- undoes just the line
COMMIT TRANSACTION;                    -- the order stays

SQLite supports the same with SAVEPOINT and ROLLBACK TO:

SQL Savepoint stand-in

Press Run (or Ctrl + Enter) to see the result here.

Named transactions and good habits

  • Keep transactions short: do validation and calculation first, then open, change, commit.
  • Never wait for user input inside a transaction.
  • Access tables in the same order everywhere; it prevents most deadlocks.
  • Combine SET XACT_ABORT ON, TRY...CATCH and a guarded ROLLBACK.
  • Check @@TRANCOUNT before returning from a procedure; a mismatch raises error 266.
flowchart TD
  A["BEGIN TRANSACTION"] --> B["Statements run"]
  B --> C{"Error?"}
  C -- "no" --> D["COMMIT: changes are durable"]
  C -- "yes" --> E["CATCH: ROLLBACK"]
  E --> F["THROW to caller"]

The pattern every writing procedure should follow.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SQL Server do by default when you run a single INSERT with no BEGIN TRANSACTION?

    Autocommit mode wraps every statement in its own transaction.
  2. 2. In a nested BEGIN TRAN / BEGIN TRAN / COMMIT / ROLLBACK sequence, what is undone?

    An inner COMMIT only decrements @@TRANCOUNT. A ROLLBACK without a savepoint name undoes all work of the outermost transaction.
  3. 3. What does SET XACT_ABORT ON change?

    Without it, many errors only cancel one statement and leave the transaction open.
  4. 4. What does a bare THROW; inside a CATCH block do?

    THROW without arguments, valid only in CATCH, rethrows the caught error with its original number, message and line.
  5. 5. Why check IF @@TRANCOUNT > 0 before ROLLBACK in the CATCH block?

    Rolling back when no transaction is open raises another error and hides the original one.

Summary

  • Transactions make groups of statements atomic; autocommit is the default.
  • The standard pattern is SET XACT_ABORT ON, BEGIN TRY, BEGIN TRAN, work, COMMIT, then in CATCH a guarded ROLLBACK and THROW.
  • Only the outermost COMMIT commits; use SAVE TRANSACTION for partial rollbacks.
  • Keep transactions short and never leave one open.

Next: Isolation levels and locking