Transaction Management in SQL Server

A transaction = a group of statements that succeed or fail TOGETHER.

BEGIN TRAN [name];         -- start
COMMIT TRAN;               -- save permanently
ROLLBACK TRAN;             -- undo everything since BEGIN TRAN
SAVE TRAN savepointName;   -- a "checkpoint" you can roll back to
@@TRANCOUNT                -- how many BEGIN TRANs are open
XACT_STATE()               -- 1 = OK, 0 = none, -1 = doomed
SET XACT_ABORT ON          -- any runtime error -> rollback whole transaction

Modes:

• Autocommit (default) : every single statement is its own transaction
• Explicit : you write BEGIN TRAN ... COMMIT
• Implicit : SET IMPLICIT_TRANSACTIONS ON (Oracle style; you must COMMIT)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables: BankAccounts and TransferHistory

DROP TABLE IF EXISTS dbo.TransferHistory;
DROP TABLE IF EXISTS dbo.BankAccounts;

CREATE TABLE dbo.BankAccounts
(
    AccountNo   VARCHAR(10)   PRIMARY KEY,
    HolderName  VARCHAR(50)   NOT NULL,
    Balance     DECIMAL(12,2) NOT NULL CHECK (Balance >= 0),   -- no overdraft
    AccountType VARCHAR(10)   NOT NULL,
    IsFrozen    BIT           NOT NULL DEFAULT 0
);

INSERT INTO dbo.BankAccounts (AccountNo, HolderName, Balance, AccountType, IsFrozen) VALUES
('SB1001', 'Ravi Kumar',    50000, 'Savings', 0),
('SB1002', 'Sita Devi',     20000, 'Savings', 0),
('SB1003', 'John Peter',     5000, 'Savings', 0),
('CA2001', 'Ravi Traders', 250000, 'Current', 0),
('SB1004', 'Fatima Khan',   15000, 'Savings', 1);    -- frozen account

CREATE TABLE dbo.TransferHistory
(
    TransferID  INT IDENTITY(1,1) PRIMARY KEY,
    FromAccount VARCHAR(10)   NOT NULL,
    ToAccount   VARCHAR(10)   NOT NULL,
    Amount      DECIMAL(12,2) NOT NULL,
    Status      VARCHAR(10)   NOT NULL,
    Note        VARCHAR(200)  NULL,
    CreatedAt   DATETIME2(0)  NOT NULL DEFAULT SYSDATETIME()
);
GO

(5 rows affected)

Part A: COMMIT and ROLLBACK

Commit: changes are saved

BEGIN TRAN;
    UPDATE dbo.BankAccounts SET Balance = Balance + 1000 WHERE AccountNo = 'SB1003';
    SELECT 'Inside TRAN' AS Stage, @@TRANCOUNT AS TranCount, Balance FROM dbo.BankAccounts WHERE AccountNo = 'SB1003';
COMMIT TRAN;
SELECT 'After COMMIT' AS Stage, @@TRANCOUNT AS TranCount, Balance FROM dbo.BankAccounts WHERE AccountNo = 'SB1003';

(1 row affected)

Rollback: changes are undone

BEGIN TRAN;
    DELETE FROM dbo.BankAccounts WHERE AccountType = 'Savings';     -- oops!
    SELECT 'Inside TRAN (after delete)' AS Stage, COUNT(*) AS Accounts FROM dbo.BankAccounts;
ROLLBACK TRAN;
SELECT 'After ROLLBACK' AS Stage, COUNT(*) AS Accounts FROM dbo.BankAccounts;   -- all 5 are back
GO

(4 rows affected)

Part B: Autocommit Problem - why we need explicit transactions

-- Money transfer WITHOUT a transaction: the 2nd statement fails (CHECK Balance >= 0)
-- but the 1st one is already saved -> money "appears" from nowhere!
DECLARE @Before DECIMAL(12,2) = (SELECT SUM(Balance) FROM dbo.BankAccounts);

BEGIN TRY
    UPDATE dbo.BankAccounts SET Balance = Balance + 9000 WHERE AccountNo = 'SB1002';   -- credit (autocommitted!)
    UPDATE dbo.BankAccounts SET Balance = Balance - 9000 WHERE AccountNo = 'SB1003';   -- debit fails (only 6000)
END TRY
BEGIN CATCH
    PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;

SELECT @Before AS TotalBefore, SUM(Balance) AS TotalAfter,
       SUM(Balance) - @Before AS MoneyCreatedFromNothing
FROM dbo.BankAccounts;

-- Fix the data back
UPDATE dbo.BankAccounts SET Balance = Balance - 9000 WHERE AccountNo = 'SB1002';
GO

(1 row affected)
(0 rows affected)
Error: The UPDATE statement conflicted with the CHECK constraint "CK__BankAccou__Balan__14D10B8B". The conflict occurred in database "SqlLearningDb", table "dbo.BankAccounts", column 'Balance'.

(1 row affected)

Part C: Safe Transfer Procedure (the correct way)

CREATE OR ALTER PROCEDURE dbo.usp_TransferMoney
    @From   VARCHAR(10),
    @To     VARCHAR(10),
    @Amount DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        IF @Amount <= 0                THROW 50301, 'Amount must be positive.', 1;
        IF @From = @To                 THROW 50302, 'Cannot transfer to the same account.', 1;
        IF EXISTS (SELECT 1 FROM dbo.BankAccounts WHERE AccountNo IN (@From, @To) AND IsFrozen = 1)
                                       THROW 50303, 'One of the accounts is frozen.', 1;

        BEGIN TRAN;
            UPDATE dbo.BankAccounts SET Balance = Balance - @Amount WHERE AccountNo = @From;
            IF @@ROWCOUNT = 0 THROW 50304, 'Source account not found.', 1;

            UPDATE dbo.BankAccounts SET Balance = Balance + @Amount WHERE AccountNo = @To;
            IF @@ROWCOUNT = 0 THROW 50305, 'Target account not found.', 1;

            INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status)
            VALUES (@From, @To, @Amount, 'SUCCESS');
        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;

        INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status, Note)
        VALUES (@From, @To, @Amount, 'FAILED', LEFT(ERROR_MESSAGE(), 200));
    END CATCH;
END;
GO

EXEC dbo.usp_TransferMoney 'SB1001', 'SB1002', 10000;   -- success
EXEC dbo.usp_TransferMoney 'SB1003', 'SB1001', 99999;   -- fails : CHECK (no overdraft)
EXEC dbo.usp_TransferMoney 'SB1001', 'SB1004', 500;     -- fails : frozen
EXEC dbo.usp_TransferMoney 'SB1001', 'XX9999', 500;     -- fails : target not found (debit rolled back)

SELECT * FROM dbo.BankAccounts;
SELECT * FROM dbo.TransferHistory;
GO

Part D: Nested Transactions and @@TRANCOUNT

SQL Server has NO real nested transactions:

• Inner COMMIT only decreases @@TRANCOUNT
• ANY ROLLBACK (without savepoint) rolls back EVERYTHING to the outermost BEGIN TRAN

BEGIN TRAN Outer1;
    PRINT CONCAT('After outer BEGIN : @@TRANCOUNT = ', @@TRANCOUNT);     -- 1
    UPDATE dbo.BankAccounts SET Balance = Balance + 1 WHERE AccountNo = 'SB1001';

    BEGIN TRAN Inner1;
        PRINT CONCAT('After inner BEGIN : @@TRANCOUNT = ', @@TRANCOUNT); -- 2
        UPDATE dbo.BankAccounts SET Balance = Balance + 1 WHERE AccountNo = 'SB1002';
    COMMIT TRAN Inner1;                                                    -- does NOT really commit
    PRINT CONCAT('After inner COMMIT: @@TRANCOUNT = ', @@TRANCOUNT);     -- 1

ROLLBACK TRAN;                                                             -- undoes BOTH updates
PRINT CONCAT('After ROLLBACK    : @@TRANCOUNT = ', @@TRANCOUNT);         -- 0
GO

After outer BEGIN : @@TRANCOUNT = 1
(1 row affected)
After inner BEGIN : @@TRANCOUNT = 2
(1 row affected)
After inner COMMIT: @@TRANCOUNT = 1
After ROLLBACK    : @@TRANCOUNT = 0

Part E: Savepoints (partial rollback)

BEGIN TRAN;
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status, Note) VALUES ('SB1001', 'SB1002', 1, 'TEST', 'step 1');
    SAVE TRAN AfterStep1;

    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status, Note) VALUES ('SB1001', 'SB1002', 2, 'TEST', 'step 2');
    SAVE TRAN AfterStep2;

    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status, Note) VALUES ('SB1001', 'SB1002', 3, 'TEST', 'step 3 - bad');

    ROLLBACK TRAN AfterStep2;      -- undo only step 3 (transaction is STILL open)
    PRINT CONCAT('@@TRANCOUNT after rollback to savepoint = ', @@TRANCOUNT);   -- still 1
COMMIT TRAN;                        -- step 1 and step 2 are saved

SELECT * FROM dbo.TransferHistory WHERE Status = 'TEST';
DELETE FROM dbo.TransferHistory WHERE Status = 'TEST';   -- clean up
GO

(1 row affected)
(1 row affected)
(1 row affected)
@@TRANCOUNT after rollback to savepoint = 1

(2 rows affected)

Part F: XACT_ABORT ON vs Off

XACT_ABORT Off (default): a failed statement is rolled back, but the transaction Continues

SET XACT_ABORT OFF;
BEGIN TRAN;
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, 'XA-OFF');
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, NULL);   -- error (NOT NULL)
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, 'XA-OFF');
COMMIT TRAN;
SELECT 'XACT_ABORT OFF' AS Mode, COUNT(*) AS RowsSaved FROM dbo.TransferHistory WHERE Status = 'XA-OFF';   -- 2 rows! (partial)
GO

(1 row affected)

XACT_ABORT ON: the error aborts the batch and rolls back the whole transaction

SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, 'XA-ON');
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, NULL);    -- error -> batch stops
    INSERT INTO dbo.TransferHistory (FromAccount, ToAccount, Amount, Status) VALUES ('A', 'B', 1, 'XA-ON');
COMMIT TRAN;
GO
SET XACT_ABORT OFF;
SELECT 'XACT_ABORT ON' AS Mode, COUNT(*) AS RowsSaved FROM dbo.TransferHistory WHERE Status = 'XA-ON';     -- 0 rows
DELETE FROM dbo.TransferHistory WHERE Status IN ('XA-OFF', 'XA-ON');
GO

(1 row affected)

(2 rows affected)

Part G: Implicit Transactions

SET IMPLICIT_TRANSACTIONS ON;
    UPDATE dbo.BankAccounts SET Balance = Balance WHERE AccountNo = 'SB1001';   -- opens a transaction automatically
    SELECT @@TRANCOUNT AS OpenTransactions;    -- 1  (you MUST commit or rollback!)
COMMIT;
SET IMPLICIT_TRANSACTIONS OFF;
GO

(1 row affected)

Part H: Find Open Transactions (forgotten BEGIN TRAN = blocking!)

SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       t.transaction_id, at.name AS TranName, at.transaction_begin_time
FROM sys.dm_tran_session_transactions t
JOIN sys.dm_exec_sessions s            ON s.session_id = t.session_id
JOIN sys.dm_tran_active_transactions at ON at.transaction_id = t.transaction_id;

DBCC OPENTRAN;   -- oldest active transaction in the current database
GO

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Best Practices

• Keep transactions SHORT (no user input / waiting inside a transaction).
• Always pair BEGIN TRAN with COMMIT/ROLLBACK in TRY...CATCH.
• Use SET XACT_ABORT ON in procedures that open transactions.
• Access tables in the SAME ORDER in all procedures (avoids deadlocks, file 23).

PRACTICE QUESTIONS 1. Write usp_PayBill that debits an account and inserts into TransferHistory with ToAccount = 'BILLER'. 2. Use savepoints to insert 5 rows and roll back only rows 4 and 5. 3. Show what happens if you call ROLLBACK TRAN InnerName (named inner transaction) - read the error. 4. Write a batch that transfers money between 3 accounts in a chain; if any step fails, nothing is saved.