Imagine you're processing a customer order in your e-commerce system. You deduct stock from the warehouse, create the order record, and bill the customer — three separate database operations. Now imagine the server crashes halfway through. Without proper transaction management, you could end up with a billed customer, no order record, and missing inventory. That's a nightmare scenario that SQL Server transactions, ACID properties, and isolation levels are specifically designed to prevent.
If you've been writing T-SQL for a while, you've probably used BEGIN TRANSACTION and COMMIT without thinking too deeply about what's happening underneath. This guide pulls back the curtain — and uses the WideWorldImporters sample database to show you exactly how these concepts play out in real, relatable scenarios.
What Is a Transaction in SQL Server?
A transaction is a logical unit of work — one or more T-SQL statements that are treated as a single, all-or-nothing operation. Either every statement in the transaction succeeds and the changes are saved, or the entire thing is rolled back as if it never happened.
SQL Server supports three types:
- Explicit transactions — you manually write
BEGIN TRANSACTION,COMMIT, andROLLBACK - Implicit transactions — SQL Server automatically starts a transaction after each
COMMITorROLLBACK(controlled bySET IMPLICIT_TRANSACTIONS ON) - Autocommit transactions — the default mode; each individual statement is its own transaction
For this guide, we'll focus on explicit transactions, which give you the most control and are most common in real-world stored procedures.
The Four ACID Properties — Explained Simply
ACID is an acronym that defines the four guarantees every reliable database transaction must provide. Here's what each one means in plain English:
Atomicity — "All or Nothing"
Every statement inside a transaction either completes fully or doesn't happen at all. If you insert an order into Sales.Orders and then the insert into Sales.OrderLines fails, atomicity ensures the order record gets rolled back too. You never end up with orphaned data.
Consistency — "Rules Always Hold"
A transaction brings the database from one valid state to another. Constraints, foreign keys, and business rules are always respected. If a NOT NULL constraint exists on Sales.Orders.CustomerID, a transaction can't leave that column empty even temporarily.
Isolation — "Transactions Don't Step on Each Other"
Concurrent transactions are shielded from each other's in-progress changes. One user's half-finished order update shouldn't affect another user's query running at the same time. How much shielding you get is controlled by isolation levels — more on those shortly.
Durability — "Committed Data Survives"
Once a transaction is committed, the changes are permanent — even if the server crashes immediately afterward. SQL Server achieves this through the transaction log, which records every change before it's written to the data files.
Step-by-Step Tutorial: Transactions in WideWorldImporters
Step 1: A Simple Explicit Transaction — Creating an Order
Let's walk through the most classic transaction scenario: inserting a new order and its order lines together as a single unit.
BEGIN TRY
BEGIN TRANSACTION;
-- Step 1: Insert the new order header
INSERT INTO Sales.Orders
(
CustomerID,
SalespersonPersonID,
ContactPersonID,
OrderDate,
CustomerPurchaseOrderNumber,
IsUndersupplyBackordered,
LastEditedBy,
LastEditedWhen
)
VALUES
(
1001, -- CustomerID (e.g., Tailspin Toys)
3, -- SalespersonPersonID
3, -- ContactPersonID
GETDATE(),
'PO-88421',
1,
1,
GETDATE()
);
DECLARE @NewOrderID INT = SCOPE_IDENTITY();
-- Step 2: Insert the order line
INSERT INTO Sales.OrderLines
(
OrderID,
StockItemID,
Description,
PackageTypeID,
Quantity,
UnitPrice,
TaxRate,
PickedQuantity,
LastEditedBy,
LastEditedWhen
)
VALUES
(
@NewOrderID,
100, -- StockItemID
'Amusing novelty item - Red',
7, -- PackageTypeID
10,
15.00,
15.000,
0,
1,
GETDATE()
);
COMMIT TRANSACTION;
PRINT 'Order created successfully. OrderID: ' + CAST(@NewOrderID AS VARCHAR);
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Transaction rolled back. Error: ' + ERROR_MESSAGE();
END CATCH;
Notice the BEGIN TRY / BEGIN CATCH wrapper — this is the professional pattern. If either insert fails, ROLLBACK TRANSACTION fires in the CATCH block, leaving the database exactly as it was before the transaction started.
Step 2: Transactions with Stock Updates — Atomicity in Action
Here's a more complex scenario: when an order is picked, we need to both record the picked quantity on the order line and reduce the quantity on hand in Warehouse.StockItemHoldings. Both must succeed or neither should.
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @OrderLineID INT = 1001;
DECLARE @PickedQty INT = 5;
DECLARE @StockItemID INT;
-- Retrieve the StockItemID for this order line
SELECT @StockItemID = StockItemID
FROM Sales.OrderLines
WHERE OrderLineID = @OrderLineID;
-- Update the picked quantity on the order line
UPDATE Sales.OrderLines
SET PickedQuantity = PickedQuantity + @PickedQty,
LastEditedBy = 1,
LastEditedWhen = GETDATE()
WHERE OrderLineID = @OrderLineID;
-- Reduce the quantity on hand in the warehouse
UPDATE Warehouse.StockItemHoldings
SET QuantityOnHand = QuantityOnHand - @PickedQty,
LastEditedBy = 1,
LastEditedWhen = GETDATE()
WHERE StockItemID = @StockItemID;
-- Safety check: don't allow negative stock
IF EXISTS (
SELECT 1 FROM Warehouse.StockItemHoldings
WHERE StockItemID = @StockItemID AND QuantityOnHand < 0
)
BEGIN
RAISERROR('Insufficient stock. Transaction aborted.', 16, 1);
END;
COMMIT TRANSACTION;
PRINT 'Pick recorded successfully.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Rollback triggered: ' + ERROR_MESSAGE();
END CATCH;
This is ACID atomicity working in your favor. The stock deduction and the order line update are inseparable — you'll never have one without the other.
Understanding Isolation Levels in SQL Server
Isolation levels control how a transaction is shielded from the work of other concurrent transactions. SQL Server offers five standard levels, each making a different tradeoff between data accuracy and concurrency (throughput).
The three classic read anomalies you're protecting against are:
| Anomaly | What Happens |
|---|---|
| Dirty Read | You read uncommitted data from another transaction that later gets rolled back |
| Non-Repeatable Read | You read the same row twice in a transaction and get different values because another transaction updated it |
| Phantom Read | You run the same query twice and get different rows because another transaction inserted or deleted data |
The Five Isolation Levels
| Isolation Level | Dirty Reads | Non-Repeatable Reads | Phantom Reads |
|---|---|---|---|
| READ UNCOMMITTED | ✅ Possible | ✅ Possible | ✅ Possible |
| READ COMMITTED (default) | ❌ Prevented | ✅ Possible | ✅ Possible |
| REPEATABLE READ | ❌ Prevented | ❌ Prevented | ✅ Possible |
| SERIALIZABLE | ❌ Prevented | ❌ Prevented | ❌ Prevented |
| SNAPSHOT | ❌ Prevented | ❌ Prevented | ❌ Prevented |
Step 3: Demonstrating READ UNCOMMITTED — The "Dirty Read"
READ UNCOMMITTED is the most permissive level. It lets you read data that another transaction has changed but not yet committed. This is rarely appropriate in business applications, but is sometimes used for quick reporting queries where absolute precision isn't critical.
-- Session A: Start a transaction and update a customer, but don't commit yet
BEGIN TRANSACTION;
UPDATE Sales.Customers
SET CreditLimit = 50000.00
WHERE CustomerID = 1001;
-- Intentionally NOT committing yet...
-- Session B: Read the uncommitted change using READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT CustomerID, CustomerName, CreditLimit
FROM Sales.Customers
WHERE CustomerID = 1001;
-- Session B can see CreditLimit = 50000.00 even though Session A hasn't committed!
-- If Session A rolls back, Session B already read "ghost" data — that's a dirty read.
ROLLBACK TRANSACTION; -- (Back in Session A)
When would you actually use this? Some developers use READ UNCOMMITTED (or the equivalent WITH (NOLOCK) table hint) on non-critical reporting queries to avoid blocking. It's a controversial practice — use it consciously and never on financial or inventory data.
Step 4: Using READ COMMITTED SNAPSHOT Isolation (RCSI)
Modern SQL Server applications often enable Read Committed Snapshot Isolation (RCSI) at the database level. This gives readers a consistent snapshot of committed data without blocking writers — the best of both worlds for most OLTP workloads.
-- Enable RCSI on WideWorldImporters (run once, as a DBA)
ALTER DATABASE WideWorldImporters
SET READ_COMMITTED_SNAPSHOT ON;
-- Now READ COMMITTED queries use row versioning instead of shared locks
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT
o.OrderID,
o.OrderDate,
c.CustomerName,
SUM(ol.Quantity * ol.UnitPrice) AS OrderTotal
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
INNER JOIN Sales.OrderLines ol ON o.OrderID = ol.OrderID
WHERE o.OrderDate >= '2016-01-01'
GROUP BY o.OrderID, o.OrderDate, c.CustomerName
ORDER BY OrderTotal DESC;
With RCSI enabled, this reporting query won't be blocked by in-progress order transactions — and it won't read dirty data either.
Step 5: SERIALIZABLE for Critical Financial Operations
When you absolutely need the strictest guarantees — for example, checking supplier payment terms before creating a purchase order — use SERIALIZABLE.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRANSACTION;
-- Verify the supplier is active and check their payment terms
DECLARE @SupplierID INT = 2;
DECLARE @PaymentDays INT;
SELECT @PaymentDays = PaymentDays
FROM Purchasing.Suppliers
WHERE SupplierID = @SupplierID
AND IsRetired = 0;
IF @PaymentDays IS NULL
BEGIN
RAISERROR('Supplier not found or is retired.', 16, 1);
END;
-- Create the purchase order
INSERT INTO Purchasing.PurchaseOrders
(
SupplierID,
OrderDate,
DeliveryMethodID,
ContactPersonID,
ExpectedDeliveryDate,
SupplierReference,
IsOrderFinalized,
LastEditedBy,
LastEditedWhen
)
VALUES
(
@SupplierID,
GETDATE(),
7,
3,
DATEADD(DAY, @PaymentDays, GETDATE()),
'SUP-REF-2024',
0,
1,
GETDATE()
);
COMMIT TRANSACTION;
PRINT 'Purchase order created.';
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT 'Error: ' + ERROR_MESSAGE();
END CATCH;
-- Reset to default
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SERIALIZABLE places range locks that prevent other transactions from inserting new rows that would affect your query — eliminating phantom reads entirely.
Tips and Common Mistakes with Transactions
Tip 1: Keep transactions as short as possible.
Every open transaction holds locks. Long-running transactions cause blocking, which cascades into timeouts and deadlocks. Do your business logic before opening the transaction, then open it only for the actual data modifications.
Tip 2: Always handle errors with TRY/CATCH.
A BEGIN TRANSACTION without a corresponding ROLLBACK path is a ticking time bomb. If an error goes unhandled, the transaction stays open — locking rows indefinitely.
Tip 3: Check @@TRANCOUNT before committing.
@@TRANCOUNT tells you how many active transactions are open in your session. In nested transaction scenarios, COMMIT only decrements the count — only the outermost COMMIT actually saves the data.
SELECT @@TRANCOUNT AS OpenTransactions;
Tip 4: Never use READ UNCOMMITTED on financial data.
Dirty reads on tables like Warehouse.StockItemHoldings or Sales.Orders can lead to wildly inaccurate inventory counts or double-processed payments. Reserve it strictly for non-critical reporting.
Tip 5: Don't confuse isolation levels with security.
Isolation levels control concurrency behavior, not who can access data. For data security, use SQL Server permissions, row-level security, and encryption — not isolation levels.
Common Mistake: Forgetting ROLLBACK in CATCH.
The single most common transaction bug in production code is a CATCH block that logs the error but forgets to roll back. The transaction remains open, holding locks until the connection drops.
-- ❌ WRONG — transaction stays open on error
BEGIN CATCH
INSERT INTO dbo.ErrorLog (Message) VALUES (ERROR_MESSAGE());
END CATCH;
-- ✅ CORRECT — always roll back first, then log
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
INSERT INTO dbo.ErrorLog (Message) VALUES (ERROR_MESSAGE());
END CATCH;
Summary / Key Takeaways
Understanding SQL Server transactions, ACID properties, and isolation levels is what separates developers who write code that works from those who write code that's genuinely reliable under pressure. Here's what to carry forward:
- A transaction wraps multiple statements into a single all-or-nothing unit of work.
- ACID guarantees — Atomicity, Consistency, Isolation, Durability — are the foundation of trustworthy database operations.
- Use explicit transactions with
BEGIN TRY / BEGIN CATCHand always includeROLLBACKin your error handler. - READ COMMITTED (the default) prevents dirty reads and is appropriate for most OLTP work.
- Consider RCSI (Read Committed Snapshot Isolation) to eliminate reader/writer blocking in busy systems.
- Use SERIALIZABLE only when you need absolute consistency, and be aware of the performance cost.
- Keep transactions short, test your rollback paths, and always check
@@TRANCOUNTin complex stored procedures.
FAQ
Q1: What's the difference between ROLLBACK and not committing?
If you open a transaction and simply close the connection without committing, SQL Server automatically rolls back all uncommitted changes — this is part of the durability guarantee. However, relying on this is bad practice. Always write explicit ROLLBACK statements in your CATCH blocks so your code is self-documenting and your intent is clear to the next developer.
Q2: Can I nest transactions in SQL Server?
Yes and no. SQL Server supports nested BEGIN TRANSACTION calls, but only the outermost COMMIT actually saves data to disk. Inner COMMIT statements simply decrement @@TRANCOUNT. However, a ROLLBACK at any nesting level rolls back the entire transaction — not just the inner one. This trips up many intermediate developers. For more control in nested scenarios, use savepoints with SAVE TRANSACTION.
Q3: What is the NOLOCK hint and is it safe to use?
WITH (NOLOCK) is a table-level hint that applies READ UNCOMMITTED isolation to a specific table in a query. It's sometimes used on reporting queries to avoid blocking, but it carries real risks: dirty reads, missing rows, and even duplicate rows due to page splits. A safer modern alternative is to enable Read Committed Snapshot Isolation (RCSI) at the database level, which gives you non-blocking reads without any risk of reading uncommitted data.