Two updates that are supposed to happen together will, eventually, only half-happen — unless you explicitly tell the database to treat them as one.
Imagine a "transfer money" feature between two bank accounts. The logic is simple enough: subtract $500 from Account A, add $500 to Account B. Two SQL statements, run one after another:
UPDATE Accounts SET Balance = Balance - 500 WHERE Id = @fromAccountId;
UPDATE Accounts SET Balance = Balance + 500 WHERE Id = @toAccountId;Now imagine the server loses power, the process crashes, or the network connection drops between those two statements — after the first one commits, before the second one runs. Account A is now $500 lighter. Account B never received it. $500 has vanished from the system entirely, with no error message, no exception even — just quietly gone.
This isn't a rare, exotic failure. Processes crash, connections drop, servers restart for maintenance, and it happens at the worst possible moment purely by bad luck. Any time your application logic requires more than one database change to happen together, you have exactly this risk — and it's a risk with a very well-established, very reliable fix: transactions.
In this lesson, you'll learn what a database transaction is, the ACID properties that describe what it guarantees, and how to use DbTransaction with BeginTransaction, Commit, and Rollback to make multi-step operations safe.
A transaction is a way of telling the database: "treat everything I'm about to do as a single, indivisible unit. Either all of it happens, or none of it happens — never leave it half-done." You start one, run your statements, and then either commit (make every change permanent, all at once) or roll back (undo every change made since the transaction started, as if none of it ever ran).
In ADO.NET, a transaction is represented by DbTransaction (concretely, SqlTransaction for SQL Server). You obtain one by calling connection.BeginTransaction() on an already-open connection, attach it to every DbCommand you run as part of that unit of work via command.Transaction = transaction, and finish with either transaction.Commit() or transaction.Rollback().
await using SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync();
try
{
// every command that should be part of this all-or-nothing unit:
command.Transaction = transaction;
await command.ExecuteNonQueryAsync();
await transaction.CommitAsync(); // make it all permanent
}
catch
{
await transaction.RollbackAsync(); // undo everything since BeginTransaction
throw;
}Without a transaction, every SQL statement you send is its own independent, immediately-permanent change. If your business operation genuinely requires several statements to succeed together — debit one account and credit another, decrement stock and create an order row, insert a parent row and its child rows — then any interruption between those statements (a crash, a dropped connection, an unhandled exception, even another concurrent process interfering) can leave the database in a state your application logic never intended to produce and never accounted for.
A transaction groups multiple statements so the database treats them as a single unit for the purpose of durability and visibility: either every statement's effect becomes permanent together (a commit), or none of them do (a rollback, or the transaction simply never being committed before the connection drops). There is no state in between where only some of the statements have taken effect.
Transactions are formally described by four properties, commonly abbreviated ACID:
| Property | Guarantee |
|---|---|
| Atomicity | All statements in the transaction succeed together, or none of them take effect — no partial result. |
| Consistency | A transaction moves the database from one valid state to another, never violating constraints (foreign keys, unique indexes, check constraints) along the way. |
| Isolation | Concurrent transactions don't see each other's uncommitted, in-progress changes — to a large extent, each transaction behaves as if it's running alone. |
| Durability | Once a transaction is committed, its changes survive — even a server crash immediately afterward won't lose a committed change. |
You don't implement ACID yourself — the database engine provides it. Your job is just to correctly mark the boundaries: where a transaction starts, and whether it ends in Commit() or Rollback().
The crash is identical in both scenarios. The outcome is completely different, because the transaction ensured the debit was never made permanent on its own.
await connection.OpenAsync();
await using DbTransaction transaction = await connection.BeginTransactionAsync();command.Transaction = transaction;public async Task TransferAsync(SqlConnection connection, int fromAccountId, int toAccountId, decimal amount)
{
await using SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync();
try
{
await using SqlCommand debit = connection.CreateCommand();
debit.Transaction = transaction;
debit.CommandText = "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id AND Balance >= @amount";
debit.Parameters.AddWithValue("@amount", amount);
debit.Parameters.AddWithValue("@id", fromAccountId);
int rowsDebited = await debit.ExecuteNonQueryAsync();
if (rowsDebited == 0)
throw new InvalidOperationException("Insufficient funds or account not found.");
await using SqlCommand credit = connection.CreateCommand();
credit.Transaction = transaction;
credit.CommandText = "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id";
credit.Parameters.AddWithValue("@amount", amount);
credit.Parameters.AddWithValue("@id", toAccountId);
await credit.ExecuteNonQueryAsync();
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw; // let the caller know the transfer didn't happen
}
}Two details worth pausing on: the debit's WHERE clause includes AND Balance >= @amount so an account can never go negative, and checking rowsDebited == 0 (recall ExecuteNonQuery's return value from the previous lesson) is what triggers the rollback for insufficient funds — before the credit ever runs.
An e-commerce checkout is another textbook transaction scenario: creating an Order row, inserting one or more OrderLine rows, and decrementing Products.Stock for each item all need to succeed together. If stock decrement fails for one item (say, a concurrent sale just sold the last unit), the order and any order lines already inserted must be rolled back too — otherwise the customer has a confirmed order for an item that's no longer actually available, or an order that's missing half its line items.
await using SqlTransaction transaction = (SqlTransaction)await connection.BeginTransactionAsync();
try
{
int orderId = await InsertOrderAsync(connection, transaction, customerId);
foreach (var item in cartItems)
{
await InsertOrderLineAsync(connection, transaction, orderId, item);
int rowsUpdated = await DecrementStockAsync(connection, transaction, item.ProductId, item.Quantity);
if (rowsUpdated == 0)
throw new InvalidOperationException($"Product {item.ProductId} is out of stock.");
}
await transaction.CommitAsync();
}
catch
{
await transaction.RollbackAsync();
throw;
}Notice this is the exact same all-or-nothing structure as the bank transfer — a different domain, the same underlying need. This pattern comes up constantly: any time "step 2 only makes sense if step 1 also happened," you're looking at a transaction boundary.
Think of a transaction like editing a shared document in draft mode. While you're making changes, nobody else sees them — they're still visible only to you, in your draft. If you decide the edit was a mistake halfway through, you can discard the whole draft and the document reverts to exactly what it was before you started — none of your half-finished edits leak through.
Only when you hit "Publish" (Commit()) do all your changes become visible to everyone at once, as a single, complete update. There's no moment where other readers see half your edit — that's isolation and atomicity working together, the same guarantee a database transaction gives your SQL statements.
Where the analogy stops: a real "discard draft" in a document editor is a manual choice you make. A database transaction is discarded (rolled back) automatically the moment something goes wrong and you never called Commit() — you don't have to remember to clean up the partial changes yourself.
No — a transaction only governs what the database does. If your C# method sends an email, calls another web API, or writes a file in the middle of a database transaction, none of that is rolled back if the transaction later fails. Keep non-database side effects (emails, external API calls) outside the transaction, or trigger them only after a successful Commit().
A single standalone statement is already atomic on its own — SQL Server implicitly wraps every individual statement in its own transaction even if you never call BeginTransaction. You only need an explicit transaction when two or more statements must succeed or fail together as one unit.
Calling connection.BeginTransaction() but forgetting command.Transaction = transaction; on one of the commands. That command runs outside the transaction and commits immediately on its own, regardless of what happens to the rest — a subtle bug that's easy to miss until it causes exactly the kind of partial update transactions exist to prevent. Set Transaction on every command sharing the unit of work — some teams write a small helper method that always sets it, precisely to avoid forgetting.
Letting an exception propagate up without calling Rollback() first, and without disposing the transaction. If the connection stays open elsewhere, the transaction can be left dangling, holding locks other operations are now waiting on. Always wrap transaction work in try/catch, roll back explicitly on failure, and use await using on the transaction so it's disposed even if you forget the explicit rollback.
Beginning a transaction, then calling a slow external API or waiting on user input before running the remaining statements and committing. The longer a transaction stays open, the longer it holds locks on the affected rows — blocking other operations that need those same rows. Keep the time between BeginTransaction and Commit/Rollback as short as possible — do slow, non-database work before starting the transaction or after it ends.
Adding BeginTransaction/Commit around one single UPDATE statement. It adds ceremony and a small amount of overhead without adding any real safety — that one statement was already atomic on its own. Reach for an explicit transaction only when two or more statements genuinely need to succeed or fail together.
| Scenario | Need an explicit transaction? |
|---|---|
| A single INSERT/UPDATE/DELETE statement | No — it's already atomic by itself |
| Debiting one account and crediting another | Yes — both must succeed or neither should |
| Inserting an order plus its order lines plus a stock decrement | Yes — a partial insert would corrupt the order |
| A read-only SELECT query | No — nothing is being changed |
| Two unrelated updates to two unrelated tables that have no logical connection | Usually no — forcing them together adds coupling and lock contention for no real benefit |
You've seen why the bank-transfer scenario needs a transaction, and how ACID describes the guarantee. Let's confirm it clicked.
1. In the bank transfer example, the debit statement succeeds and the process crashes before the credit statement runs. If both statements ran inside a transaction that was never committed, what state is the database in after the crash?
Correct: B
Why B is correct: Because the changes were never committed, atomicity guarantees that none of them became permanent. The crash leaves the uncommitted transaction rolled back — both accounts are exactly as they were before the transfer began.
Why A is incorrect: This is exactly the outcome a transaction prevents — it would be the result if the two statements had run without any transaction wrapping them.
Why C is incorrect: The database isn't corrupted — rollback is a normal, built-in recovery mechanism, not a manual repair process.
Why D is incorrect: The credit statement never even ran, since the crash happened before it — there's no way its effect could appear.
Reinforcement: An uncommitted transaction is treated as if it never happened at all — that's exactly the safety property atomicity provides.
2. Which ACID property specifically guarantees that once a transaction's Commit() call returns successfully, its changes will survive even an immediate server crash?
Correct: D
Why D is correct: Durability is specifically the promise that once a transaction is committed, its changes are permanent — a crash immediately afterward cannot undo them, because the change was already durably recorded before Commit() returned.
Why A is incorrect: Atomicity is about all-or-nothing execution of the statements within the transaction, not about survival after a crash post-commit.
Why B is incorrect: Consistency is about the database never landing in a state that violates its rules/constraints — it's not specifically about crash survival.
Why C is incorrect: Isolation is about concurrent transactions not seeing each other's uncommitted changes — unrelated to what happens after a successful commit.
Reinforcement: Each ACID letter maps to a distinct guarantee — durability specifically covers "survives a crash after commit."
3. A developer begins a transaction, runs an UPDATE, then calls a slow third-party payment API, and only afterward runs a second UPDATE and commits. What's the main problem with this design?
Correct: B
Why B is correct: A transaction holds locks on the rows it has modified for as long as it stays open. Doing slow, unrelated work (like an external API call) in the middle of a transaction needlessly extends how long those rows stay locked, increasing contention for anything else that needs them.
Why A is incorrect: Transactions can contain any number of statements — that's the whole point of grouping multiple statements into one unit.
Why C is incorrect: A transaction only governs database changes — an external API call is not part of the database and is never rolled back by a database transaction, regardless of when it happens relative to the transaction.
Why D is incorrect: Ordinary C# code can run between statements inside a transaction — the problem isn't that it's disallowed, it's that doing so while the transaction is open extends lock duration.
Reinforcement: Keep transactions short — do slow or external work before starting one or after it ends, not in the middle.
4. Why does a single, standalone UPDATE statement not need an explicit BeginTransaction()/Commit() call?
Correct: B
Why B is correct: The database engine already wraps every individual statement in its own implicit transaction — it either fully applies or doesn't apply at all. Explicit transactions become necessary only when multiple statements must succeed or fail together as one unit.
Why A is incorrect: UPDATE statements are fully covered by ACID guarantees — there's no exemption; a single UPDATE is simply already atomic without needing an explicit wrapper.
Why C is incorrect: Explicit transactions work with any combination of statements — INSERT, UPDATE, DELETE, and even SELECT can all participate in a transaction.
Why D is incorrect: An UPDATE inside an explicit transaction absolutely can be rolled back — that's exactly what makes transactions useful in the first place.
Reinforcement: Reach for an explicit transaction specifically when two or more statements need to succeed or fail as a single unit — not for every single statement by default.
You now understand transactions, ACID, and how to keep multi-step database operations safe. Next up: leaving raw ADO.NET behind and meeting Entity Framework Core.
dotnetmadeeasy.com — Learn C# and .NET, the right way.