Transactions

Group changes into a transaction so they all happen or none do, and use ROLLBACK as a safety net while you work.

Apprentice · T-SQL · SQL · Updated

By

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:

Means
Atomic all of the transaction's changes happen, or none do
Consistent the data follows every rule (keys, required columns) before and after
Isolated other users don't see half-finished changes
Durable once committed, the changes survive, even a crash

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.

Do it: undo a teammate's mistake, then make the right change

Your teammate meant to change one price, Chai's, but ran their UPDATE without a WHERE. Every product's price is now 0. Luckily they started a transaction first, and it's still open, so nothing is saved yet. Their SQL ran before yours (the Tables tab shows it, at the bottom):

BEGIN TRANSACTION;

UPDATE Products
SET UnitPrice = 0;

The Products table has three columns: ProductId (the product's number), ProductName and UnitPrice (the price of one). Before the mistake it held these rows, and the change your teammate meant is in the last column:

ProductId ProductName UnitPrice before the mistake After your SQL
1 Chai 18.00 19.00 (the change they meant)
2 Chang 19.00 19.00
3 Aniseed Syrup 10.00 10.00
4 Chef Anton's Cajun Seasoning 22.00 22.00

After your SQL runs, the check reads every product, sorted by ProductId, and it should give exactly the prices in the last column.

Your job: write the SQL that puts things right, in two steps.

  • First, end your teammate's open transaction so that its change is undone, and every price is back to what it was.
  • Then start a transaction of your own.
  • In it, set the UnitPrice of product 1 (Chai) to 19.00, and only that product.
  • End your transaction so that your change is saved.

Practiced live on the lesson page: write the SQL right there and check it.

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

Cheat sheets