Lesson 48 of 54 18 minMaster

Transactions and ACID

Make several changes succeed or fail together: autocommit, BEGIN TRANSACTION, COMMIT and ROLLBACK, why an error does not end a transaction, SET XACT_ABORT ON, the TRY…CATCH pattern, nested transactions, savepoints and what ACID means.

Placing an order at BuildMart takes several statements: add a row to Orders, add its lines to OrderItems and take the items out of stock. If the server stops after the first two, the shop has an order whose goods were never taken out of stock. A transaction groups statements so that they all happen or none of them do.

Every statement is a transaction

You have used transactions in every lesson without knowing it. By default SQL Server runs in autocommit mode: each INSERT, UPDATE or DELETE is a transaction of its own. A statement that changes 3 rows changes all 3 or none. Here a CHECK constraint stops stock going below zero, and one of the three tools has only 12 in stock:

Query.sql

1ALTER TABLE dbo.Products ADD CONSTRAINT CK_Products_Stock CHECK (Stock >= 0);2GO3UPDATE dbo.Products SET Stock = Stock - 15 WHERE CategoryId = 1;4 5SELECT Name, Stock FROM dbo.Products WHERE CategoryId = 1;

Output

Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the CHECK constraint "CK_Products_Stock". The conflict occurred in database "BuildMart", table "dbo.Products", column 'Stock'.
The statement has been terminated.

Name              Stock
----------------  -----
Claw hammer          40
Cordless drill       12
Tape measure 8 m     65

The hammer and the tape measure had enough stock, but they are unchanged too. The UPDATE failed as a whole, so SQL Server undid the rows it had already changed. You never see a statement half done.

BEGIN TRANSACTION and COMMIT

To make several statements one unit, start a transaction yourself. BEGIN TRANSACTION starts it; COMMIT makes every change since then permanent:

Query.sql

1BEGIN TRANSACTION;2 3INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)4VALUES (3, '2024-06-20', 'Pending');5 6DECLARE @OrderId int = SCOPE_IDENTITY();7 8INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)9VALUES (@OrderId, 2, 1, 89.00),10       (@OrderId, 8, 3, 11.90);11 12UPDATE dbo.Products13SET Stock = Stock - CASE ProductId WHEN 2 THEN 1 ELSE 3 END14WHERE ProductId IN (2, 8);15 16COMMIT;17 18SELECT i.OrderId, p.Name, i.Quantity, p.Stock19FROM dbo.OrderItems AS i20JOIN dbo.Products AS p ON p.ProductId = i.ProductId21WHERE i.OrderId = 11;

Output

OrderId  Name               Quantity  Stock
-------  -----------------  --------  -----
     11  Cordless drill            1     11
     11  Wood screws (200)         3    147

Until COMMIT runs, the changes are provisional. Other sessions cannot see them (the next lesson shows what they see instead), and nothing is permanent yet.

ROLLBACK undoes the lot

ROLLBACK undoes every change since BEGIN TRANSACTION. @@TRANCOUNT tells you whether a transaction is open: 0 means none.

Query.sql

1BEGIN TRANSACTION;2 3DELETE FROM dbo.OrderItems;4 5SELECT @@TRANCOUNT AS OpenTransactions, COUNT(*) AS OrderLines6FROM dbo.OrderItems;7 8ROLLBACK;9 10SELECT @@TRANCOUNT AS OpenTransactions, COUNT(*) AS OrderLines11FROM dbo.OrderItems;

Output

OpenTransactions  OrderLines
----------------  ----------
               1           0

OpenTransactions  OrderLines
----------------  ----------
               0          18

Inside the transaction the session sees its own deletes: 0 lines. After ROLLBACK all 18 lines are back. This is a safe way to try a risky UPDATE or DELETE: run it inside a transaction, check the result, then COMMIT or ROLLBACK.

The trap: an error does not end the transaction

This surprises almost everyone. Most errors stop only the statement that failed. The transaction stays open and the batch carries on to the next statement, COMMIT included. Here the second statement refers to an order that does not exist:

Query.sql

1BEGIN TRANSACTION;2 3UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;4 5INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)6VALUES (99, 2, 1, 89.00);          -- there is no order 997 8COMMIT;9 10SELECT Name, Stock, @@TRANCOUNT AS OpenTransactions11FROM dbo.Products WHERE ProductId = 2;

Output

Msg 547, Level 16, State 0, Line 5
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__OrderItem__Order__412EB0B6". The conflict occurred in database "BuildMart", table "dbo.Orders", column 'OrderId'.
The statement has been terminated.

Name            Stock  OpenTransactions
--------------  -----  ----------------
Cordless drill     11                 0

The INSERT failed, but COMMIT still ran, so the drill’s stock went down from 12 to 11 for an order line that was never written. That is exactly the half-done change a transaction was meant to prevent.

SET XACT_ABORT ON

With SET XACT_ABORT ON, any run-time error rolls back the whole transaction and stops the batch. Put it at the top of every script and stored procedure that uses transactions:

Query.sql

1SET XACT_ABORT ON;2 3BEGIN TRANSACTION;4 5UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;6 7INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)8VALUES (99, 2, 1, 89.00);9 10COMMIT;11GO12SELECT Name, Stock, @@TRANCOUNT AS OpenTransactions13FROM dbo.Products WHERE ProductId = 2;

Output

Msg 547, Level 16, State 0, Line 7
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__OrderItem__Order__412EB0B6". The conflict occurred in database "BuildMart", table "dbo.Orders", column 'OrderId'.

Name            Stock  OpenTransactions
--------------  -----  ----------------
Cordless drill     12                 0

The stock is still 12 and no transaction is open. The COMMIT never ran, because the batch stopped at the error. The SELECT is in a new batch (after GO), so it still runs.

Interactive

Inside a transaction

BEGIN TRANSACTION;
INSERT INTO dbo.Orders … VALUES (3, '2024-06-20', 'Pending');
INSERT INTO dbo.OrderItems … (@OrderId, 2, 1, 89.00), (@OrderId, 8, 3, 11.90);
UPDATE dbo.Products SET Stock = Stock - … WHERE ProductId IN (2, 8);
COMMIT;

@@TRANCOUNT 0

	This session sees	Committed (other sessions)
Drill stock	12	12
Screws stock	150	150
Order 11	none	none

Press “Run next statement”. Highlighted cells are changes this session can see but that are not committed.

The full pattern: XACT_ABORT with TRY…CATCH

XACT_ABORT makes sure nothing is half done. TRY…CATCH (from the error-handling lesson) lets you react: log the error, tidy up, and pass the error on. Together they give the standard shape for a unit of work:

Query.sql

1SET XACT_ABORT ON;2 3BEGIN TRY4    BEGIN TRANSACTION;5 6    UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;7 8    INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)9    VALUES (99, 2, 1, 89.00);10 11    COMMIT;12END TRY13BEGIN CATCH14    IF @@TRANCOUNT > 0 ROLLBACK;15 16    PRINT CONCAT('Rolled back: ', ERROR_MESSAGE());17    THROW;                                  -- pass the error on to the caller18END CATCH;

Output

Rolled back: The INSERT statement conflicted with the FOREIGN KEY constraint "FK__OrderItem__Order__412EB0B6". The conflict occurred in database "BuildMart", table "dbo.Orders", column 'OrderId'.
Msg 547, Level 16, State 0, Line 8
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__OrderItem__Order__412EB0B6". The conflict occurred in database "BuildMart", table "dbo.Orders", column 'OrderId'.

In the CATCH block, test before you roll back: if the error happened before BEGIN TRANSACTION, there is nothing to roll back, and ROLLBACK with no open transaction is itself an error. XACT_STATE() tells you more than @@TRANCOUNT:

XACT_STATE()	Meaning	What you can do
1	A transaction is open and can be committed	COMMIT or ROLLBACK
0	No transaction is open	Nothing to end
-1	A transaction is open but doomed: an error means it can only be rolled back	ROLLBACK only; a COMMIT fails

With XACT_ABORT ON, every error inside a TRY block dooms the transaction, so XACT_STATE() is -1 in the CATCH block:

Query.sql

1SET XACT_ABORT ON;2 3BEGIN TRY4    BEGIN TRANSACTION;5    UPDATE dbo.Products SET Stock = Stock - 1 WHERE ProductId = 2;6    INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)7    VALUES (99, 2, 1, 89.00);8END TRY9BEGIN CATCH10    SELECT XACT_STATE() AS XactState, @@TRANCOUNT AS OpenTransactions,11           ERROR_NUMBER() AS ErrorNumber;12    IF XACT_STATE() <> 0 ROLLBACK;13END CATCH;14 15SELECT XACT_STATE() AS XactState, @@TRANCOUNT AS OpenTransactions;

Output

XactState  OpenTransactions  ErrorNumber
---------  ----------------  -----------
       -1                 1          547

XactState  OpenTransactions
---------  ----------------
        0                 0

Nested transactions are not really nested

You can run BEGIN TRANSACTION inside a transaction, but that does not start a second, independent transaction. It only adds 1 to @@TRANCOUNT. COMMIT takes 1 off, and only the COMMIT that brings it to 0 really commits. ROLLBACK always undoes everything back to the outermost BEGIN:

Query.sql

1BEGIN TRANSACTION;                                     -- @@TRANCOUNT 12    UPDATE dbo.Products SET Price = 20.00 WHERE ProductId = 1;3 4    BEGIN TRANSACTION;                                 -- @@TRANCOUNT 25        UPDATE dbo.Products SET Price = 10.00 WHERE ProductId = 3;6    COMMIT;                                            -- back to 1: nothing is committed yet7 8    SELECT @@TRANCOUNT AS OpenTransactions;9ROLLBACK;                                              -- undoes both updates10 11SELECT ProductId, Name, Price FROM dbo.Products WHERE ProductId IN (1, 3);

Output

OpenTransactions
----------------
               1

ProductId  Name              Price
---------  ----------------  -----
        1  Claw hammer       18.50
        3  Tape measure 8 m   9.75

The inner COMMIT did not save the tape measure’s new price. This matters when a stored procedure that uses a transaction is called by code that already has one open: the procedure’s COMMIT commits nothing, and its ROLLBACK undoes the caller’s work too.

Savepoints: undo part of a transaction

To undo only the last few changes, mark a point with SAVE TRANSACTION name and later run ROLLBACK TRANSACTION name. The changes before the savepoint stay, and the transaction stays open:

Query.sql

1BEGIN TRANSACTION;2 3INSERT INTO dbo.Orders (CustomerId, OrderDate, Status)4VALUES (5, '2024-06-21', 'Pending');                   -- order 115 6SAVE TRANSACTION BeforeLines;7 8INSERT INTO dbo.OrderItems (OrderId, ProductId, Quantity, UnitPrice)9VALUES (11, 12, 3, 6.80);10 11ROLLBACK TRANSACTION BeforeLines;                      -- undo the line, keep the order12 13SELECT @@TRANCOUNT AS OpenTransactions;14COMMIT;15 16SELECT o.OrderId, o.Status,17       (SELECT COUNT(*) FROM dbo.OrderItems AS i WHERE i.OrderId = o.OrderId) AS Lines18FROM dbo.Orders AS o19WHERE o.OrderId = 11;

Output

OpenTransactions
----------------
               1

OrderId  Status   Lines
-------  -------  -----
     11  Pending      0

ACID

Database people sum up what a transaction promises with four letters:

Letter	Promise	How SQL Server keeps it
Atomicity	All the changes happen, or none do	Every change is written to the transaction log first; ROLLBACK and crash recovery use the log to undo
Consistency	A transaction takes the data from one valid state to another	Constraints (keys, foreign keys, CHECK) are checked; a change that breaks one fails
Isolation	Transactions running at the same time do not see each other’s half-done work	Locks or row versions; the next two lessons
Durability	Once COMMIT returns, the change survives a crash or power cut	COMMIT waits until the log records are on disk; the data pages are written later

Durability works through write-ahead logging. A change is made to the page in memory and described in the log. COMMIT returns as soon as the log is safely on disk. The changed data pages are written to the data file later, at a checkpoint. If the server crashes in between, SQL Server reads the log when it starts again: it redoes committed changes that had not reached the data file and undoes changes from transactions that never committed.

Quick check

Without SET XACT_ABORT ON, a transaction runs an UPDATE (it works), then an INSERT that breaks a foreign key, then COMMIT. What is saved?

Quick check

@@TRANCOUNT is 2 after two BEGIN TRANSACTION statements. You run COMMIT, then ROLLBACK. What happens to the changes made after the second BEGIN?

Practice

Order 9 (two boxes of wall plugs short) is being cancelled. In one transaction, set its Status to 'Cancelled' and put its items back into stock: add each line’s Quantity to the product’s Stock. Use SET XACT_ABORT ON and TRY…CATCH, then show order 9’s status and the wall plugs’ stock.

Show a hint

One UPDATE with a join adds the quantities back: UPDATE p SET Stock = p.Stock + i.Quantity FROM dbo.Products AS p JOIN dbo.OrderItems AS i ON ….

Show the solution

Solution.sql

1SET XACT_ABORT ON;2 3BEGIN TRY4    BEGIN TRANSACTION;5 6    UPDATE dbo.Orders SET Status = 'Cancelled' WHERE OrderId = 9;7 8    UPDATE p9    SET Stock = p.Stock + i.Quantity10    FROM dbo.Products AS p11    JOIN dbo.OrderItems AS i ON i.ProductId = p.ProductId12    WHERE i.OrderId = 9;13 14    COMMIT;15END TRY16BEGIN CATCH17    IF @@TRANCOUNT > 0 ROLLBACK;18    THROW;19END CATCH;20 21SELECT o.OrderId, o.Status, p.Name, p.Stock22FROM dbo.Orders AS o23JOIN dbo.OrderItems AS i ON i.OrderId = o.OrderId24JOIN dbo.Products AS p ON p.ProductId = i.ProductId25WHERE o.OrderId = 9;

Output

OrderId  Status     Name              Stock
-------  ---------  ----------------  -----
      9  Cancelled  Wall plugs (100)      4

Remember

Each statement is its own transaction (autocommit): it changes all its rows or none.
BEGIN TRANSACTION … COMMIT makes several statements one unit; ROLLBACK undoes them.
Most errors end only the statement, not the transaction: the batch goes on and COMMIT commits the rest.
SET XACT_ABORT ON makes any error roll back the whole transaction and stop the batch.
The standard shape: SET XACT_ABORT ON; BEGIN TRY … COMMIT END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK; THROW; END CATCH.
XACT_STATE(): 1 open, 0 none, -1 doomed (roll back only).
Nested BEGIN TRANSACTION only counts; ROLLBACK undoes everything. SAVE TRANSACTION allows a partial undo.
ACID: atomic, consistent, isolated, durable. Durability comes from the transaction log being on disk before COMMIT returns.

Foundations

Creating and changing data

Functions and expressions

Joins and combining tables

Grouping and subqueries

Advanced querying

Programmability

Indexes and performance

Master level

Transactions and ACID

Every statement is a transaction

BEGIN TRANSACTION and COMMIT

ROLLBACK undoes the lot

The trap: an error does not end the transaction

SET XACT_ABORT ON

Inside a transaction

The full pattern: XACT_ABORT with TRY…CATCH

Nested transactions are not really nested

Savepoints: undo part of a transaction

ACID

Remember