Transaction Handling in SQL Server

When working with Microsoft SQL Server, it’s important to understand how transactions work. Transactions are the foundation of safe and reliable database operations. They ensure that your changes to the database are made correctly, and if something goes wrong, you can easily undo them.

In this post, we’ll talk about transaction handling in SQL Server, explain BEGIN TRAN, COMMIT, and ROLLBACK, and look at examples of how to use them. You’ll also learn how to use BEGIN TRANSACTION and COMMIT in SQL Server, and how to safely undo changes with ROLLBACK.

All examples below use the Northwind database we created earlier.

What Is a Transaction in SQL Server?

A transaction is a single unit of work that can include one or more SQL statements. The key rule is that all operations in a transaction must succeed, or none of them should. Transactions make sure your data stays consistent.

BEGIN TRAN in SQL Server

To start a transaction manually, you use the BEGIN TRAN (short for “begin transaction”) command.

Here’s a simple example:

BEGIN TRAN
UPDATE Customers
SET ContactName = 'Marijan Sivric'
WHERE CustomerID = 'ALFKI'

This starts a transaction and runs the update, but nothing is finalized yet:

The changes are “in progress” until you explicitly commit them. So, if you close the previous query, and start a new one with SELECT query, you will see that the record didn’t change:

COMMIT TRANSACTION in SQL Server

When you’re happy with your changes, you use COMMIT TRANSACTION to make them permanent.

BEGIN TRAN
UPDATE Customers
SET ContactName = 'Marijan Sivric'
WHERE CustomerID = 'ALFKI'

COMMIT TRAN

Here is what it looks like in SSMS:

After COMMIT, the change is saved to the database permanently. If you forget to commit, SQL Server won’t save your changes (especially if you close the connection). Here is how it looks after COMMIT:

This is the standard pattern for how to use BEGIN TRANSACTION and COMMIT in SQL Server.

How to ROLLBACK Transaction in SQL Server

If something goes wrong during a transaction, you can undo the changes using ROLLBACK.

Let’s say you start a transaction but realize you’ve updated the wrong record:

BEGIN TRAN
UPDATE Customers
SET ContactName = 'Wrong Name'
WHERE CustomerID = 'ALFKI';

ROLLBACK TRAN;

Here is how it looks in SSMS:

The ROLLBACK statement cancels all changes made after the transaction began. When you check the table again (with SELECT statement), you’ll see that nothing was modified.

Example: Combining BEGIN, COMMIT, and ROLLBACK

Here’s a slightly longer example using the Orders table in the Northwind database.

BEGIN TRAN

UPDATE Orders
SET Freight = Freight + 10
WHERE ShipCountry = 'Germany';

-- Check if everything looks correct before committing
SELECT * FROM Orders WHERE ShipCountry = 'Germany';

-- If everything looks fine:
COMMIT TRAN;

-- Or if something went wrong:
-- ROLLBACK TRAN;

This is a common real-world use case: increase shipping costs for orders in a specific country, check the results, and then decide whether to keep or undo the change:

Why Use Transactions?

Transactions are essential when your query modifies multiple rows or tables, or when data consistency matters.

For example:

• If you’re updating both Orders and Order Details, you want both updates to succeed, or neither.
• If an error happens halfway through, ROLLBACK can bring your data back to its original state.

Without transactions, partial updates could leave your database in an inconsistent or corrupted state.

Best Practices for Transaction Handling in SQL Server

• Always start with BEGIN TRAN and end with COMMIT or ROLLBACK.
• Keep transactions as short as possible, since long transactions can lock tables and slow performance.
• Use error handling (TRY...CATCH) to control rollbacks automatically.
• Test your logic before running transactions on production data.

Here’s an example using a TRY...CATCH block:

BEGIN TRY
    BEGIN TRAN

    UPDATE Customers
    SET ContactName = 'New Contact'
    WHERE CustomerID = 'ALFKI';

    COMMIT TRAN;
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    PRINT 'Error occurred. Transaction rolled back.';
END CATCH

This ensures that if anything fails, your transaction is safely rolled back.

Final Thoughts

Learning transaction handling in SQL Server is essential for anyone who wants to work safely with databases. Transactions let you make multiple changes as one logical operation, protecting your data from errors or incomplete updates.

Now you know:

• How to start a transaction with BEGIN TRAN in SQL Server
• How to make changes permanent using COMMIT TRANSACTION in SQL Server
• How to use BEGIN TRANSACTION and COMMIT in SQL Server together
• How to ROLLBACK transaction in SQL Server if something goes wrong

Once you master transactions, you’ll feel much more confident making updates in your SQL Server database. In the next tutorial, we’ll look at how to manage locks and concurrency to handle multiple users safely.

If want to learn more about SQL, check out my last blog post explaining What Are Stored Procedures Used For in SQL.

If you would like to learn more about programming SQL queries, make sure you buy this book: T-SQL Fundamentals (Developer Reference)