Transactions

Learn BEGIN TRANSACTION, COMMIT, and ROLLBACK in SQL Server, the ACID properties, and a bank-transfer example that shows why transactions matter.

Introduction

Some operations only make sense if every step succeeds together. Transferring money between two bank accounts, for example, involves both subtracting from one account and adding to another - if only one half happens, the data is corrupt. A transaction is SQL Server's mechanism for grouping multiple statements into a single all-or-nothing unit of work. This lesson introduces transactions, the ACID properties they guarantee, and walks through a bank-transfer example.

What You Will Learn

What a transaction is and why it matters.
The ACID properties transactions guarantee.
How to use BEGIN TRANSACTION, COMMIT, and ROLLBACK.
A realistic bank-transfer example.
The difference between implicit and explicit transactions.

What Is a Transaction?

A transaction is a sequence of one or more statements that SQL Server treats as a single logical operation. Either every statement in the transaction succeeds and its changes become permanent (a commit), or something goes wrong and every change is undone as if none of it happened (a rollback). There is no in-between state visible to other connections.

The ACID Properties

Property	Meaning
Atomicity	All statements in the transaction succeed, or none of them do
Consistency	The database moves from one valid state to another, never leaving constraints violated
Isolation	Concurrent transactions do not see each other's uncommitted changes
Durability	Once committed, changes survive even a server crash immediately after

BEGIN, COMMIT, ROLLBACK

BEGIN TRANSACTION starts an explicit transaction. COMMIT TRANSACTION makes all of its changes permanent. ROLLBACK TRANSACTION undoes everything since BEGIN TRANSACTION.

BEGIN TRANSACTION;

UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountID = 2;

COMMIT TRANSACTION;

Bank Transfer Example

Here is a more complete transfer that checks the sender has enough balance before committing, rolling back if not.

CREATE TABLE dbo.Accounts (
    AccountID INT PRIMARY KEY,
    Owner     VARCHAR(50) NOT NULL,
    Balance   DECIMAL(12,2) NOT NULL CHECK (Balance >= 0)
);

INSERT INTO dbo.Accounts VALUES (1, 'Alice', 1200.00), (2, 'Ben', 300.00);

BEGIN TRANSACTION;

DECLARE @FromBalance DECIMAL(12,2);
SELECT @FromBalance = Balance FROM dbo.Accounts WHERE AccountID = 1;

IF @FromBalance >= 500
BEGIN
    UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
    COMMIT TRANSACTION;
    PRINT 'Transfer completed.';
END
ELSE
BEGIN
    ROLLBACK TRANSACTION;
    PRINT 'Transfer failed: insufficient funds.';
END

SELECT AccountID, Owner, Balance FROM dbo.Accounts;

Result

Click Run to see what this code prints.

Checking for Errors

A basic pattern for detecting a failed statement inside a transaction is to check @@ERROR immediately after it runs, though the next lesson introduces a much cleaner approach using TRY/CATCH.

BEGIN TRANSACTION;

UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END

UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
IF @@ERROR <> 0
BEGIN
    ROLLBACK TRANSACTION;
    RETURN;
END

COMMIT TRANSACTION;

Prefer TRY/CATCH

Checking @@ERROR after every statement works but is verbose and easy to forget. The next lesson shows how BEGIN TRY / BEGIN CATCH makes transactional error handling far more reliable and readable.

Implicit vs Explicit Transactions

By default, SQL Server runs in autocommit mode: every individual statement is its own implicit transaction, committed automatically if it succeeds. An explicit transaction, started with BEGIN TRANSACTION, groups multiple statements together until you COMMIT or ROLLBACK. Almost all multi-step business logic - like the bank transfer above - needs an explicit transaction.

Common Mistakes

Avoid These Mistakes

Starting a transaction and forgetting to COMMIT or ROLLBACK, leaving locks held indefinitely.
Wrapping long-running or user-interactive steps inside a transaction, blocking other users unnecessarily.
Nesting BEGIN TRANSACTION calls and assuming an inner COMMIT fully commits - only the outermost COMMIT actually does.
Not checking whether earlier statements failed before continuing to the next one.
Performing non-transactional operations (like sending an email) inside a transaction that might roll back.

Best Practices

Keep transactions as short as possible to minimize how long locks are held.
Always pair BEGIN TRANSACTION with a guaranteed COMMIT or ROLLBACK, ideally using TRY/CATCH (next lesson).
Avoid any user interaction or slow external calls while a transaction is open.
Use SET XACT_ABORT ON so most runtime errors automatically roll back the transaction.
Test both the success and failure paths of every transactional procedure.

Frequently Asked Questions

What happens if my connection drops mid-transaction?

SQL Server automatically rolls back any transaction left open by a connection that disconnects, so partial changes are never left committed.

Can I nest transactions?

Syntactically yes, but SQL Server only tracks a single transaction count (@@TRANCOUNT) - an inner COMMIT just decrements the count rather than truly committing, and any ROLLBACK undoes the entire outermost transaction.

Do SELECT statements need a transaction?

Not usually - a single SELECT is already its own implicit transaction. Explicit transactions matter when you need multiple statements (often writes) to succeed or fail together.

What does SET XACT_ABORT ON do?

It tells SQL Server to automatically roll back the entire transaction if any runtime error occurs, rather than continuing execution - it is commonly recommended alongside TRY/CATCH, covered next.

Key Takeaways

A transaction groups statements into one all-or-nothing unit of work.
ACID (Atomicity, Consistency, Isolation, Durability) describes what transactions guarantee.
BEGIN TRANSACTION starts one; COMMIT makes changes permanent; ROLLBACK undoes them.
SQL Server autocommits each statement by default unless an explicit transaction is open.
Keep transactions short, and always guarantee a COMMIT or ROLLBACK is reached.

Summary

Transactions are how SQL Server keeps multi-step changes safe and consistent, even when something goes wrong partway through. Next, you will learn TRY/CATCH error handling, which pairs naturally with transactions to make rollback-on-error code much cleaner.

Next Lesson →