Transactions

After this lesson you can

• Group changes in a transaction, and commit or roll them back

Key takeaways

• BEGIN TRANSACTION starts a group of changes; COMMIT saves them all, and ROLLBACK undoes them all.
• While you work, start a transaction, make the change, look at the result, then commit or roll back.
• When one task takes several statements, such as an order and its order lines, a transaction makes them succeed or fail together.
• Without SET XACT_ABORT ON; or TRY and CATCH, a failed statement is undone on its own and COMMIT still saves the rest.
• Finish every transaction you start: an open one holds locks, and other people's queries wait for it.

Every change you've made so far was saved the moment it ran. A transaction lets you group changes together and decide afterwards: keep them all (commit), or undo them all (roll back).

A safety net

BEGIN TRANSACTION;

UPDATE Products
SET UnitPrice = UnitPrice * 1.10
WHERE CategoryId = 1;

SELECT ProductName, UnitPrice
FROM Products
WHERE CategoryId = 1;

ROLLBACK;

1. BEGIN TRANSACTION starts the transaction.
2. The UPDATE changes 12 prices, and the SELECT shows the new prices.
3. ROLLBACK undoes everything since BEGIN TRANSACTION. Run the SELECT again on its own, and the old prices are back.

When you're happy with a change, end with COMMIT instead, and it's saved for good:

BEGIN TRANSACTION;

UPDATE Customers
SET ContactName = 'Alfred Blauer'
WHERE CustomerId = 'ALFKI';

COMMIT;

This is the safest way to work while you're learning, and many professionals work this way on real data: start a transaction, make the change, look at the result, then decide.

All or nothing

Transactions matter most when one task takes several statements. A customer's order is a row in Orders and a row in OrderDetails for each product. If the second statement failed after the first succeeded, you'd have an order with no products. A transaction lets the steps succeed together or fail together, with one catch. When a statement fails, SQL Server undoes just that statement and carries on, so COMMIT would still save the order. SET XACT_ABORT ON; at the top of the script fixes that: any error then rolls back the whole transaction and stops the script.

SET XACT_ABORT ON;

DECLARE @NewOrderId int;

BEGIN TRANSACTION;

INSERT INTO Orders (CustomerId, EmployeeId, OrderDate)
VALUES ('ALFKI', 1, '2026-05-01');

SET @NewOrderId = SCOPE_IDENTITY();

INSERT INTO OrderDetails (OrderId, ProductId, UnitPrice, Quantity, Discount)
VALUES (@NewOrderId, 1, 18.00, 2, 0)
    , (@NewOrderId, 2, 19.00, 1, 0);

COMMIT;

Databases make this promise with four properties, often called ACID:

Rolling back when something fails

TRY and CATCH are the other way to make a script roll back by itself when a statement fails, and they let you say what went wrong. Here the second insert breaks a foreign key, since there's no product 999, so the whole order is undone:

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Orders (CustomerId, EmployeeId, OrderDate)
    VALUES ('ALFKI', 1, '2026-05-01');

    INSERT INTO OrderDetails (OrderId, ProductId, UnitPrice, Quantity, Discount)
    VALUES (SCOPE_IDENTITY(), 999, 18.00, 2, 0);

    COMMIT;
END TRY
BEGIN CATCH
    ROLLBACK;
    PRINT 'The order was not saved: ' + ERROR_MESSAGE();
END CATCH;

Neither the order nor its line is saved, and the message says why.

Don't leave it open

A transaction that's begun but never committed or rolled back holds locks on the rows it changed, and other people's queries wait for it. If you close an SSMS window with a transaction open, SSMS asks whether to commit it. Finish every transaction you start.

Check yourself

2 questions to try on the lesson page.

So what

A transaction turns a group of changes into one decision: all or nothing. Use ROLLBACK as a safety net while you work, and transactions in your apps whenever one task takes more than one statement.

Key terms

• Transaction
• BEGIN TRANSACTION
• COMMIT
• ROLLBACK
• ACID Properties
• TRY...CATCH

Cheat sheets

• Transactions