Published / updated29 Aug 2026

Transactions in ADO.NET

Before you start

You need: CRUD and procedures (Article 05), and transactions (Track 06 Article 09).

Time: about 45 minutes, plus the practice.

Learning objective

Write multi-statement operations that leave no partial state, and recover correctly from deadlocks and timeouts.

Topics

• SqlTransaction and the command association rule
• Structuring try / catch around a transaction
• Isolation levels from C#
• Save points
• Deadlocks and retry
• TransactionScope
• Where transactions belong in the layering

SqlTransaction

public FeePaymentResult RecordPayment(FeePayment payment)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            try
            {
                int paymentId = InsertPayment(connection, transaction, payment);
                UpdateAccountBalance(connection, transaction, payment);

                transaction.Commit();

                return new FeePaymentResult { PaymentId = paymentId };
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

Two statements that must both happen: the payment row and the balance update. Without a transaction, a failure between them leaves a payment recorded against an unchanged balance — the student's receipt says paid, the system says owing.

Every command needs the transaction

private static int InsertPayment(
    SqlConnection connection, SqlTransaction transaction, FeePayment payment)
{
    const string sql = @"
        INSERT INTO dbo.FeePayment (PublicId, SchoolId, FeeAccountId, Amount,
                                    PaidOn, PaymentMode, CollectedBy)
        VALUES (@PublicId, @SchoolId, @FeeAccountId, @Amount,
                @PaidOn, @PaymentMode, @CollectedBy);

        SELECT CAST(SCOPE_IDENTITY() AS INT);";

    using (SqlCommand command = new SqlCommand(sql, connection, transaction))
    {
        command.Parameters.Add("@PublicId", SqlDbType.UniqueIdentifier).Value = Guid.NewGuid();
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = payment.SchoolId;
        command.Parameters.Add("@FeeAccountId", SqlDbType.Int).Value = payment.FeeAccountId;

        SqlParameter amount = command.Parameters.Add("@Amount", SqlDbType.Decimal);
        amount.Precision = 18;
        amount.Scale = 2;
        amount.Value = payment.Amount;

        command.Parameters.Add("@PaidOn", SqlDbType.DateTime2).Value = DateTime.UtcNow;
        command.Parameters.Add("@PaymentMode", SqlDbType.TinyInt).Value = (byte)payment.PaymentMode;
        command.Parameters.Add("@CollectedBy", SqlDbType.NVarChar, 100).Value = payment.CollectedBy;

        return (int)command.ExecuteScalar()!;
    }
}

The third constructor argument is the transaction. Omit it and you get:

ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction.

An awkward message for a simple omission — and one that appears only at run time, on the first command you forgot.

command.Transaction = transaction is the equivalent when the command is built elsewhere.

Structuring the try/catch

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        // work
        transaction.Commit();
    }
    catch
    {
        try
        {
            transaction.Rollback();
        }
        catch (Exception rollbackException)
        {
            _logger.LogError(rollbackException, "Rollback failed after a transaction error.");
        }

        throw;
    }
}

The nested try around Rollback matters. If the connection has already been lost, Rollback throws too — and that secondary exception replaces the original, hiding the real cause. Log it and rethrow the first.

throw; not throw ex;. throw ex resets the stack trace to the catch block, so the production log points at your error handler rather than the failing line.

Disposing a SqlTransaction without committing rolls it back, so the using is a safety net — but write the explicit Rollback anyway, so the intent is visible.

Validate before opening the transaction

public FeePaymentResult RecordPayment(FeePayment payment)
{
    if (payment.Amount <= 0)
    {
        throw new ArgumentOutOfRangeException(nameof(payment), "Amount must be positive.");
    }

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        connection.Open();

        decimal outstanding = GetOutstanding(connection, payment.SchoolId, payment.FeeAccountId);

        if (payment.Amount > outstanding)
        {
            throw new PaymentExceedsBalanceException(payment.Amount, outstanding);
        }

        using (SqlTransaction transaction = connection.BeginTransaction())
        {
            // ...
        }
    }
}

Every statement inside a transaction holds locks until commit. Checks that can fail belong outside it, so a rejected payment never takes a lock at all.

The read-then-write gap this creates is closed by the database: the CHECK constraint on FeeAccount rejects an overpayment even if two clerks pass the check simultaneously. The application check gives a good message; the constraint gives the guarantee.

Isolation levels

using (SqlTransaction transaction =
       connection.BeginTransaction(IsolationLevel.ReadCommitted))

Level	Use
ReadUncommitted	Almost never — reads uncommitted data
ReadCommitted	Default; correct for most work
RepeatableRead	Re-reading the same rows must give the same answer
Serializable	Strongest; heavy locking
Snapshot	Consistent point-in-time read with no blocking

Raising the level increases blocking. Start at ReadCommitted and change only with a specific reason you can state.

ReadUncommitted — the C# equivalent of WITH (NOLOCK) — can skip rows entirely or read the same row twice during a scan. On a fee total that is a wrong number with no error. Enable READ_COMMITTED_SNAPSHOT on the database instead when blocking is the problem.

Save points

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        InsertExam(connection, transaction, exam);

        transaction.Save("BeforeResults");

        try
        {
            InsertResults(connection, transaction, results);
        }
        catch (SqlException ex) when (ex.Number == 2627)
        {
            transaction.Rollback("BeforeResults");   // undo only the results
            _logger.LogWarning("Duplicate results skipped for exam {ExamId}", exam.Id);
        }

        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
}

Rollback(name) undoes back to a save point and leaves the transaction open. Rollback() with no argument discards everything.

Save points are genuinely useful for imports where one bad batch should not lose the whole run. They are worth avoiding elsewhere — the control flow is hard to follow, and a nested transaction in SQL Server is not independent.

Deadlocks and retry

Two transactions each holding a lock the other needs. SQL Server kills one and returns error 1205.

private const int DeadlockErrorNumber = 1205;
private const int TimeoutErrorNumber = -2;

public T ExecuteWithRetry<T>(Func<T> operation, int maxAttempts = 3)
{
    int attempt = 0;

    while (true)
    {
        attempt++;

        try
        {
            return operation();
        }
        catch (SqlException ex)
            when ((ex.Number == DeadlockErrorNumber || ex.Number == TimeoutErrorNumber)
                  && attempt < maxAttempts)
        {
            int delayMs = 100 * (int)Math.Pow(2, attempt - 1);

            _logger.LogWarning(
                "Transient SQL error {Number} on attempt {Attempt}. Retrying in {Delay}ms.",
                ex.Number, attempt, delayMs);

            Thread.Sleep(delayMs);
        }
    }
}

FeePaymentResult result = ExecuteWithRetry(() => RecordPayment(payment));

Three rules for retry:

Retry only transient errors. 1205 and -2 are transient. A constraint violation (2627, 547) will fail identically every time — retrying it wastes time and hides the real problem.

Back off exponentially. Retrying immediately makes contention worse. 100 ms, 200 ms, 400 ms gives the other transaction time to finish.

Retry the whole operation, not one statement. The transaction was rolled back, so everything must run again from the start.

Preventing deadlocks

Retry is the recovery; consistent ordering is the fix.

Transaction A: FeeAccount 42 → FeePayment 108
Transaction B: FeePayment 108 → FeeAccount 42     ← deadlock

If every operation touches FeeAccount before FeePayment, this deadlock cannot form. Deadlocks are almost always a code-ordering problem, not a database problem.

Also keep transactions short. The longer locks are held, the wider the window for a cycle.

TransactionScope

using (TransactionScope scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions
    {
        IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
        Timeout = TimeSpan.FromSeconds(30)
    },
    TransactionScopeAsyncFlowOption.Enabled))
{
    _studentRepository.Create(student);
    _feeRepository.CreateAccount(feeAccount);

    scope.Complete();
}

TransactionScope enlists connections automatically, so several repositories share one transaction without passing a SqlTransaction between them.

Three cautions:

• Complete() is required. Disposing without it rolls back. Forgetting it is a silent data-loss bug — no exception, nothing saved.
• TransactionScopeAsyncFlowOption.Enabled is mandatory with async. Without it the scope does not flow across an await, and the continuation runs outside the transaction.
• Two different connection strings can escalate to a distributed transaction, which needs MSDTC. On modern .NET this throws rather than escalating. Keep a scope to one database.

For a single database, an explicit SqlTransaction is simpler and has no escalation risk. Reach for TransactionScope when several repositories genuinely must commit together.

Where transactions belong

Not in the repository. A repository method that opens its own transaction cannot be composed:

// Cannot be combined with anything else atomically
public void Create(Student student)
{
    using (SqlTransaction transaction = connection.BeginTransaction())
    {
        // ...
    }
}

Enrolling a student means creating the student and their fee account, atomically. If each opens its own transaction, a failure on the second leaves the first committed.

Put the transaction in the service layer, which knows the business operation, and let repositories accept a connection and transaction:

public class EnrolmentService
{
    private readonly string _connectionString;
    private readonly IStudentRepository _studentRepository;
    private readonly IFeeRepository _feeRepository;

    public void EnrolStudent(Student student, FeeAccount feeAccount)
    {
        using (SqlConnection connection = new SqlConnection(_connectionString))
        {
            connection.Open();

            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    int studentId = _studentRepository.Create(connection, transaction, student);

                    feeAccount.StudentId = studentId;
                    _feeRepository.CreateAccount(connection, transaction, feeAccount);

                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
    }
}

The repository does one thing and says nothing about transaction boundaries. The service decides what must be atomic. This is the shape that survives contact with real requirements.

Errors you will hit

Message	Cause	Fix
ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction	A command inside the transaction was not given it	Pass transaction to every command
Half the operation survives a rollback	Same cause — one command committed independently	Same fix
This SqlTransaction has completed; it is no longer usable	Used after commit or rollback	Create a new one
Deadlock victim message	Two transactions taking locks in different orders	Access tables in the same order everywhere
Two concurrent payments and one is lost	Read-modify-write across a round trip	Let the database do the arithmetic

The first two rows are the same bug. Miss the transaction argument on one command and a rollback leaves the database half-updated, with no error anywhere.

Common mistakes

• No transaction around related writes
• Forgetting to pass the transaction to a command
• throw ex; instead of throw;
• Rollback throwing and hiding the original exception
• Validation inside the transaction rather than before it
• Long transactions holding locks across a user prompt or HTTP call
• ReadUncommitted to make blocking disappear
• Retrying constraint violations
• Retrying with no back-off
• Retrying one statement instead of the whole operation
• Transactions opened inside repositories, so operations cannot compose
• TransactionScope without Complete()
• TransactionScope with async and no AsyncFlowOption
• Inconsistent table ordering across operations

Practice

The course assignment here is add transaction handling.

1. Write RecordPayment with a SqlTransaction covering the insert and the balance update.
2. Omit the transaction argument on the second command. Record the exact exception.
3. Throw an exception deliberately between the two statements. Confirm the payment does not exist and the balance is unchanged.
4. Remove the transaction entirely and repeat. Confirm the payment exists and the balance is wrong — this is what the transaction prevents.
5. Move the overpayment validation inside the transaction, then back outside. Explain which is better and why.
6. Cause a deadlock: two console apps updating FeeAccount and FeePayment in opposite order, in a loop. Catch it and confirm SqlException.Number == 1205.
7. Add ExecuteWithRetry with exponential back-off. Confirm the operation now succeeds.
8. Fix the root cause by making both apps touch the tables in the same order. Confirm no deadlock occurs at all.
9. Set CommandTimeout = 1 on a command containing WAITFOR DELAY '00:00:05' inside a transaction. Confirm error -2 and that the rollback happened.
10. Write EnrolStudent creating a student and a fee account atomically. Force a failure on the second and confirm neither exists.
11. Rewrite it with TransactionScope, then remove scope.Complete(). Confirm nothing is saved and no exception is thrown.

Then run the course debugging exercise — trace an uncommitted transaction. In SSMS, BEGIN TRANSACTION and update a FeeAccount without committing. Run your application against the same row and observe the block. Find both sessions with sys.dm_exec_requests, identify the blocker, then commit and watch the application complete.

Exercise 4 is the one that makes the case, and exercise 11 is the TransactionScope trap that loses data silently.

You can now

• Write a multi-statement operation that leaves no partial state
• Pass the transaction to every command inside it
• Say what a missing transaction argument does on rollback
• Let the database do increment arithmetic
• Avoid deadlocks by fixing table access order

Review questions

1. What happens when a command inside a transaction is not given that transaction?
2. Why must validation happen before BeginTransaction?
3. Which SQL error numbers are worth retrying, and which are not?
4. Why should a repository method not open its own transaction?

Next: Dapper fundamentals