Transactions and Concurrency in C#: Two Clerks, One Invoice – Data Access | Lesson 64

Published / updated7 Oct 2026

Introduction

Lesson 24 showed transactions in SQL: several statements that succeed or fail together. Lesson 25 showed what happens when two sessions touch the same rows. This lesson brings both ideas into C#.

The example is the fee counter. A payment must save the receipt and the invoice's new status together. And when two clerks open the same invoice, the second must not be allowed to take money the first one already took.

By the end of this lesson, you will be able to:

Use SaveChangesAsync as a unit of work, and explicit transactions when one is not enough.
Explain why a rollback does not undo objects in memory.
Describe the lost update problem, and pessimistic and optimistic concurrency.
Add optimistic concurrency with a ROWVERSION column and EF Core.
Translate a concurrency conflict into an error the application understands.
Choose between ADO.NET, Dapper, and EF Core for a task.

Why this matters

Money is where data mistakes hurt most. A receipt without a matching invoice update, or a payment taken twice, means angry parents, wrong accounts, and hours of manual fixing.

These bugs never appear when you test alone: one person, one screen, one click. They appear on the first busy morning, when two clerks serve two parents of the same family at once.

The concept

Example: transactions in C#

Every data tool in this module can run a transaction:

Tool	How	Seen in
ADO.NET	`connection.BeginTransaction()`, then pass it to every `SqlCommand`	Lesson 57
Dapper	The same `SqlTransaction`, passed as the `transaction` argument	Lesson 58
EF Core, automatic	Each `SaveChangesAsync` is one transaction	Lesson 59
EF Core, explicit	`db.Database.BeginTransactionAsync()`, then `CommitAsync` or `RollbackAsync`	This lesson
Any tool	`TransactionScope`: every connection opened inside joins it	Lessons 62 and 63

A unit of work is the business operation that must be atomic, such as "take a payment". The best case is one SaveChangesAsync for the whole unit: EF Core then handles the transaction for you.

Example: the lost update

Clerk A and clerk B both open an invoice with 6,000 due. Clerk A takes 6,000 and saves. Clerk B, still looking at "6,000 due", takes 6,000 too, and saves.

Each clerk followed the rules, based on what their screen showed. Together, they took 12,000 for a 6,000 balance. This is the lost update problem: the second save was based on data that was no longer true.

Example: pessimistic and optimistic concurrency

Approach	Idea	SMS example	Good when
Pessimistic	Lock the row while working, so nobody else can change it	`usp_RecordFeePayment` reads with `UPDLOCK` (Lesson 22)	Conflicts are frequent; the work is short and inside one call
Optimistic	Do not lock; at save time, check that nobody changed the row	A `ROWVERSION` check in EF Core	Conflicts are rare; users think for a while between reading and saving

A clerk may look at an invoice for a minute before taking the money. Holding a database lock for that minute would block everyone else, so screens use the optimistic approach.

Example: what a rowversion is

A ROWVERSION column holds a number that SQL Server changes automatically every time the row is updated. You never set it yourself.

The script adds one to FeeInvoices, then updates one invoice without changing any value. The version still changes:

database/064-rowversion.sql
-- Lesson 64: a version number on every fee invoice, for optimistic concurrency.
-- SQL Server changes a ROWVERSION value automatically every time the row is updated, so
-- "update this invoice only if its version is still the one I read" detects a change by
-- someone else. Safe to run more than once.
USE SchoolDb;
GO

IF COL_LENGTH(N'dbo.FeeInvoices', N'RowVersion') IS NULL
    ALTER TABLE dbo.FeeInvoices ADD RowVersion ROWVERSION NOT NULL;
GO

-- Check: update one invoice without changing anything, and watch its version change.
DECLARE @Before BINARY(8) = (SELECT RowVersion FROM dbo.FeeInvoices WHERE Id = 4);
UPDATE dbo.FeeInvoices SET Status = Status WHERE Id = 4;
SELECT
    InvoiceNumber,
    @Before    AS VersionBefore,
    RowVersion AS VersionAfter
FROM dbo.FeeInvoices
WHERE Id = 4;
GO

Output
InvoiceNumber        VersionBefore      VersionAfter
-------------------- ------------------ ------------------
INV-2026-000004      0x0000000000021B14 0x0000000000021B1D

Saving with WHERE Id = @id AND RowVersion = @versionRead updates the row only if nobody has changed it since it was read. If the version moved, zero rows match, and EF Core knows someone else got there first.

Apply it to the SMS

Step 1: the concurrency token

FeeInvoiceConfiguration maps the new column as a shadow property: a property EF Core tracks that does not exist in the C# class. FeeInvoice in Core stays free of database details.

FeeInvoiceConfiguration (part)
// Lesson 64: the ROWVERSION column (database/064-rowversion.sql) as a concurrency token.
// A shadow property: EF Core tracks it, but FeeInvoice in Core has no such property.
// Every UPDATE then ends with "AND RowVersion = <the value read>".
builder.Property<byte[]>("RowVersion").IsRowVersion();

Show src/SchoolApp.Data/EfCore/Configurations/FeeConfigurations.cs

src/SchoolApp.Data/EfCore/Configurations/FeeConfigurations.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata.Builders;
using SchoolApp.Core.Entities;

namespace SchoolApp.Data.EfCore.Configurations;

// Lesson 60: dbo.FeeInvoices and dbo.FeePayments: one invoice has many payments.
public class FeeInvoiceConfiguration : IEntityTypeConfiguration<FeeInvoice>
{
    public void Configure(EntityTypeBuilder<FeeInvoice> builder)
    {
        builder.ToTable("FeeInvoices");
        builder.Property(i => i.InvoiceNumber).HasMaxLength(20).IsUnicode(false);

        // No setters: mapped because they are named here, filled through the constructor.
        builder.Property(i => i.TotalAmount).HasPrecision(10, 2);
        builder.Property(i => i.DiscountAmount).HasPrecision(10, 2);
        builder.Property(i => i.Status).HasMaxLength(13).IsUnicode(false).HasConversion<string>();

        builder.HasOne(i => i.Enrollment).WithMany().HasForeignKey(i => i.EnrollmentId);

        // One-to-many: the invoice's Payments list and each payment's FeeInvoice point at each other.
        builder.HasMany(i => i.Payments).WithOne(p => p.FeeInvoice).HasForeignKey(p => p.FeeInvoiceId);

        // Lesson 64: the ROWVERSION column (database/064-rowversion.sql) as a concurrency token.
        // A shadow property: EF Core tracks it, but FeeInvoice in Core has no such property.
        // Every UPDATE then ends with "AND RowVersion = <the value read>".
        builder.Property<byte[]>("RowVersion").IsRowVersion();

        builder.HasAuditColumns();
    }
}

public class FeePaymentConfiguration : IEntityTypeConfiguration<FeePayment>
{
    public void Configure(EntityTypeBuilder<FeePayment> builder)
    {
        builder.ToTable("FeePayments");
        builder.Property(p => p.ReceiptNumber).HasMaxLength(20).IsUnicode(false);
        builder.Property(p => p.Amount).HasPrecision(10, 2);
        builder.Property(p => p.PaidOn).HasPrecision(0);
        builder.Property(p => p.PaymentMode).HasMaxLength(12).IsUnicode(false).HasConversion<string>();
        builder.Property(p => p.TransactionReference).HasMaxLength(50).IsUnicode(false);

        builder.HasAuditColumns();
        builder.Property(p => p.DeletedAt).HasPrecision(0);

        // A global query filter: every query on FeePayments gets "IsDeleted = 0" added.
        builder.HasQueryFilter(SoftDeleteFilter.Name, p => !p.IsDeleted);
    }
}

Step 2: a payment service with a unit of work

Core gets an interface for what payments need, and a service that uses it:

src/SchoolApp.Core/Interfaces/IFeeInvoiceRepository.cs
using SchoolApp.Core.Entities;

namespace SchoolApp.Core.Interfaces;

// Lesson 64: what fee payments need from storage. SaveChangesAsync saves every change to the
// loaded invoices together, or none of them: a unit of work (Lesson 62).
public interface IFeeInvoiceRepository
{
    // The invoice with its payments, so its balance is the real one (Lesson 60).
    Task<FeeInvoice?> GetWithPaymentsAsync(string invoiceNumber, CancellationToken cancellationToken = default);

    // The next receipt number, from the same sequence as usp_RecordFeePayment.
    Task<string> NextReceiptNumberAsync(CancellationToken cancellationToken = default);

    // Throws SchoolRuleException 50014 if someone else changed the invoice after it was loaded.
    Task SaveChangesAsync(CancellationToken cancellationToken = default);
}

src/SchoolApp.Core/Services/FeePaymentService.cs
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Core.Services;

// Lesson 64: taking a fee payment. The invoice checks the rules (50011 to 50013), and the
// repository saves the payment and the invoice's new status together, or neither.
public class FeePaymentService
{
    private readonly IFeeInvoiceRepository _invoices;

    public FeePaymentService(IFeeInvoiceRepository invoices)
    {
        _invoices = invoices;
    }

    public async Task<FeePayment> PayAsync(string invoiceNumber, decimal amount, PaymentMode paymentMode,
                                           CancellationToken cancellationToken = default)
    {
        FeeInvoice invoice = await _invoices.GetWithPaymentsAsync(invoiceNumber, cancellationToken)
            ?? throw new SchoolRuleException(50010, "Invoice not found.");

        string receiptNumber = await _invoices.NextReceiptNumberAsync(cancellationToken);
        FeePayment payment = invoice.RecordPayment(amount, paymentMode, receiptNumber);

        await _invoices.SaveChangesAsync(cancellationToken);
        return payment;
    }
}

The invoice checks the rules from Lesson 38 and adds the payment to its Payments list (Lesson 60). Then one SaveChangesAsync saves the new payment and the new status together.

Step 3: the EF Core repository

src/SchoolApp.Data/EfCore/EfFeeInvoiceRepository.cs
using Microsoft.EntityFrameworkCore;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;

namespace SchoolApp.Data.EfCore;

// Lesson 64: fee invoices with EF Core. The DbContext is the unit of work: SaveChangesAsync writes
// the new payment and the invoice's status in one transaction, checking the invoice's RowVersion.
public class EfFeeInvoiceRepository : IFeeInvoiceRepository
{
    private readonly SchoolDbContext _db;

    public EfFeeInvoiceRepository(SchoolDbContext db)
    {
        _db = db;
    }

    public Task<FeeInvoice?> GetWithPaymentsAsync(string invoiceNumber, CancellationToken cancellationToken = default)
    {
        return _db.FeeInvoices
            .Include(i => i.Payments)
            .SingleOrDefaultAsync(i => i.InvoiceNumber == invoiceNumber, cancellationToken);
    }

    // The same format as usp_RecordFeePayment: RCP-<year>-NNNNNN. ToListAsync, not SingleAsync:
    // NEXT VALUE FOR is not allowed inside the subquery that SingleAsync would wrap around it.
    public async Task<string> NextReceiptNumberAsync(CancellationToken cancellationToken = default)
    {
        List<long> next = await _db.Database
            .SqlQuery<long>($"SELECT NEXT VALUE FOR dbo.ReceiptNumbers AS Value")
            .ToListAsync(cancellationToken);
        return $"RCP-{DateTime.UtcNow.Year}-{next[0]:000000}";
    }

    // A concurrency conflict becomes a school rule error that callers understand.
    public async Task SaveChangesAsync(CancellationToken cancellationToken = default)
    {
        try
        {
            await _db.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateConcurrencyException ex)
        {
            throw new SchoolRuleException(50014, "The invoice was changed by someone else. Load it again and retry.", ex);
        }
    }
}

When the version check fails, EF Core throws DbUpdateConcurrencyException. The repository turns it into SchoolRuleException 50014, keeping the original as InnerException. Callers in Core and, later, the Web API see a school error, not an EF Core type.

SchoolRuleException gained a constructor that accepts that inner exception:

src/SchoolApp.Core/Exceptions/SchoolRuleException.cs
namespace SchoolApp.Core.Exceptions;

// Lesson 37: our own exception type for broken school rules.
// ErrorNumber uses the same numbers as the SQL code, such as 50001 for impossible marks.
public class SchoolRuleException : Exception
{
    public int ErrorNumber { get; }

    public SchoolRuleException(int errorNumber, string message) : base(message)
    {
        ErrorNumber = errorNumber;
    }

    // Lesson 64: keeps the original error (such as a database exception) as InnerException,
    // so logs still show what really happened underneath.
    public SchoolRuleException(int errorNumber, string message, Exception innerException) : base(message, innerException)
    {
        ErrorNumber = errorNumber;
    }
}

Step 4: the program

The program creates its own invoice, INV-DEMO-064, for 10,000, uses it in three experiments, and deletes it at the end. That keeps SchoolDb exactly as it was.

Show src/SchoolApp.Console/Program.cs

src/SchoolApp.Console/Program.cs
// Lesson 64: transactions and concurrency in code.
// The program creates its own demo invoice, uses it, and deletes it at the end, so SchoolDb is unchanged.
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Enums;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Services;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;

SqlConnectionFactory connections = SqlConnectionFactory.FromEnvironment();
bool showSql = false;
SchoolDbContext NewContext() => SchoolDbContextFactory.Create(connections, message =>
{
    if (showSql && message.Contains("SET NOCOUNT ON"))   // only SaveChanges batches start like this
    {
        // Skip the first line (timing and parameters); print the SQL itself.
        Console.WriteLine(string.Join(Environment.NewLine, message.Split('\n').Skip(1).Select(line => "  SQL> " + line.TrimEnd())));
    }
});

const string DemoInvoice = "INV-DEMO-064";

await using (SchoolDbContext db = NewContext())
{
    await RemoveDemoInvoiceAsync(db);   // in case an earlier run stopped halfway
    FeeInvoice invoice = new FeeInvoice(DemoInvoice, totalAmount: 10000m)
    {
        EnrollmentId = 16,              // Meera Iyer, 2026-27
        InvoiceDate = new DateOnly(2026, 10, 7),
        DueDate = new DateOnly(2026, 10, 31)
    };
    db.FeeInvoices.Add(invoice);
    await db.SaveChangesAsync();
    Console.WriteLine($"Created {invoice.InvoiceNumber} for 10000, status {invoice.Status}");
}

try
{
    // 1. An explicit transaction around two steps: cancel the invoice, then write an audit row.
    //    The audit INSERT fails (its Action is not allowed), so both steps are rolled back.
    await using (SchoolDbContext db = NewContext())
    {
        FeeInvoice invoice = await db.FeeInvoices.SingleAsync(i => i.InvoiceNumber == DemoInvoice);
        await using IDbContextTransaction transaction = await db.Database.BeginTransactionAsync();
        try
        {
            invoice.Cancel();
            await db.SaveChangesAsync();

            string oldValues = """{"Status":"Unpaid"}""";
            string newValues = """{"Status":"Cancelled"}""";
            await db.Database.ExecuteSqlAsync($"""
                INSERT INTO dbo.AuditLogs (TableName, RecordId, Action, OldValues, NewValues)
                VALUES ('FeeInvoices', {invoice.Id}, 'CANCEL', {oldValues}, {newValues});
                """);
            await transaction.CommitAsync();
        }
        catch (Exception ex) when (ex is not OperationCanceledException)
        {
            await transaction.RollbackAsync();
            Console.WriteLine($"Step 2 failed, both steps rolled back: {ex.Message.Split('.')[0]}.");
        }
        Console.WriteLine($"In memory the invoice still says {invoice.Status}; " +
                          $"the database says {await db.FeeInvoices.AsNoTracking().Where(i => i.Id == invoice.Id).Select(i => i.Status).SingleAsync()}");
    }

    // 2. A unit of work: one SaveChanges writes the payment and the invoice's new status together.
    await using (SchoolDbContext db = NewContext())
    {
        FeePaymentService service = new FeePaymentService(new EfFeeInvoiceRepository(db));
        showSql = true;
        FeePayment payment = await service.PayAsync(DemoInvoice, 4000m, PaymentMode.Cash);
        showSql = false;
        Console.WriteLine($"Paid {payment.Amount}, receipt {payment.ReceiptNumber}; invoice now {payment.FeeInvoice!.Status}, balance {payment.FeeInvoice.Balance}");
    }

    // 3. Two clerks, one invoice. Clerk B opens the invoice; clerk A then takes the whole balance.
    await using SchoolDbContext clerkB = NewContext();
    EfFeeInvoiceRepository clerkBInvoices = new EfFeeInvoiceRepository(clerkB);
    FeeInvoice seenByB = (await clerkBInvoices.GetWithPaymentsAsync(DemoInvoice))!;
    Console.WriteLine($"Clerk B opens the invoice: balance {seenByB.Balance}");

    await using (SchoolDbContext clerkA = NewContext())
    {
        FeePayment paidByA = await new FeePaymentService(new EfFeeInvoiceRepository(clerkA))
            .PayAsync(DemoInvoice, 6000m, PaymentMode.Online);
        Console.WriteLine($"Clerk A takes {paidByA.Amount}: invoice {paidByA.FeeInvoice!.Status}");
    }

    // Clerk B's copy still shows 6000 due, so the rules in memory allow the same payment again...
    seenByB.RecordPayment(6000m, PaymentMode.Cash, await clerkBInvoices.NextReceiptNumberAsync());
    try
    {
        await clerkBInvoices.SaveChangesAsync();   // ...but the RowVersion check refuses it
    }
    catch (SchoolRuleException ex)
    {
        Console.WriteLine($"Clerk B refused: error {ex.ErrorNumber}, {ex.Message} ({ex.InnerException!.GetType().Name})");
    }

    // Clerk B loads the invoice again and sees the truth.
    await using (SchoolDbContext fresh = NewContext())
    {
        FeeInvoice reloaded = (await new EfFeeInvoiceRepository(fresh).GetWithPaymentsAsync(DemoInvoice))!;
        Console.WriteLine($"Clerk B reloads: {reloaded.Status}, paid {reloaded.AmountPaid} in {reloaded.Payments.Count} payments, balance {reloaded.Balance}");
    }
}
finally
{
    await using SchoolDbContext db = NewContext();
    await RemoveDemoInvoiceAsync(db);
    Console.WriteLine($"Removed {DemoInvoice} and its payments");
}

// ExecuteDeleteAsync (Lesson 61): payments first, because they point at the invoice.
static async Task RemoveDemoInvoiceAsync(SchoolDbContext db)
{
    await db.FeePayments.IgnoreQueryFilters().Where(p => p.FeeInvoice!.InvoiceNumber == DemoInvoice).ExecuteDeleteAsync();
    await db.FeeInvoices.Where(i => i.InvoiceNumber == DemoInvoice).ExecuteDeleteAsync();
}

Output
Created INV-DEMO-064 for 10000, status Unpaid
Step 2 failed, both steps rolled back: The INSERT statement conflicted with the CHECK constraint "CK_AuditLogs_Action".
In memory the invoice still says Cancelled; the database says Unpaid
  SQL> SET NOCOUNT ON;
  SQL> UPDATE [FeeInvoices] SET [Status] = @p0
  SQL> OUTPUT INSERTED.[RowVersion]
  SQL> WHERE [Id] = @p1 AND [RowVersion] = @p2;
  SQL> INSERT INTO [FeePayments] ([Amount], [CreatedBy], [DeletedAt], [FeeInvoiceId], [IsDeleted], [PaidOn], [PaymentMode], [ReceiptNumber], [TransactionReference], [UpdatedAt], [UpdatedBy])
  SQL> OUTPUT INSERTED.[Id], INSERTED.[CreatedAt]
  SQL> VALUES (@p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13);
Paid 4000, receipt RCP-2026-000013; invoice now PartiallyPaid, balance 6000.00
Clerk B opens the invoice: balance 6000.00
Clerk A takes 6000: invoice Paid
Clerk B refused: error 50014, The invoice was changed by someone else. Load it again and retry. (DbUpdateConcurrencyException)
Clerk B reloads: Paid, paid 10000.00 in 2 payments, balance 0.00
Removed INV-DEMO-064 and its payments

Your receipt numbers will be higher: the sequence never gives numbers back (Lesson 57).

Example: an explicit transaction

The first experiment cancels the invoice and then writes an audit row: two steps that must succeed together. One SaveChangesAsync cannot cover both, because the audit row is written with raw SQL, so the code opens a transaction with BeginTransactionAsync.

The audit INSERT used the action 'CANCEL', which the CHECK constraint on AuditLogs does not allow. The catch called RollbackAsync, and the cancel, already sent by SaveChangesAsync, was undone with it: the database still says Unpaid.

Example: rollback does not touch memory

The next line shows a trap: the invoice object still says Cancelled. A rollback undoes the database, not your C# objects, and EF Core's change tracker still believes its saved values.

After a rollback, throw the DbContext away and load the data again. That is one more reason to keep each DbContext short-lived (Lesson 59).

Example: one SaveChanges, one transaction

The SQL from the payment shows the whole unit of work in one batch: the invoice's status UPDATE and the payment INSERT, sent together in one transaction. If the INSERT failed, the UPDATE would be rolled back too.

Look at the end of the UPDATE: WHERE [Id] = @p1 AND [RowVersion] = @p2. That is the optimistic check. OUTPUT INSERTED.[RowVersion] reads the new version back, so the next save from this DbContext checks against the right value.

Example: two clerks, one invoice

Clerk B opened the invoice while 6,000 was due. Clerk A then took the 6,000, so the invoice became Paid and its version changed. Clerk B's copy still showed 6,000 due, so RecordPayment allowed the same payment again in memory.

At SaveChangesAsync, the UPDATE found no row with Clerk B's old version. EF Core threw DbUpdateConcurrencyException, and nothing of Clerk B's payment was saved. After reloading, Clerk B saw the truth: Paid, 10,000 in 2 payments, balance 0.

Example: the same run without the token

To see what the token prevents, the IsRowVersion() line was commented out for one run. Every other line was the same:

Output without the RowVersion token
Clerk B opens the invoice: balance 6000.00
Clerk A takes 6000: invoice Paid
Clerk B reloads: Paid, paid 16000.00 in 3 payments, balance -6000.00

No error, no warning: the 10,000 invoice took 16,000, and the balance went negative. That is the lost update from the concept section, happening for real. One configuration line stops it.

Example: choosing a data-access tool

Module 9 used three tools for the same school. Each has a place:

Situation	Good choice	Why
Everyday create, read, update, delete with rules	EF Core	Change tracking, units of work, concurrency tokens, LINQ
Heavy reports and dashboards	Dapper	Hand-tuned SQL straight into DTOs, as fast as ADO.NET
Bulk loading, streaming huge results, special SQL Server features	ADO.NET	Full control: `SqlBulkCopy`, table-valued parameters
Simple bulk changes with no entity rules	EF Core `ExecuteUpdateAsync`	One statement, no loading
Rules that must hold even outside the app	Stored procedures and constraints	The database protects itself (Lessons 10 and 22)

All three can live in one project behind repository interfaces (Lesson 62), sharing one connection string and, when needed, one transaction.

Common mistakes

Several SaveChangesAsync calls for one business operation, with no transaction. A failure in the middle leaves half the work saved. Fix: one save, or an explicit transaction.
Reusing objects after a rollback. They still hold the rolled-back values. Fix: discard the DbContext and reload.
No concurrency check on screens that edit shared data. The last save silently wins. Fix: a ROWVERSION token.
Letting DbUpdateConcurrencyException reach the user. It means nothing to a clerk. Fix: translate it into a clear error that says to reload.
Holding a transaction open while waiting for a person. Locks block every other clerk. Fix: keep transactions short; use optimistic checks across user think-time.

In real projects

From 16 years of .NET projects

At admission time, a school's office had three counters taking fees. Twice in one week, the accounts team found invoices with more paid than billed, and refunds had to be arranged by hand.

The cause was a lost update: two counters serving siblings opened the same family invoice. Nobody could reproduce it in testing, because testing was always one person at a time.

A rowversion column and a "this invoice changed, please reload" message fixed it in a day. Since then, every editable money table I design gets a version column on the first day, not after the first refund.

Best practices

Make each business operation one unit of work, ideally one SaveChangesAsync.
Use explicit transactions only when one save cannot cover the work, and keep them short.
Add a ROWVERSION concurrency token to tables that several users edit.
Translate concurrency conflicts into clear errors, and reload after any rollback.
Keep database-level rules too: constraints and procedures protect data from every client.

Recap

Each SaveChangesAsync is a transaction; BeginTransactionAsync covers several steps.
A rollback undoes the database, not the objects in memory.
The lost update: two users save changes based on the same old data.
Pessimistic locking blocks others; optimistic checks a version when saving.
A ROWVERSION token made EF Core refuse Clerk B's payment; without it, 10,000 became 16,000.
EF Core for everyday work, Dapper for heavy reads, ADO.NET for special cases.

Homework

Library module task: Add a ROWVERSION column to BookCopies with a script. Then simulate two librarians who open Malgudi Days' copy at the same time: librarian A marks it Lost and saves, then librarian B tries to mark it Issued. Show that B is refused, and set the copy back to Available.

A correct result looks like (your version numbers will differ):

Output
Both see copy 4 as Available, version 000000000002D694
Librarian A saved: Lost, new version 000000000002F5D1
Librarian B refused: the copy changed after it was opened
In the database, copy 4 is Lost
Copy 4 set back to Available

Show the solution

library-assignment/064-library-rowversion.sql
-- Library homework, Lesson 64: a version number on every book copy, so two librarians cannot
-- overwrite each other's change to the same copy. Safe to run more than once.
USE SchoolDb;
GO

IF COL_LENGTH(N'dbo.BookCopies', N'RowVersion') IS NULL
    ALTER TABLE dbo.BookCopies ADD RowVersion ROWVERSION NOT NULL;
GO

SELECT Id, BookId, CopyNumber, Status, RowVersion
FROM dbo.BookCopies
ORDER BY Id;
GO

library-assignment/064-library-concurrency.cs
// Library homework, Lesson 64: two librarians change the same book copy. The RowVersion column
// (064-library-rowversion.sql) lets the first change win and refuses the second.
// Run with:  dotnet run library-assignment/064-library-concurrency.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:property PublishAot=false
using Microsoft.EntityFrameworkCore;
using SchoolApp.Data;

DbContextOptions<LibraryDbContext> options = new DbContextOptionsBuilder<LibraryDbContext>()
    .UseSqlServer(SqlConnectionFactory.FromEnvironment().ConnectionString)
    .Options;

// Both librarians open Malgudi Days, copy 1 (Id 4), at the same moment.
await using LibraryDbContext librarianA = new LibraryDbContext(options);
await using LibraryDbContext librarianB = new LibraryDbContext(options);
BookCopy copyForA = await librarianA.BookCopies.SingleAsync(c => c.Id == 4);
BookCopy copyForB = await librarianB.BookCopies.SingleAsync(c => c.Id == 4);
Console.WriteLine($"Both see copy 4 as {copyForA.Status}, version {Convert.ToHexString(copyForA.RowVersion)}");

try
{
    // Librarian A reports the copy lost, and saves first.
    copyForA.Status = "Lost";
    await librarianA.SaveChangesAsync();
    Console.WriteLine($"Librarian A saved: Lost, new version {Convert.ToHexString(copyForA.RowVersion)}");

    // Librarian B, still holding the old version, tries to mark it Issued.
    copyForB.Status = "Issued";
    try
    {
        await librarianB.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException)
    {
        Console.WriteLine("Librarian B refused: the copy changed after it was opened");
    }

    BookCopy current = await librarianB.BookCopies.AsNoTracking().SingleAsync(c => c.Id == 4);
    Console.WriteLine($"In the database, copy 4 is {current.Status}");
}
finally
{
    // Put the copy back as it was, so the homework can run again.
    await librarianA.BookCopies.Where(c => c.Id == 4).ExecuteUpdateAsync(s => s.SetProperty(c => c.Status, "Available"));
    Console.WriteLine("Copy 4 set back to Available");
}

class BookCopy
{
    public int Id { get; set; }
    public string Status { get; set; } = "";
    public byte[] RowVersion { get; set; } = [];   // a normal property here, so it can be printed
}

class LibraryDbContext : DbContext
{
    public LibraryDbContext(DbContextOptions<LibraryDbContext> options) : base(options)
    {
    }

    public DbSet<BookCopy> BookCopies => Set<BookCopy>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<BookCopy>(copy =>
        {
            copy.ToTable("BookCopies");
            copy.Property(c => c.Status).HasMaxLength(9).IsUnicode(false);
            copy.Property(c => c.RowVersion).IsRowVersion();
        });
    }
}

Here RowVersion is a normal property, so the program can print it. In the SMS it is a shadow property instead, to keep Core free of database details. Both work the same way.

Quiz

What does one SaveChangesAsync call do with several changes?
1. Saves each in its own transaction
2. Saves them all in one transaction
3. Saves only the first
4. Needs TransactionScope to save them
Show answer
Answer: B. Saves them all in one transaction The payment INSERT and the invoice UPDATE went in one batch, one transaction.
After RollbackAsync, what does the tracked invoice object show?
1. The database values
2. The values from before the rollback
3. null
4. An exception
Show answer
Answer: B. The values from before the rollback In memory it still said Cancelled; the database said Unpaid.
What is a lost update?
1. A failed INSERT
2. A save based on data that someone else changed meanwhile
3. A deleted row
4. A missing index
Show answer
Answer: B. A save based on data that someone else changed meanwhile Two clerks took 6,000 each from a 6,000 balance.
How does a ROWVERSION column detect a conflict?
1. It locks the row
2. The UPDATE requires the version that was read, and SQL Server changes it on every update
3. It counts users
4. It stores the user name
Show answer
Answer: B. The UPDATE requires the version that was read, and SQL Server changes it on every update WHERE Id = @id AND RowVersion = @versionRead matched zero rows for Clerk B.
Which tool fits a heavy dashboard query best?
1. EF Core change tracking
2. Dapper with hand-tuned SQL
3. TransactionScope
4. ExecuteDeleteAsync
Show answer
Answer: B. Dapper with hand-tuned SQL Dapper maps exact SQL into DTOs, as fast as ADO.NET.

Interview questions

Questions are grouped by experience level. Click a question to see a model answer. All questions: Data Access interview questions.

Fresher (0-2 yrs)

F How does EF Core use transactions when you call SaveChangesAsync? concept

Each SaveChangesAsync call runs all its INSERT, UPDATE, and DELETE statements in one transaction: either all are saved or none. Taking a payment saved the payment and the invoice's new status together. For several saves or mixed raw SQL, use Database.BeginTransactionAsync().

See the example · Permalink

F What is the lost update problem? concept

Two users read the same data, both change it based on what they read, and the second save overwrites or contradicts the first. Two clerks each took 6,000 from an invoice with 6,000 due, because both screens showed 6,000; without a check, the invoice ended paid 16,000 of 10,000.

See the example · Permalink

F What is a ROWVERSION column in SQL Server? concept

A binary value that SQL Server changes automatically on every update of the row; you never set it. Comparing the version you read with the current one tells you whether someone changed the row in between, which is the basis of optimistic concurrency.

See the example · Permalink

Mid (3-5 yrs)

M What is the difference between pessimistic and optimistic concurrency? concept

Pessimistic concurrency locks the data while working, so nobody else can change it, as usp_RecordFeePayment does with UPDLOCK. Optimistic concurrency takes no lock and checks at save time that the data has not changed, such as with a rowversion. Optimistic suits screens where users think between reading and saving; pessimistic suits short operations with frequent conflicts.

See the example · Permalink

M How do you add optimistic concurrency to an entity in EF Core? coding

Map a ROWVERSION column as a concurrency token with IsRowVersion(), either on a byte[] property or as a shadow property, builder.Property<byte[]>("RowVersion").IsRowVersion(). EF Core then adds AND RowVersion = @original to every UPDATE and DELETE, and throws DbUpdateConcurrencyException when no row matches.

See the example · Permalink

M What happens to tracked entities when you roll back an EF Core transaction? concept

Nothing: the rollback undoes the database, but the objects keep their changed values, and the change tracker treats them as saved. The cancelled invoice still said Cancelled in memory while the database said Unpaid. Discard the DbContext and reload after a rollback.

See the example · Permalink

Senior (6-9 yrs)

S A clerk gets a concurrency error when taking a fee payment. What should the application do? scenario

Not show the raw exception. Translate DbUpdateConcurrencyException into a clear error, such as 50014 "the invoice was changed by someone else", reload the invoice so the clerk sees the current balance, and let them decide again. Never retry a money operation automatically with stale values.

How to answer: Cover translation, reloading, and user decision, and explain why automatic retry or "client wins" is wrong for payments, while it may be fine for low-risk fields.

See the example · Permalink

S When do you need an explicit transaction in EF Core instead of relying on SaveChanges? concept

When one business operation needs several SaveChangesAsync calls, mixes EF Core with raw SQL or another tool, or must read and write under one isolation level. Keep it short, commit at the end, and roll back on any error. If connection retries are enabled, run the transaction inside the execution strategy, because retries cannot replay a user-started transaction.

How to answer: Give concrete triggers, show the begin, commit, rollback shape, and mention the execution strategy caveat and why long transactions hurt concurrency.

See the example · Permalink

Architect / Lead (10+ yrs)

A How would you choose between ADO.NET, Dapper, and EF Core for a new system like the SMS? design

Default to EF Core for everyday work with rules, because units of work, concurrency tokens, and LINQ remove much code and many bugs. Use Dapper for heavy reports where hand-tuned SQL matters, and ADO.NET for special cases such as bulk copy or streaming. Keep all of them behind repository interfaces, and keep critical rules in the database too.

How to answer: Decide by workload, not by preference; mention mixing tools in one solution, shared transactions, team skills, and measurement before switching tools.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-64-transactions-concurrency (code repository coming soon)
Files: database/064-rowversion.sql, src/SchoolApp.Core/Services/FeePaymentService.cs, src/SchoolApp.Data/EfCore/EfFeeInvoiceRepository.cs, src/SchoolApp.Console/Program.cs, library-assignment/064-library-concurrency.cs
Video: coming soon

What's next

Module 9 is complete: SchoolApp has rules in Core, three data-access tools in Data, tests for both, and safe payments.

Next, Module 10 puts all of it behind a Web API, starting with the language every API speaks. Lesson 65: HTTP and REST basics

Want live sessions, code reviews, and interview preparation? Join the next batch at Sahasra Technologies.

Introduction​

Why this matters​

The concept​

Example: transactions in C#​

Example: the lost update​

Example: pessimistic and optimistic concurrency​

Example: what a rowversion is​

Apply it to the SMS​

Step 1: the concurrency token​

Step 2: a payment service with a unit of work​

Step 3: the EF Core repository​

Step 4: the program​

Example: an explicit transaction​

Example: rollback does not touch memory​

Example: one SaveChanges, one transaction​

Example: two clerks, one invoice​

Example: the same run without the token​

Example: choosing a data-access tool​

Common mistakes​

In real projects​

Best practices​

Recap​

Homework​

Quiz​

Interview questions​

Fresher (0-2 yrs)​

Mid (3-5 yrs)​

Senior (6-9 yrs)​

Architect / Lead (10+ yrs)​

Resources​

What's next​

Introduction

Why this matters

The concept

Example: transactions in C#

Example: the lost update

Example: pessimistic and optimistic concurrency

Example: what a rowversion is

Apply it to the SMS

Step 1: the concurrency token

Step 2: a payment service with a unit of work

Step 3: the EF Core repository

Step 4: the program

Example: an explicit transaction

Example: rollback does not touch memory

Example: one SaveChanges, one transaction

Example: two clerks, one invoice

Example: the same run without the token

Example: choosing a data-access tool

Common mistakes

In real projects

Best practices

Recap

Homework

Quiz

Interview questions

Fresher (0-2 yrs)

Mid (3-5 yrs)

Senior (6-9 yrs)

Architect / Lead (10+ yrs)

Resources

What's next