Transactions

In simple words#

Learning objectives#

• Use SqlTransaction with Commit and Rollback
• Enlist every command in the transaction
• Choose an isolation level
• Use TransactionScope correctly with async code

Explanation#

connection.BeginTransaction() starts a transaction. Every command that should be part of it must have its Transaction property set.

Commit makes the changes permanent. If an exception occurs, call Rollback — or simply let the transaction be disposed without Commit, which rolls it back.

Isolation levels control what other sessions can see: ReadCommitted is the default; Snapshot avoids blocking readers; Serializable is the strictest.

TransactionScope is an ambient alternative. In async code create it with TransactionScopeAsyncFlowOption.Enabled, or it will not flow across await.

Code example: Place an order atomically#

1await using var conn = new SqlConnection(cs);
2await conn.OpenAsync();
3await using var tx = (SqlTransaction)await conn.BeginTransactionAsync();
4

5try
6{
7    await using var stock = new SqlCommand(
8        "UPDATE Products SET Stock = Stock - @qty WHERE Id = @id AND Stock >= @qty", conn, tx);
9    stock.Parameters.Add("@qty", SqlDbType.Int).Value = 2;
10    stock.Parameters.Add("@id", SqlDbType.Int).Value = 1;
11    if (await stock.ExecuteNonQueryAsync() == 0)
12        throw new InvalidOperationException("Not enough stock");
13

14    await using var order = new SqlCommand(
15        "INSERT INTO Orders (ProductId, Quantity) OUTPUT INSERTED.Id VALUES (@id, @qty)", conn, tx);
16    order.Parameters.Add("@id", SqlDbType.Int).Value = 1;
17    order.Parameters.Add("@qty", SqlDbType.Int).Value = 2;
18    int orderId = (int)(await order.ExecuteScalarAsync())!;
19

20    await tx.CommitAsync();
21    Console.WriteLine(
quot;Order #{orderId} committed");
22}
23catch (Exception ex)
24{
25    await tx.RollbackAsync();
26    Console.WriteLine(
quot;Rolled back: {ex.Message}");
27}

Order #1002 committed   — or, if stock is too low: "Rolled back: Not enough stock"

This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.

Step-by-step explanation#

1. The transaction is started on an open connection.
2. Both commands receive the transaction through their constructor.
3. If stock cannot be reduced, an exception aborts the whole operation.
4. Commit saves both changes; Rollback undoes both.

Real-world use case#

Common mistakes#

Best practices#

• Keep transactions short
• Pass the transaction to every command
• Use await using so uncommitted transactions roll back
• Consider Snapshot isolation for read-heavy systems

Remember this#

Practice exercise#

1using var scope = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled);
2await using var conn = new SqlConnection(cs);
3await conn.OpenAsync();          // enlists automatically
4// … run the two commands without setting Transaction …
5scope.Complete();                // commit when disposed

Mini quiz#

Want more? Practise all ADO.NET MCQs · ADO.NET interview questions

Related topics#

My notes#

Saved in this browser.