What Is a Transaction in SQL Server?

A transaction in SQL Server is a group of changes that either all happen or none of them do. There is no halfway. That promise is the reason databases are trusted with money, and it is the thing a spreadsheet cannot offer you.

The Bank Transfer

Move a hundred rupees from one account to another and the database does two things: take it off one row, add it to the other. Between those two statements the money exists nowhere.

If the server loses power in that gap, the money has to either be back where it started or safely arrived. A transaction is how you get that guarantee. Both statements are inside it, so both count or neither does.

Watch It Undo

This ran on SQL Server 2025 against a table of 200,000 orders. I zeroed every order from one city, checked inside the transaction, then changed my mind.

BEGIN TRANSACTION;

UPDATE dbo.Orders SET amount = 0 WHERE city = 'Perth';

SELECT COUNT(*) AS zeroed_inside_the_transaction
FROM dbo.Orders WHERE city = 'Perth' AND amount = 0;

ROLLBACK;

SELECT COUNT(*) AS zeroed_after_rollback
FROM dbo.Orders WHERE city = 'Perth' AND amount = 0;

zeroed_inside_the_transaction
16440

zeroed_after_rollback
3

Inside the transaction, 16,440 rows were zero. After the rollback, three, and those three were already zero before I started. Sixteen thousand changes undone by one word.

That is worth doing yourself once. Run a dangerous UPDATE inside BEGIN TRANSACTION, look at what you did, then ROLLBACK. It is the cheapest safety net in the product.

Commit and Rollback

COMMIT makes the changes permanent and releases the locks. ROLLBACK throws them away as though they never happened.

Until you do one or the other, the transaction is open, your locks are held, and other people may be waiting behind you.

You Are Always in One

This surprises people. A single UPDATE with no BEGIN TRANSACTION around it is still a transaction. SQL Server wraps it in one and commits when it finishes.

So an UPDATE touching a million rows either changes all of them or none. There is no state where six hundred thousand are done. Writing BEGIN TRANSACTION yourself is how you group several statements into one of these.

The Four Letters

You will meet ACID. It is four promises, and they are short.

Atomic: all or nothing, which is what the rollback above showed. Consistent: the database obeys its own rules at the start and the end, so a transaction cannot leave a foreign key pointing at nothing. Isolated: transactions running at the same time do not see each other’s half-finished work. Durable: once committed, it survives the power going out, which is what the transaction log is for.

The Open Transaction Problem

This is the one that causes real trouble, and it is almost always human.

Somebody types BEGIN TRANSACTION, runs an UPDATE, and goes to lunch. Their locks are held for an hour. Everybody else queues. The transaction log cannot free the space that transaction is using, so it grows, and on a busy database it can fill a drive before anybody notices.

One query finds it:

SELECT s.session_id, s.login_name, s.host_name,
       t.transaction_begin_time, LEFT(x.text, 60) AS last_statement
FROM sys.dm_tran_active_transactions AS t
JOIN sys.dm_tran_session_transactions AS st ON st.transaction_id = t.transaction_id
JOIN sys.dm_exec_sessions AS s ON s.session_id = st.session_id
OUTER APPLY sys.dm_exec_sql_text(
    (SELECT sql_handle FROM sys.dm_exec_requests WHERE session_id = s.session_id)) AS x
WHERE s.is_user_process = 1
ORDER BY t.transaction_begin_time;

A transaction_begin_time from this morning is your answer. When a database’s log is growing and log_reuse_wait_desc says ACTIVE_TRANSACTION, this is usually what you will find.

How to Stay Out of Trouble

Keep transactions short. Open, change, commit. Do the thinking before you begin, not in the middle.

Never wait for a person inside a transaction. No prompts, no confirmation dialogs, no going for coffee.

Use TRY and CATCH so an error rolls back rather than leaving the transaction open. And check @@TRANCOUNT when a script surprises you, because it tells you how many you actually have open.

A transaction is not a safety feature you switch on, it is the line you draw around work that must not be left half done.

This post was rewritten from scratch in September 2026. The original, published on 2013-06-12, was a short announcement about something that no longer exists. The address is the same, the subject is now a basic idea worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.