# 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 November 3, 2026

## 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

```sql
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:

```sql
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.

```sql
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:

```sql
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](https://www.ask-codey.com/glossary/transaction)
- BEGIN TRANSACTION
- [COMMIT](https://www.ask-codey.com/glossary/commit)
- [ROLLBACK](https://www.ask-codey.com/glossary/rollback)
- [ACID Properties](https://www.ask-codey.com/glossary/acid-properties)
- TRY...CATCH

## Cheat sheets

- [Transactions](https://www.ask-codey.com/cheat-sheets/modifying-data-in-t-sql-transactions-cheat-sheet)

Source: https://www.ask-codey.com/lessons/modifying-data-in-t-sql-transactions