What is a transaction?

What is a transaction?

A transaction is a unit of work the database promises to treat as indivisible. Every statement inside it succeeds and the changes become permanent together, or none of them do and the database looks exactly as it did before you started. There is no third outcome, and no window in which another session sees half of it.

The textbook example is still the best one because it makes the failure obvious. Move money between two accounts and you have two statements: subtract from one, add to the other. If the server loses power between them, the money has left one account and arrived nowhere. No amount of careful application code fixes that, because the gap is on the other side of the network call. A transaction closes the gap by making both statements one operation.

A transaction is not a performance feature and it is not an advanced technique. It is the mechanism by which "these things must be true together" survives a crash.- why the guarantee exists

One thing that surprises people: you are already using transactions. SQL Server runs in autocommit mode by default, so every individual statement is implicitly its own transaction. An UPDATE that matches ten thousand rows and fails on row 9,998 rolls all ten thousand back. What BEGIN TRANSACTION buys you is not the guarantee itself; it is the ability to move the boundary so it spans several statements.

Choosing that boundary is a design decision rather than a technical one. The right question is never "should this be in a transaction" but "what must be true together for this operation to be correct?" Everything inside the answer belongs in one transaction. Everything else - notifications, logging, calling another service, reading data you only display - belongs outside it, and section 07 is about what happens when it is not.

ACID, one letter at a time

ACID is the four properties a transactional database guarantees. The acronym gets recited far more often than it gets understood, and two of the letters are routinely misread.

Atomicity

All or nothing. The engine achieves this with a write-ahead log: before a change is applied to a data page, a record describing it goes to the transaction log. If the transaction rolls back, or the server dies and recovers, the engine walks the log and undoes anything that never committed.

Worth knowing: a rollback is real work, not an instant discard. Rolling back a transaction that ran for twenty minutes can take a comparable amount of time, and once it starts it cannot be cancelled or hurried. Killing the session does not skip the rollback - it starts it.

Consistency

The database moves from one valid state to another, where "valid" means the rules you declared: primary keys, foreign keys, check constraints, unique indexes, triggers. A transaction that would leave a constraint violated does not commit.

This is the letter people misread. It is not the C in the CAP theorem, which is about replicas agreeing with each other, and it is not a guarantee that your data is meaningful. If your business rule lives only in application code, the database has no opinion about it and cannot enforce it.

Isolation

Concurrent transactions do not see each other's unfinished work. Note the word "concurrent": isolation is the only ACID property that is configurable, it has several levels, and the default is not the strictest one. Section 05 is entirely about this.

Durability

When COMMIT returns, the change survives an immediate power failure. The engine guarantees it by hardening the log record to disk before acknowledging the commit - the data pages themselves can be written lazily afterwards, because the log is enough to reconstruct them. This is also why transaction log throughput sets the ceiling on write-heavy workloads, and why the log belongs on your fastest storage. SQL Server offers delayed durability, which acknowledges commits before the log is hardened - a deliberate, explicit trade of the D for throughput, and one to make on purpose rather than by accident.

Writing one that survives

Here is the part that catches experienced developers: in T-SQL, an error does not necessarily roll back your transaction, and it does not necessarily stop the batch. Depending on the severity, some errors abort only the current statement and execution simply continues - with your transaction still open, now missing one of its statements.

The pattern that behaves the way you expect:

Every line of that is load-bearing:

• SET XACT_ABORT ON promotes statement-level aborts to transaction-level ones, so any error terminates the whole transaction instead of leaving it half-applied and open. Turn it on for anything with an explicit BEGIN TRANSACTION.
• XACT_STATE() reports what you are allowed to do: 1 means open and committable, -1 means doomed (the only legal action is a rollback), and 0 means there is no transaction. Checking it prevents the secondary error - trying to roll back when there is nothing to roll back - that hides the real one.
• THROW re-raises the original error with its number, severity, and line intact, after the rollback has happened. Swallowing an error in a CATCH block and returning a success code is how a failed transfer becomes a support ticket six weeks later.

One more thing to know before you meet it in someone else's procedure: nested transactions are a fiction. A second BEGIN TRANSACTION increments @@TRANCOUNT and nothing else. Only the outermost COMMIT actually commits, while any ROLLBACK anywhere unwinds the entire stack regardless of depth. If you genuinely need to undo part of a transaction, the mechanism is a named save point and ROLLBACK TRANSACTION <name>.

What locks actually do

Isolation has to be implemented by something, and in the default configuration that something is locking. A lock is a claim registered against a resource for the duration of a transaction, and the engine takes them automatically. Four modes cover most of what you will see:

• Shared (S) - taken to read. Multiple readers coexist happily.
• Exclusive (X) - taken to modify. Incompatible with everything, including other X locks.
• Update (U) - taken while searching for the row to modify, then converted to X. Only one session can hold a U lock on a resource, which prevents two sessions from reading the same row and then both trying to upgrade to X - a deadlock that would otherwise happen constantly.
• Intent (IS, IX) - placed at the table and page level to advertise that row-level locks exist below. They let the engine answer "can I lock this whole table?" without inspecting every row.

Locks are also taken at different granularities: row, page, or table. Fine granularity means more concurrency and more bookkeeping; each lock costs memory. When a single statement accumulates roughly five thousand locks on one object, SQL Server performs lock escalation and trades them all for one table lock. This is why a large DELETE that seemed harmless in testing can lock an entire table in production, and why batching large modifications into chunks of a few thousand rows is standard practice.

The distinction that matters most in day-to-day debugging: blocking is not deadlocking. Blocking is a queue. Session 66 wants a row session 51 has, so it waits, and when session 51 commits it proceeds. It resolves itself. A deadlock is a cycle that will never resolve without intervention, which is the next section. Most "the database is locked up" reports are blocking, and the fix is almost never in the lock configuration - it is in how long the blocking transaction stays open.

Isolation levels

Perfect isolation would mean running every transaction one at a time. That is correct and unusably slow, so the SQL standard defines levels that trade correctness guarantees for concurrency, named after the anomalies they permit:

• Dirty read - you read a change another transaction has not committed, and it may yet roll back. You have read a value that never officially existed.
• Non-repeatable read - you read a row twice in one transaction and get different values, because someone committed a change in between.
• Phantom read - you run the same range query twice and the second run returns rows that were not there before.

Two of those rows deserve more than a table cell.

READ UNCOMMITTED, and its more familiar spelling WITH (NOLOCK), is widely treated as a free performance hint sprinkled on slow reports. It is not a hint; it is a different correctness contract. Beyond dirty reads, a scan running with no shared locks can miss committed rows entirely, or return the same row twice, if pages split underneath it while it reads. For a rough dashboard number that may be acceptable. For anything a person acts on, it is a bug waiting for a busy afternoon.

READ COMMITTED SNAPSHOT (RCSI) is a database-level option that changes how the default level is implemented. Instead of taking shared locks, readers are served the last committed version of each row from the version store in tempdb. Readers stop blocking writers and writers stop blocking readers, which eliminates an entire category of production incident. It is the default in Azure SQL Database, and for most OLTP applications it is the single highest-leverage setting available. The costs are real but modest: tempdb carries the version store, and each row gains 14 bytes of versioning overhead as it is updated.

Deadlocks

A deadlock is a cycle of waiting. Session 51 holds a lock on row A and wants row B; session 66 holds row B and wants row A. Neither can proceed and neither will ever give up voluntarily, so they would both wait forever.

SQL Server runs a deadlock monitor that looks for these cycles every few seconds. When it finds one it picks a victim - normally the transaction that would be cheapest to roll back, unless you have set DEADLOCK_PRIORITY - kills it, rolls it back completely, and returns error 1205 to that session. The other transaction proceeds as if nothing happened.

The mindset shift that matters: a deadlock is a normal condition of a concurrent system, not a defect to be eliminated. You reduce their frequency, and you make your application survive the ones that remain. Any code path that writes should be able to retry on 1205.

Where they come from, in rough order of frequency:

• Inconsistent access order. One procedure updates Orders then Inventory; another updates Inventory then Orders. Under load, they meet. Agreeing on a single order for every write path removes this entire class.
• A read and a write approaching the same rows from opposite directions. A query using a non-clustered index and then looking up into the clustered index takes its locks in the reverse order of a query working straight down the clustered index. Covering the read (see What is an index?) removes the lookup and the reversal with it.
• Lock escalation turning two row-level operations that would never have met into two table-level operations that certainly do.
• Long transactions, which do not cause deadlocks directly but widen the window in which every other cause can fire.

To see them: SQL Server's system_health Extended Events session captures the deadlock graph by default, so the last several are already recorded on your server without you configuring anything. The graph names both statements, both resources, and the victim, which is usually enough to spot the ordering problem in a minute.

And then retry. Catch 1205 outside the transaction, wait a short randomized interval so both losers do not collide again, and re-run the whole unit of work from the beginning. Two or three attempts handles essentially all of them. The one rule: retry the entire transaction, never a fragment of it - the victim was rolled back completely, so resuming from the middle applies half an operation to a state that no longer exists.

The long transaction

Almost every serious concurrency problem in production traces back to one root cause: a transaction that stayed open longer than it needed to. An open transaction holds three things hostage.

Locks, which is the visible symptom - other sessions queue behind it, and at the front of the queue the timeouts start.

The transaction log, which is the invisible one. The log cannot truncate past the oldest active transaction. A session that opened a transaction an hour ago and went to lunch pins the log at that point, and it grows until the disk fills. This happens even in SIMPLE recovery, which is why "the log file is 200 GB and we do not even take log backups" is a recognisable support case with a recognisable cause.

The version store, if you are using RCSI or snapshot isolation. Old row versions must be kept as long as any transaction might still need to see them, so one long-running reader inflates tempdb for everybody.

The practical rule is short and absolute. Never do any of these inside a transaction:

• Call an HTTP API, a payment gateway, or any other network service.
• Wait for user input. A transaction opened when a user clicked "edit" and committed when they click "save" is not a design; it is a hostage situation.
• Send an email, publish to a message queue, or write to a file.
• Run a long analytical query for a value the writes do not depend on.

Do the slow work first, gather what you need, then open the transaction, write, and commit. When something genuinely must happen exactly if and only if the transaction commits - publishing an OrderPlaced event, for example - the answer is the outbox pattern: write the message into a table inside the same transaction, and let a separate process pick it up and deliver it after the commit. The message and the data commit atomically because they are the same commit, and no external system is inside your lock window.

A note for ORM users, since this is where the pattern usually goes wrong. Entity Framework Core wraps each SaveChanges call in its own transaction automatically, which is correct and short. The problem is the "unit of work per request" pattern layered on top: a transaction opened at the start of an HTTP request and committed as the response is written. Every external call, every cache miss, and every slow view render inside that request is now happening with locks held.

Practical rules

• Keep transactions as short as the correctness requirement allows. Everything else in this article is downstream of this one line.
• Use the template. SET XACT_ABORT ON, TRY / CATCH, XACT_STATE(), THROW. Write it once, reuse it everywhere.
• Turn on RCSI for OLTP databases unless you have a specific reason not to, and confirm tempdb is provisioned for it.
• Write to objects in the same order everywhere. The cheapest deadlock fix there is, and it costs nothing but agreement.
• Retry on 1205, from outside the transaction, with a small randomized delay, two or three times.
• Do not reach for NOLOCK to make something faster. If reads are blocking, the answer is RCSI or a better index, not a weaker correctness contract.
• Batch large modifications into chunks of a few thousand rows to stay under lock escalation, with each batch its own transaction.
• Never hold a transaction across a network call or a user interaction. Not once, not for a small feature, not temporarily.

Transactions are the oldest guarantee in databases and still the most valuable one, which is why the discussion in What is NoSQL? about ACID and BASE is really a discussion about how much of this you are willing to give up for scale. In a relational system you are not giving any of it up. You just have to hold it correctly: decide deliberately what must be true together, do exactly that work inside the boundary, and get out.