Transactions in SQL Server

4 min
Advanced

Until now, every time we executed an INSERT, UPDATE, or DELETE, the operation was confirmed immediately. If the line of code didn’t produce an error, the change was set in stone instantly.

But the real world is complex. Often, a business operation involves several steps in the database.

The classic example: A bank transfer.

Subtract 100€ from Ana’s account.
Add 100€ to Beto’s account.

What happens if the power goes out just after step 1 and before step 2? Ana has lost her money, Beto hasn’t received it, and the bank has a serious accounting discrepancy.

Since that would be a big problem, to avoid it we use Transactions.

What is a Transaction?

A transaction is a logical unit of work that groups one or more operations. Its main goal is to guarantee the Atomicity property (the ‘A’ in ACID):

Atomicity: Either all operations in the group execute successfully, or none execute.

There are no intermediate states. If something fails halfway through, the database must be able to “travel back in time” and leave everything exactly as it was at the beginning.

Control Commands

In T-SQL, we control explicit transactions with three fundamental commands:

The BEGIN TRANSACTION command starts a new transaction. From this point on, all operations performed on the database are part of the transaction.

BEGIN TRANSACTION;

Copied!

The COMMIT command ends the transaction and confirms all changes made. Once committed, the changes are permanent and cannot be undone.

COMMIT;

Copied!

The ROLLBACK command undoes all changes made since the start of the transaction. This is useful if an error occurs and the operations need to be reverted.

ROLLBACK;

Copied!

Let’s see how we would implement the bank example securely.

-- Start the transaction
BEGIN TRANSACTION;

-- Step 1: Subtract money from Ana
UPDATE Cuentas 
SET Saldo = Saldo - 100 
WHERE Cliente = 'Ana';

-- Let's imagine an error occurs here or the power goes out.
-- Since we haven't done a COMMIT, SQL Server would automatically undo step 1 upon restarting.

-- Step 2: Add money to Beto
UPDATE Cuentas 
SET Saldo = Saldo + 100 
WHERE Cliente = 'Beto';

-- If we get here without errors, we save everything
COMMIT TRANSACTION;

Copied!

Using ROLLBACK

ROLLBACK is our “Undo” button. We can invoke it manually if we detect a logical condition that prevents us from continuing.

For example, what if Ana doesn’t have enough balance?

BEGIN TRANSACTION;

    -- Step 1: Try to subtract
    UPDATE Cuentas 
    SET Saldo = Saldo - 100 
    WHERE Cliente = 'Ana';

    -- Check if the account has gone negative
    DECLARE @NuevoSaldo DECIMAL(10,2);
    SELECT @NuevoSaldo = Saldo FROM Cuentas WHERE Cliente = 'Ana';

    IF @NuevoSaldo < 0
    BEGIN
        -- Problem! Insufficient funds. We undo everything.
        PRINT 'Error: Insufficient funds. Canceling operation.';
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        -- All good, proceed with the deposit to Beto
        UPDATE Cuentas 
        SET Saldo = Saldo + 100 
        WHERE Cliente = 'Beto';
        
        -- Confirm the transaction
        COMMIT TRANSACTION;
        PRINT 'Transfer completed successfully.';
    END

Copied!

In this script, if Ana ends up negative, the initial UPDATE is reverted thanks to the ROLLBACK. It’s as if it never happened.

Implicit vs Explicit Transactions

It’s important to understand how SQL Server works by default:

Auto-Commit (Default): Each individual statement (INSERT, UPDATE…) is a transaction in itself. If you execute a line and it works, it’s saved (automatic COMMIT). If it fails, it’s undone (automatic ROLLBACK).
Explicit Transaction: As soon as we write BEGIN TRANSACTION, we disable auto-commit for that session until we close the transaction.

Be careful with leaving transactions open! One of the most common causes of a database “hanging” is a developer who executed BEGIN TRAN, made some changes, and went to lunch without executing COMMIT or ROLLBACK.

While the transaction is open, SQL Server locks the affected rows (or tables) to protect them. No one else will be able to read or write to them until you finish. Always close what you open.

@@TRANCOUNT

If you’re ever unsure whether you have an open transaction, you can check the system variable @@TRANCOUNT.

If it returns 0: There is no active transaction.
If it returns > 0: There is an active transaction (the number indicates the nesting level).

SELECT @@TRANCOUNT;

Copied!