Transactions, TRY...CATCH and XACT_ABORT – SQL Server | Lesson 24

Published / updated4 Oct 2026

Introduction

You have used BEGIN TRANSACTION and ROLLBACK since Lesson 10, mostly to undo tests. In this lesson, you learn what a transaction really guarantees, and how to write code that is truly all or nothing.

A transaction is a group of changes that succeed together or fail together. The fee payment is the perfect example: a payment row without an updated invoice is worse than no payment at all.

By the end of this lesson, you will be able to:

Explain the four ACID properties with SMS examples.
Start, commit, and roll back transactions.
Explain why a transaction alone does not undo a failed statement's neighbours.
Write the TRY...CATCH pattern and use SET XACT_ABORT ON.
Read @@TRANCOUNT, and avoid the nested rollback trap with procedures.

Why this matters

Recording a payment touches two tables. If the server saves the payment and then fails before updating the invoice, the parent has paid but the school says they owe money. The next reminder letter is an angry phone call.

Most developers believe "I used a transaction, so I am safe". The first demo in this lesson shows that this is not always true, and the rest shows the patterns that make it true.

The concept

Example: ACID

Every transaction promises four things, known as ACID:

Property	Meaning	SMS example
Atomicity	All changes happen, or none	Payment and invoice status saved together
Consistency	Rules hold before and after	Constraints and triggers still pass
Isolation	Other sessions do not see half-done work	Nobody reads the payment before the status update (Lesson 25)
Durability	Committed changes survive a crash	After `COMMIT`, a power cut cannot lose the payment

Think of handing over a school admission file. Either the whole file, with form, certificates, and fee receipt, goes into the cabinet, or the clerk hands it all back. A file with half its pages is never filed.

Example: the three statements

Statement	Effect
`BEGIN TRANSACTION`	Start grouping changes
`COMMIT TRANSACTION`	Make all changes permanent
`ROLLBACK TRANSACTION`	Undo every change since `BEGIN`

Without BEGIN TRANSACTION, each statement is its own transaction and is committed on its own. This is called autocommit, and it is SQL Server's default.

Example: a transaction alone is not enough

By default, when one statement fails inside a transaction, SQL Server undoes that statement only. The transaction stays open, with the earlier statements still in it. If your code then runs COMMIT, half the work is saved.

The fixes are TRY...CATCH and SET XACT_ABORT ON, shown in the demos below. Both make sure that a failure anywhere means nothing is saved.

Apply it to the SMS

Each demo records a payment on Kabir's invoice and then sets an invalid status, Done, which CK_FeeInvoices_Status refuses. Every demo ends with the data unchanged.

Show queries/024-transactions.sql

queries/024-transactions.sql
-- Lesson 24: transactions. Every demo leaves the data unchanged.
-- Each demo records a payment for Kabir's invoice (3) and then tries to set an
-- invalid status 'Done', which the CHECK constraint refuses.
USE SchoolDb;
GO

-- 1. A transaction alone is not enough: the failed statement is undone,
--    but the INSERT before it stays inside the transaction.
BEGIN TRANSACTION;

INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode)
VALUES ('RCP-DEMO-000001', 3, 10000, SYSUTCDATETIME(), 'Cash');

UPDATE dbo.FeeInvoices SET Status = 'Done' WHERE Id = 3;  -- fails: CHECK constraint

SELECT COUNT(*) AS DemoPaymentsInsideTransaction
FROM dbo.FeePayments
WHERE ReceiptNumber LIKE 'RCP-DEMO-%';

ROLLBACK TRANSACTION;  -- a COMMIT here would have saved half the work
GO

-- 2. TRY...CATCH: any error jumps to CATCH, which rolls everything back.
BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode)
    VALUES ('RCP-DEMO-000002', 3, 10000, SYSUTCDATETIME(), 'Cash');

    UPDATE dbo.FeeInvoices SET Status = 'Done' WHERE Id = 3;  -- fails

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

    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

SELECT COUNT(*) AS DemoPaymentsAfterCatch
FROM dbo.FeePayments
WHERE ReceiptNumber LIKE 'RCP-DEMO-%';
GO

-- 3. SET XACT_ABORT ON: any error cancels the whole transaction automatically.
SET XACT_ABORT ON;

BEGIN TRANSACTION;

INSERT INTO dbo.FeePayments (ReceiptNumber, FeeInvoiceId, Amount, PaidOn, PaymentMode)
VALUES ('RCP-DEMO-000003', 3, 10000, SYSUTCDATETIME(), 'Cash');

UPDATE dbo.FeeInvoices SET Status = 'Done' WHERE Id = 3;  -- fails, and ends the batch

COMMIT TRANSACTION;  -- never reached
GO

SET XACT_ABORT OFF;

SELECT
    @@TRANCOUNT AS OpenTransactions,
    (SELECT COUNT(*) FROM dbo.FeePayments WHERE ReceiptNumber LIKE 'RCP-DEMO-%') AS DemoPaymentsAfterXactAbort;
GO

-- 4. @@TRANCOUNT counts open transactions. An inner COMMIT only lowers the count;
--    one ROLLBACK undoes everything.
BEGIN TRANSACTION;
BEGIN TRANSACTION;
SELECT @@TRANCOUNT AS AfterTwoBegins;
COMMIT TRANSACTION;
SELECT @@TRANCOUNT AS AfterOneCommit;
ROLLBACK TRANSACTION;
SELECT @@TRANCOUNT AS AfterRollback;
GO

Example: demo 1, the partial save

The UPDATE fails with error 547, but the count inside the transaction shows the payment is still there:

DemoPaymentsInsideTransaction
1

The script ends with ROLLBACK. Had it ended with COMMIT, the payment would be saved and the invoice left unchanged: exactly the bug from "Why this matters".

Example: demo 2, TRY...CATCH

TRY...CATCH sends any error to the CATCH block. There, you roll back and report:

Example
BEGIN TRY
    BEGIN TRANSACTION;
    -- the changes
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;  -- pass the original error on to the caller
END CATCH;

The demo shows the error in its CATCH block instead of re-throwing it:

ErrorNumber	ErrorMessage
547	The UPDATE statement conflicted with the CHECK constraint "CK_FeeInvoices_Status". ...

DemoPaymentsAfterCatch is 0: the payment was rolled back together with the failed update.

ERROR_NUMBER() and ERROR_MESSAGE() work only inside CATCH. A bare THROW; inside CATCH passes the original error to the caller unchanged, which is usually what you want.

Example: demo 3, XACT_ABORT

SET XACT_ABORT ON tells SQL Server: on any error, roll back the whole transaction and stop. No CATCH is needed for the rollback:

OpenTransactions	DemoPaymentsAfterXactAbort
0	0

usp_RecordFeePayment in Lesson 22 uses SET XACT_ABORT ON for this reason. Many teams use both: XACT_ABORT guarantees the rollback, and CATCH adds logging or a clearer message.

Example: demo 4, @@TRANCOUNT

@@TRANCOUNT counts open transactions. Each BEGIN TRANSACTION adds 1, and each COMMIT subtracts 1. But one ROLLBACK sets it straight to 0, undoing everything:

AfterTwoBegins	AfterOneCommit	AfterRollback
2	1	0

So SQL Server has no truly independent inner transactions. The inner COMMIT does not save anything; only the outermost COMMIT does, once the count drops back to zero.

Example: the nested rollback trap

This matters when a caller wraps a procedure call in its own transaction. Here, the caller starts a transaction, calls usp_RecordFeePayment with too much money, and catches the error:

Example
BEGIN TRANSACTION;
DECLARE @R VARCHAR(20);

BEGIN TRY
    EXEC dbo.usp_RecordFeePayment @FeeInvoiceId = 3, @Amount = 50000,
        @PaymentMode = 'Cash', @ReceiptNumber = @R OUTPUT;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrNo, @@TRANCOUNT AS OpenTransactionsAfterError;
END CATCH;

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

ErrNo	OpenTransactionsAfterError
50013	0

The procedure's ROLLBACK undid the caller's transaction too. Any work the caller did before the call is gone, and a later COMMIT in the caller would fail. Rule: after catching an error from a procedure, always check @@TRANCOUNT before doing anything else.

Example: savepoints

A savepoint marks a point inside a transaction that you can roll back to, without undoing everything:

Example
BEGIN TRANSACTION;

UPDATE dbo.Guardians SET Occupation = N'Pilot' WHERE Id = 2;
SAVE TRANSACTION BeforePhone;
UPDATE dbo.Guardians SET Phone = '9876549999' WHERE Id = 2;
ROLLBACK TRANSACTION BeforePhone;  -- undo only the phone change

SELECT Occupation, Phone, @@TRANCOUNT AS StillOpen FROM dbo.Guardians WHERE Id = 2;

ROLLBACK TRANSACTION;

Occupation	Phone	StillOpen
Pilot	9876540102	1

The occupation change survived, the phone change was undone, and the transaction is still open. The final ROLLBACK then undoes the rest. Savepoints are rare in application code, but useful in long batch jobs that can skip one bad record and carry on.

Example: how COMMIT survives a power cut

Every change is first written to the transaction log, a file next to the data file. COMMIT returns only after the log records are safely on disk. The data pages themselves can be written later.

If the server loses power, SQL Server reads the log when it starts again: committed work is redone, and unfinished work is undone. That is how durability works, and why the log file must sit on reliable storage.

Example: a forgotten open transaction

The most common transaction problem in SSMS is human: someone runs BEGIN TRANSACTION and an UPDATE, then goes to lunch. Their locks stay, and other users wait. DBCC OPENTRAN shows the oldest open transaction in the current database:

Example
DBCC OPENTRAN;

It reports the session number (SPID) and the start time of the oldest open transaction. SSMS also warns you when you close a window with an open transaction. Answer the question it asks; do not just click OK.

Example: database or application transactions?

Both the database and C# can control transactions. In this course:

Situation	Where the transaction lives
One business action in one procedure	Inside the procedure, as in `usp_RecordFeePayment`
Several repository calls in the API	In C#, with one database transaction around them (Module 9)
Two different databases or services	Avoid if possible; use a design that tolerates delay (Module 13)

Whichever side starts it must also finish it: never begin in C# and commit in SQL.

Example: keep transactions short

A transaction holds locks until it ends (Lesson 25). Other users who need the same rows wait. So:

Never wait for a user, a file, or a web call inside a transaction.
Read and validate first, then open the transaction for the writes only.
Commit as soon as the writes are done.

Common mistakes

Committing after an error. Half the work is saved. Fix: TRY...CATCH with rollback, or SET XACT_ABORT ON.
Swallowing errors in CATCH. The caller thinks it worked. Fix: end CATCH with THROW;.
Assuming an inner COMMIT saves something. It only lowers @@TRANCOUNT. Fix: only the outermost transaction decides.
Ignoring @@TRANCOUNT after a procedure error. The caller's transaction may be gone. Fix: check it before continuing.
Long transactions. Locks block other users. Fix: keep only writes inside, and commit quickly.
Leaving a transaction open in SSMS. Other users wait on your locks for hours. Fix: finish every manual transaction, and check with DBCC OPENTRAN when users report that saving "hangs".

In real projects

From 16 years of .NET projects

The "paid but showing unpaid" bug is real. I found it once in a fee system that ran two separate statements without a transaction.

A timeout between them left 37 families marked unpaid, and the school sent them reminder letters. The fix took ten minutes; the apologies took a week.

Since then, every procedure I approve that changes more than one row starts with SET XACT_ABORT ON, and every CATCH block ends with THROW;. Two lines, and the most common transaction bugs simply cannot happen.

Best practices

Put every multi-statement change in one transaction.
Use SET XACT_ABORT ON in procedures that change data.
Use TRY...CATCH: roll back if @@TRANCOUNT > 0, then THROW;.
Keep transactions short: validate first, write fast, commit.
Check @@TRANCOUNT after any error caught from a procedure.
Start and finish each transaction on the same side: SQL or C#, never both.

Recap

A transaction makes several changes all-or-nothing; ACID describes its promises.
Without BEGIN TRANSACTION, every statement commits on its own.
A failed statement alone does not roll back the rest of the transaction.
TRY...CATCH and SET XACT_ABORT ON make failure undo everything.
@@TRANCOUNT counts open transactions; one ROLLBACK ends them all.
Savepoints undo part of a transaction; the log makes COMMIT durable.

Homework

Library module task: Write usp_ReturnBook. It sets ReturnedOn and calculates the fine (2 per day after the due date) in a transaction, inside TRY...CATCH.

If there is no open issue with that Id, throw error 50021. In CATCH, roll back and re-throw.

A correct result looks like: a copy due on 24 July and returned on 28 July gets a fine of 8, and the Lesson 23 trigger sets the copy back to Available.

Show the solution

library-assignment/024-library-return-procedure.sql
-- Library homework, Lesson 24: return a copy and charge the late fine, all or nothing.
USE SchoolDb;
GO

CREATE OR ALTER PROCEDURE dbo.usp_ReturnBook
    @BookIssueId INT,
    @ReturnedOn  DATE
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.BookIssues
        SET ReturnedOn = @ReturnedOn,
            FineAmount = CASE
                WHEN @ReturnedOn > DueOn THEN DATEDIFF(DAY, DueOn, @ReturnedOn) * 2
                ELSE 0
            END
        WHERE Id = @BookIssueId
          AND ReturnedOn IS NULL;

        IF @@ROWCOUNT = 0
        BEGIN
            THROW 50021, 'No open issue with this Id.', 1;
        END;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        THROW;  -- pass the original error to the caller
    END CATCH;
END;
GO

THROW inside TRY jumps to CATCH, which rolls back and re-throws the same error to the caller.

Quiz

Which ACID property means 'all changes happen, or none'?
1. Atomicity
2. Consistency
3. Isolation
4. Durability
Show answer
Answer: A. Atomicity Atomicity: the transaction is one indivisible unit, like a sealed admission file.
Inside a transaction, the second of two statements fails. With default settings, what happens to the first?
1. It is rolled back automatically
2. It stays in the open transaction until you commit or roll back
3. It is committed immediately
4. SQL Server restarts
Show answer
Answer: B. It stays in the open transaction until you commit or roll back Only the failed statement is undone; you must handle the rest.
What does SET XACT_ABORT ON do?
1. Speeds up transactions
2. Rolls back the whole transaction on any error
3. Disables constraints
4. Commits after every statement
Show answer
Answer: B. Rolls back the whole transaction on any error Any run-time error ends and rolls back the transaction.
After BEGIN TRAN, BEGIN TRAN, COMMIT, what is @@TRANCOUNT?
1. 0
2. 1
3. 2
4. 3
Show answer
Answer: B. 1 Each BEGIN adds 1, each COMMIT subtracts 1.
A procedure rolls back after an error. What happens to the caller's open transaction?
1. It is unaffected
2. It is rolled back too, and @@TRANCOUNT becomes 0
3. It is committed
4. It is paused
Show answer
Answer: B. It is rolled back too, and @@TRANCOUNT becomes 0 ROLLBACK undoes all open transactions, not just the procedure's.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: SQL Server interview questions.

Fresher (0-2 yrs)

F What is a transaction? concept

A group of changes that succeed together or fail together. BEGIN TRANSACTION starts it, COMMIT makes every change permanent, and ROLLBACK undoes them all. Recording a payment and updating the invoice status is one transaction.

See the example · Permalink

F What does ACID stand for? concept

Atomicity (all or nothing), Consistency (rules hold before and after), Isolation (others do not see half-done work), and Durability (committed changes survive a crash). Together they describe what a database transaction guarantees.

See the example · Permalink

F What is autocommit mode? concept

SQL Server's default: without BEGIN TRANSACTION, each statement is its own transaction and is committed as soon as it succeeds. Two related statements run this way are not protected; if the second fails, the first stays saved.

See the example · Permalink

Mid (3-5 yrs)

M Inside a transaction, the second of two statements fails. Is the first one undone? concept

Not by default. SQL Server undoes only the failed statement; the transaction stays open with the first change in it, and a later COMMIT saves it. Use TRY...CATCH with ROLLBACK, or SET XACT_ABORT ON, so any error undoes everything.

See the example · Permalink

M What does SET XACT_ABORT ON do, and why use it? concept

With it on, any run-time error rolls back the whole transaction and stops the batch. It guarantees that a failure never leaves a half-done transaction, even when a CATCH block is missing or a client times out. Procedures that change data should use it.

See the example · Permalink

M Write the standard TRY...CATCH pattern for a transaction. coding

BEGIN TRY BEGIN TRANSACTION; ...changes...; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; THROW; END CATCH;. The check avoids an error when no transaction is open, and THROW; passes the original error to the caller.

See the example · Permalink

Senior (6-9 yrs)

S Does SQL Server support nested transactions? concept

Not really. Each BEGIN TRANSACTION increases @@TRANCOUNT, and an inner COMMIT only decreases it; nothing is saved until the outermost COMMIT. A ROLLBACK anywhere undoes everything and sets the count to 0. Savepoints are the only way to undo part of a transaction.

How to answer: Explain @@TRANCOUNT with the 2, 1, 0 example, then the consequence: a procedure that rolls back destroys its caller's transaction. Show how callers should check @@TRANCOUNT after an error, and mention savepoints for partial undo.

See the example · Permalink

S Users say saving 'hangs' every afternoon. You suspect an open transaction. How do you find it? scenario

Run DBCC OPENTRAN in the database to see the oldest open transaction and its session, and query sys.dm_exec_requests for blocked sessions and who blocks them. Often it is an SSMS window with an uncommitted change, or an application path that never commits. Close or kill it, then fix the cause.

How to answer: Give the diagnosis steps with the exact tools, then the decision (contact the owner before killing a session), and the prevention: short transactions, XACT_ABORT, and alerts on long-running transactions.

See the example · Permalink

Architect / Lead (10+ yrs)

A A business action spans the database and an external payment service. How do you keep them consistent? design

A database transaction cannot include an external service. Record the intent first in the database, call the service with an idempotency key, then record the result; a background job retries or reconciles anything left pending. The design accepts brief inconsistency and always ends in a known state.

How to answer: Explain why distributed transactions are avoided, then describe the pattern (outbox or pending-state plus reconciliation). Use the SMS online payment as the example, and cover retries, idempotency, and monitoring.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-24-transactions (code repository coming soon)
Files: queries/024-transactions.sql, library-assignment/024-library-return-procedure.sql
Video: coming soon

What's next

Transactions protect one session's work. Next: what happens when many sessions touch the same rows at once, including blocking and deadlocks. Lesson 25: Isolation levels and deadlocks

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: ACID​

Example: the three statements​

Example: a transaction alone is not enough​

Apply it to the SMS​

Example: demo 1, the partial save​

Example: demo 2, TRY...CATCH​

Example: demo 3, XACT_ABORT​

Example: demo 4, @@TRANCOUNT​

Example: the nested rollback trap​

Example: savepoints​

Example: how COMMIT survives a power cut​

Example: a forgotten open transaction​

Example: database or application transactions?​

Example: keep transactions short​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: ACID

Example: the three statements

Example: a transaction alone is not enough

Apply it to the SMS

Example: demo 1, the partial save

Example: demo 2, TRY...CATCH

Example: demo 3, XACT_ABORT

Example: demo 4, @@TRANCOUNT

Example: the nested rollback trap

Example: savepoints

Example: how COMMIT survives a power cut

Example: a forgotten open transaction

Example: database or application transactions?

Example: keep transactions short

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next