Enrollments API: Roll Numbers, Capacity, and Locks – Web API | Lesson 78

Published / updated8 Oct 2026

Introduction

An enrollment puts a student into a section for one academic year, with a roll number. It is a small record with four rules:

The student must be active.
The student must not already be enrolled this year.
The section must have a free place.
The roll number must be the next one.

Each rule is easy alone. The difficult part is that two clerks may enroll students into the same section at the same moment, and both must still get correct roll numbers. This lesson builds the endpoint and makes it safe under that pressure.

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

Put a multi-step business operation in a service with numbered errors.
Generate the next number in a group, such as a roll number, without collisions.
Use a pessimistic lock so concurrent requests for the same section run one after another.
Prove the lock works with a deterministic integration test.
Explain why removed students change the count but not the roll numbers.

Why this matters

Admission week means many enrollments at once, often into the same popular sections. A race condition that appears once in a thousand requests appears every day of that week.

The patterns in this lesson, "check, then act" under a lock, and "next number in a group", appear everywhere: invoice numbers, seat booking, stock, and appointment slots.

The concept

Example: the check-then-act race

The service checks the section, then acts on what it saw:

Time	Clerk A	Clerk B
1	Reads 5-A: highest roll number 0
2		Reads 5-A: highest roll number 0
3	Saves roll number 1
4		Saves roll number 1: clash

Between checking and acting, the world changed. The same race can push a full section over its capacity: both clerks see 49 of 50 students and both add one.

Example: locking the section

Think of one register book on the class teacher's desk. Only the clerk holding the book can write a new roll number; the other clerk waits, then reads the book as it is after the first one finishes.

In SQL Server, SELECT ... WITH (UPDLOCK, ROWLOCK) inside a transaction takes an update lock on one row (Lesson 25). Another transaction asking for the same lock waits until the first commits or rolls back. Locking the section's row makes enrollments into that section run one at a time, while other sections are not affected.

Approach	How	Fits enrollments?
Optimistic (Lesson 64)	Save, and fail if someone changed the data	Clerks would retry often in admission week
Pessimistic, this lesson	Lock the section, then check and save	Yes: short work, conflicts likely
Only a unique constraint	Let the database reject duplicates	A useful last guard, but capacity has no constraint

Example: error numbers for enrollments

Number	Meaning	HTTP
50030	No such student	404
50031	No such section in that class	404
50032	Student is not active	422
50033	Already enrolled this academic year	409
50034	Section is full	409
50035	No current academic year	422

They are added to sms-reference.md, and SchoolRuleExceptionHandler maps them, as Lesson 74 set up.

Apply it to the SMS

Step 1: the request and the interface

src/SchoolApp.Core/Dtos/EnrollmentDtos.cs
using System.ComponentModel.DataAnnotations;

namespace SchoolApp.Core.Dtos;

// Lesson 78: enrolling a student in a section for the current academic year.

public record EnrollStudentRequest(
    [Required] string AdmissionNumber,
    [Range(1, 10)] int ClassId,
    [Required, StringLength(2)] string SectionName);

public record EnrollmentDto(
    int Id,
    string AdmissionNumber,
    string StudentName,
    string AcademicYear,
    string ClassName,
    string SectionName,
    short RollNumber,
    DateOnly EnrolledOn);

// A section as the enrollment rules need it.
public record SectionInfo(int Id, string ClassName, string Name, byte Capacity);

// How full a section is this year, read while the section is locked.
public record SectionFill(int Count, short MaxRollNumber);

src/SchoolApp.Core/Interfaces/IEnrollmentRepository.cs
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;

namespace SchoolApp.Core.Interfaces;

// Lesson 78: what enrolling needs from storage, including a lock on one section.
public interface IEnrollmentRepository
{
    Task<SectionInfo?> GetSectionAsync(int classId, string sectionName, CancellationToken cancellationToken = default);

    // Starts a transaction and locks the section, so only one enrollment into it runs at a time.
    // Everything read and saved until CommitAsync is part of that transaction.
    Task<ISectionLock> LockSectionAsync(int sectionId, CancellationToken cancellationToken = default);

    Task<bool> IsEnrolledAsync(int studentId, int academicYearId, CancellationToken cancellationToken = default);

    Task<SectionFill> GetSectionFillAsync(int sectionId, int academicYearId, CancellationToken cancellationToken = default);

    // Throws SchoolRuleException 50033 if the database finds the student already enrolled this year.
    Task AddAsync(Enrollment enrollment, CancellationToken cancellationToken = default);

    Task<EnrollmentDto?> GetAsync(int id, CancellationToken cancellationToken = default);
}

// A held lock. Disposing it without CommitAsync rolls everything back.
public interface ISectionLock : IAsyncDisposable
{
    Task CommitAsync(CancellationToken cancellationToken = default);
}

ISectionLock represents a held lock. Core does not know it is a SQL Server row lock: only that it must commit when the work succeeds, and that disposing it without committing undoes everything.

Step 2: the service

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

namespace SchoolApp.Core.Services;

// Lesson 78: enrolling a student in a section for the current academic year.
// Rules: the student and section exist, the student is active and not yet enrolled this year,
// the section has a free place, and the student gets the next roll number in the section.
// The section is locked while the rules are checked and the enrollment is saved, so two clerks
// enrolling into the same section at the same moment are handled one after the other.
public class EnrollmentService
{
    private readonly IStudentRepository _students;
    private readonly IMasterDataRepository _masterData;
    private readonly IEnrollmentRepository _enrollments;

    public EnrollmentService(IStudentRepository students, IMasterDataRepository masterData, IEnrollmentRepository enrollments)
    {
        _students = students;
        _masterData = masterData;
        _enrollments = enrollments;
    }

    public async Task<EnrollmentDto> EnrollAsync(EnrollStudentRequest request, CancellationToken cancellationToken = default)
    {
        Student student = await _students.GetByAdmissionNumberAsync(request.AdmissionNumber, cancellationToken)
            ?? throw new SchoolRuleException(50030, $"No student has admission number {request.AdmissionNumber}.");
        if (student.Status != StudentStatus.Active)
        {
            throw new SchoolRuleException(50032, $"Only active students can be enrolled; this student is {student.Status}.");
        }

        AcademicYearDto year = await _masterData.GetCurrentAcademicYearAsync(cancellationToken)
            ?? throw new SchoolRuleException(50035, "No academic year is marked as current.");
        SectionInfo section = await _enrollments.GetSectionAsync(request.ClassId, request.SectionName, cancellationToken)
            ?? throw new SchoolRuleException(50031, $"Class {request.ClassId} has no section {request.SectionName}.");

        // From here until CommitAsync, no other enrollment into this section can run.
        await using ISectionLock sectionLock = await _enrollments.LockSectionAsync(section.Id, cancellationToken);

        if (await _enrollments.IsEnrolledAsync(student.Id, year.Id, cancellationToken))
        {
            throw new SchoolRuleException(50033, $"{student.FullName} is already enrolled in {year.Name}.");
        }

        SectionFill fill = await _enrollments.GetSectionFillAsync(section.Id, year.Id, cancellationToken);
        if (fill.Count >= section.Capacity)
        {
            throw new SchoolRuleException(50034, $"{section.ClassName}-{section.Name} is full ({section.Capacity} students).");
        }

        Enrollment enrollment = new Enrollment
        {
            StudentId = student.Id,
            AcademicYearId = year.Id,
            SectionId = section.Id,
            RollNumber = (short)(fill.MaxRollNumber + 1),
            EnrolledOn = DateOnly.FromDateTime(DateTime.UtcNow)
        };
        await _enrollments.AddAsync(enrollment, cancellationToken);
        await sectionLock.CommitAsync(cancellationToken);

        return new EnrollmentDto(enrollment.Id, student.AdmissionNumber, student.FullName, year.Name,
                                 section.ClassName, section.Name, enrollment.RollNumber, enrollment.EnrolledOn);
    }
}

Checks that do not depend on the section's current state run before the lock: the student exists and is active, the year and the section exist. The checks that do, already enrolled, capacity, and the roll number, run after it, while no one else can change the section.

await using disposes the lock however the method ends. If a rule throws after the lock is taken, the transaction rolls back and the lock is released.

Step 3: the lock in EF Core

src/SchoolApp.Data/EfCore/EfEnrollmentRepository.cs
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Entities;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;
using SchoolApp.Data.EfCore.Configurations;

namespace SchoolApp.Data.EfCore;

// Lesson 78: enrollments with EF Core, and a pessimistic lock on the section (Lesson 25).
public class EfEnrollmentRepository : IEnrollmentRepository
{
    private readonly SchoolDbContext _db;

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

    public Task<SectionInfo?> GetSectionAsync(int classId, string sectionName, CancellationToken cancellationToken = default)
    {
        return _db.Sections
            .Where(s => s.ClassId == classId && s.Name == sectionName)
            .Select(s => new SectionInfo(s.Id, s.SchoolClass!.Name, s.Name, s.Capacity))
            .SingleOrDefaultAsync(cancellationToken);
    }

    public async Task<ISectionLock> LockSectionAsync(int sectionId, CancellationToken cancellationToken = default)
    {
        IDbContextTransaction transaction = await _db.Database.BeginTransactionAsync(cancellationToken);

        // UPDLOCK keeps an update lock on this one Sections row until the transaction ends. A second
        // enrollment into the same section waits here; enrollments into other sections do not.
        await _db.Database.ExecuteSqlAsync(
            $"SELECT Id FROM dbo.Sections WITH (UPDLOCK, ROWLOCK) WHERE Id = {sectionId};", cancellationToken);

        return new EfSectionLock(transaction);
    }

    public Task<bool> IsEnrolledAsync(int studentId, int academicYearId, CancellationToken cancellationToken = default)
    {
        return _db.Enrollments.AnyAsync(e => e.StudentId == studentId && e.AcademicYearId == academicYearId, cancellationToken);
    }

    public async Task<SectionFill> GetSectionFillAsync(int sectionId, int academicYearId, CancellationToken cancellationToken = default)
    {
        // The count is of current students, so removed students free their place (the query filter).
        int count = await _db.Enrollments
            .CountAsync(e => e.SectionId == sectionId && e.AcademicYearId == academicYearId, cancellationToken);

        // Roll numbers are different: a removed student's enrollment row still holds its roll number in
        // UQ_Enrollments_SectionId_AcademicYearId_RollNumber, so the highest number must include it.
        short maxRoll = await _db.Enrollments
            .IgnoreQueryFilters([SoftDeleteFilter.Name])
            .Where(e => e.SectionId == sectionId && e.AcademicYearId == academicYearId)
            .MaxAsync(e => (short?)e.RollNumber, cancellationToken) ?? 0;

        return new SectionFill(count, maxRoll);
    }

    public async Task AddAsync(Enrollment enrollment, CancellationToken cancellationToken = default)
    {
        _db.Enrollments.Add(enrollment);
        try
        {
            await _db.SaveChangesAsync(cancellationToken);
        }
        catch (DbUpdateException ex) when (SqlErrors.IsUniqueKeyViolation(ex, "UQ_Enrollments_StudentId_AcademicYearId"))
        {
            // The final guard: the same student enrolled at the same moment into a different section.
            _db.Entry(enrollment).State = EntityState.Detached;
            throw new SchoolRuleException(50033, "The student is already enrolled in this academic year.", ex);
        }
    }

    public Task<EnrollmentDto?> GetAsync(int id, CancellationToken cancellationToken = default)
    {
        return _db.Enrollments
            .Where(e => e.Id == id)
            .Select(e => new EnrollmentDto(e.Id, e.Student!.AdmissionNumber, e.Student.FirstName + " " + e.Student.LastName,
                                           e.AcademicYear!.Name, e.Section!.SchoolClass!.Name, e.Section.Name,
                                           e.RollNumber, e.EnrolledOn))
            .SingleOrDefaultAsync(cancellationToken);
    }

    private sealed class EfSectionLock : ISectionLock
    {
        private readonly IDbContextTransaction _transaction;

        public EfSectionLock(IDbContextTransaction transaction)
        {
            _transaction = transaction;
        }

        public Task CommitAsync(CancellationToken cancellationToken = default) => _transaction.CommitAsync(cancellationToken);

        // Disposing a transaction that was not committed rolls it back and releases the lock.
        public ValueTask DisposeAsync() => _transaction.DisposeAsync();
    }
}

LockSectionAsync starts a transaction and runs the UPDLOCK query. Everything EF Core reads and saves through the same DbContext until CommitAsync is part of that transaction.

AddAsync still translates one unique-key error: a student enrolled at the same moment into a different section. The section lock cannot see that, but UQ_Enrollments_StudentId_AcademicYearId can, and the answer is the same 50033.

Example: counts and roll numbers differ

Since Lesson 76, enrollments of removed students are hidden by the query filter. For capacity that is right: a removed student frees their place.

For roll numbers it is not. A removed student's enrollment row still holds its roll number in the unique constraint, so GetSectionFillAsync finds the highest number with IgnoreQueryFilters. Otherwise, a new student could be given a roll number that is still taken, and the insert would fail.

Step 4: the controller

src/SchoolApp.Api/Controllers/EnrollmentsController.cs
using Microsoft.AspNetCore.Mvc;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;

namespace SchoolApp.Api.Controllers;

// Lesson 78: enrolling students for the current academic year.
[ApiController]
[Route("api/enrollments")]
[Produces("application/json")]
public class EnrollmentsController : ControllerBase
{
    private readonly EnrollmentService _service;
    private readonly IEnrollmentRepository _enrollments;

    public EnrollmentsController(EnrollmentService service, IEnrollmentRepository enrollments)
    {
        _service = service;
        _enrollments = enrollments;
    }

    /// <summary>Gets one enrollment.</summary>
    /// <response code="200">The enrollment.</response>
    /// <response code="404">No enrollment has this id.</response>
    [HttpGet("{id:int}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public async Task<ActionResult<EnrollmentDto>> Get(int id, CancellationToken cancellationToken)
    {
        EnrollmentDto? enrollment = await _enrollments.GetAsync(id, cancellationToken);
        return enrollment is null ? NotFound() : enrollment;
    }

    /// <summary>Enrolls a student in a section for the current academic year, with the next roll number.</summary>
    /// <response code="201">The student was enrolled.</response>
    /// <response code="400">The request is invalid.</response>
    /// <response code="404">No such student (50030) or section (50031).</response>
    /// <response code="409">Already enrolled this year (50033), or the section is full (50034).</response>
    /// <response code="422">The student is not active (50032), or there is no current year (50035).</response>
    [HttpPost]
    [ProducesResponseType(StatusCodes.Status201Created)]
    [ProducesResponseType(StatusCodes.Status400BadRequest)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    [ProducesResponseType(StatusCodes.Status409Conflict)]
    [ProducesResponseType(StatusCodes.Status422UnprocessableEntity)]
    public async Task<ActionResult<EnrollmentDto>> Enroll(EnrollStudentRequest request, CancellationToken cancellationToken)
    {
        EnrollmentDto enrollment = await _service.EnrollAsync(request, cancellationToken);
        return CreatedAtAction(nameof(Get), new { id = enrollment.Id }, enrollment);
    }
}

Step 5: every outcome

Ishaan is admitted (Lesson 73) and enrolled. Class 5's section C already has 50 of 50 students; A is empty this year:

Output
POST /api/enrollments   {"admissionNumber":"ADM-2026-10001","classId":5,"sectionName":"C"}
HTTP/1.1 409 Conflict
{"...":"...","detail":"Class 5-C is full (50 students).","errorNumber":50034}

POST /api/enrollments   {"admissionNumber":"ADM-2026-10001","classId":5,"sectionName":"A"}
HTTP/1.1 201 Created
Location: http://localhost:5080/api/enrollments/10029
{"id":10029,"admissionNumber":"ADM-2026-10001","studentName":"Ishaan Kumar","academicYear":"2026-27","className":"Class 5","sectionName":"A","rollNumber":1,"enrolledOn":"2026-10-08"}

POST /api/enrollments   (the same request again)
HTTP/1.1 409 Conflict
{"...":"...","detail":"Ishaan Kumar is already enrolled in 2026-27.","errorNumber":50033}

And the remaining kinds of failure:

Request	Status	Detail
Section `Z` of class 5	404, 50031	Class 5 has no section Z.
Student `ADM-1999-0001`	404, 50030	No student has admission number ADM-1999-0001.
Kabir, already in 10-A	409, 50033	Kabir Singh is already enrolled in 2026-27.
Ishaan after being marked Transferred	422, 50032	Only active students can be enrolled; this student is Transferred.
`classId` 12, section `ABC`	400	ClassId must be between 1 and 10; SectionName at most 2 characters.

The SQL log shows the lock being taken for each enrollment: SELECT Id FROM dbo.Sections WITH (UPDLOCK, ROWLOCK) WHERE Id = @p0;. Ishaan and his enrollment were removed from SchoolDb after the demo.

Step 6: proving the lock

A race is hard to show by sending two requests by hand: they rarely arrive close enough together. A test can make the timing certain instead:

Show tests/SchoolApp.Tests/Integration/EnrollmentTests.cs

tests/SchoolApp.Tests/Integration/EnrollmentTests.cs
using System.Diagnostics;
using Dapper;
using Microsoft.Data.SqlClient;
using SchoolApp.Core.Dtos;
using SchoolApp.Core.Exceptions;
using SchoolApp.Core.Interfaces;
using SchoolApp.Core.Services;
using SchoolApp.Data;
using SchoolApp.Data.EfCore;

namespace SchoolApp.Tests.Integration;

// Lesson 78: enrollment rules and the section lock, against the real SchoolDb.
// EnrollmentService runs its own transaction, so these tests cannot use a rollback scope (Lesson 63).
// Instead, each test creates its own students (ADM-2099-...) and deletes them, and their
// enrollments, at the end.
[Trait("Category", "Integration")]
public class EnrollmentTests
{
    private static readonly SqlConnectionFactory Connections = SqlConnectionFactory.FromEnvironment();
    private const int Class5 = 5;   // 5-A (Id 9) is empty this year; 5-C (Id 101) is full, 50 of 50

    private static EnrollmentService NewService(SchoolDbContext db) =>
        new EnrollmentService(new EfStudentRepository(db), new EfMasterDataRepository(db), new EfEnrollmentRepository(db));

    private static async Task CreateStudentsAsync(params string[] admissionNumbers)
    {
        await using SqlConnection connection = Connections.Create();
        foreach (string admissionNumber in admissionNumbers)
        {
            await connection.ExecuteAsync("""
                INSERT INTO dbo.Students (AdmissionNumber, FirstName, LastName, DateOfBirth, Gender, AdmissionDate)
                VALUES (@AdmissionNumber, 'Test', 'Student', '2016-04-12', 'F', '2026-10-07');
                """, new { AdmissionNumber = admissionNumber });
        }
    }

    private static async Task DeleteStudentsAsync()
    {
        await using SqlConnection connection = Connections.Create();
        await connection.ExecuteAsync("""
            DELETE e FROM dbo.Enrollments AS e JOIN dbo.Students AS s ON s.Id = e.StudentId
            WHERE s.AdmissionNumber LIKE 'ADM-2099-%';
            DELETE FROM dbo.Students WHERE AdmissionNumber LIKE 'ADM-2099-%';
            """);
    }

    [Fact]
    public async Task EnrollAsync_TwoNewStudents_GetRollNumbersOneAndTwo()
    {
        try
        {
            await CreateStudentsAsync("ADM-2099-0001", "ADM-2099-0002");
            await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
            EnrollmentService service = NewService(db);

            EnrollmentDto first = await service.EnrollAsync(new EnrollStudentRequest("ADM-2099-0001", Class5, "A"));
            EnrollmentDto second = await service.EnrollAsync(new EnrollStudentRequest("ADM-2099-0002", Class5, "A"));

            Assert.Equal(1, first.RollNumber);
            Assert.Equal(2, second.RollNumber);
            Assert.Equal("2026-27", second.AcademicYear);
        }
        finally
        {
            await DeleteStudentsAsync();
        }
    }

    [Fact]
    public async Task EnrollAsync_SameStudentTwice_Throws50033()
    {
        try
        {
            await CreateStudentsAsync("ADM-2099-0003");
            await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);
            EnrollmentService service = NewService(db);
            await service.EnrollAsync(new EnrollStudentRequest("ADM-2099-0003", Class5, "A"));

            SchoolRuleException error = await Assert.ThrowsAsync<SchoolRuleException>(
                () => service.EnrollAsync(new EnrollStudentRequest("ADM-2099-0003", Class5, "B")));

            Assert.Equal(50033, error.ErrorNumber);
        }
        finally
        {
            await DeleteStudentsAsync();
        }
    }

    [Fact]
    public async Task EnrollAsync_FullSection_Throws50034()
    {
        try
        {
            await CreateStudentsAsync("ADM-2099-0004");
            await using SchoolDbContext db = SchoolDbContextFactory.Create(Connections);

            SchoolRuleException error = await Assert.ThrowsAsync<SchoolRuleException>(
                () => NewService(db).EnrollAsync(new EnrollStudentRequest("ADM-2099-0004", Class5, "C")));

            Assert.Equal(50034, error.ErrorNumber);
        }
        finally
        {
            await DeleteStudentsAsync();
        }
    }

    // Clerk A holds the lock on 5-A. Clerk B's enrollment into 5-A must wait until A finishes.
    [Fact]
    public async Task EnrollAsync_WhileAnotherClerkHoldsTheSection_Waits()
    {
        try
        {
            await CreateStudentsAsync("ADM-2099-0005");
            await using SchoolDbContext dbA = SchoolDbContextFactory.Create(Connections);
            await using SchoolDbContext dbB = SchoolDbContextFactory.Create(Connections);

            ISectionLock lockA = await new EfEnrollmentRepository(dbA).LockSectionAsync(9);
            Stopwatch timer = Stopwatch.StartNew();
            Task<EnrollmentDto> clerkB = NewService(dbB).EnrollAsync(new EnrollStudentRequest("ADM-2099-0005", Class5, "A"));

            await Task.Delay(1000);
            Assert.False(clerkB.IsCompleted);   // still waiting for the section

            await lockA.DisposeAsync();          // clerk A finishes without saving: the lock is released
            EnrollmentDto enrolled = await clerkB;

            Assert.True(timer.ElapsedMilliseconds >= 1000);
            Assert.Equal(1, enrolled.RollNumber);
        }
        finally
        {
            await DeleteStudentsAsync();
        }
    }
}

The last test plays both clerks. Clerk A takes the lock on 5-A and holds it, and Clerk B starts an enrollment into 5-A.

After one second, B has still not finished: it is waiting at the lock. When A releases the lock, B completes with roll number 1.

The other three tests check the roll numbers 1 and 2, the duplicate, and the full section. They cannot use a rollback scope, because the service runs its own transaction, so each creates test students numbered ADM-2099-... and deletes them in finally. The suite has 47 tests, all passing.

Example: how long to hold a lock

While a section is locked, every other enrollment into it waits. That is fine when the locked work is a few quick queries; it would be terrible if the code called a slow service, or waited for a person, inside the lock.

So the service does everything it can before taking the lock, and commits as soon as the enrollment is saved. Short locks are the price that makes pessimistic locking acceptable.

Common mistakes

Check-then-act without a lock or constraint. Two requests both pass the check. Fix: lock, or let a constraint decide, or both.
MAX(...) + 1 outside a transaction. Two requests compute the same next number. Fix: compute it while the group is locked.
Locking the whole table. Every enrollment in the school waits for every other. Fix: lock only the row that represents the group, here one section.
Holding a lock while doing slow work. Others wait for nothing. Fix: check everything possible before locking; commit promptly.
Counting with the query filter when numbering. Removed students' numbers are reused and clash. Fix: ignore the filter for the highest number.

In real projects

From 16 years of .NET projects

During one admission week, a school found three sections with two students sharing a roll number. Teachers' attendance sheets had duplicates, and the marks entry screen refused to save.

The enrollment code read the highest roll number and added one, with no lock, and the database had no unique constraint on roll numbers. It had worked for years at a small school, until a second office counter opened.

We added the unique constraint first, so the data could not get worse, then the section lock. The fix was small; finding which students to renumber took two days.

Best practices

Keep multi-step rules in one service method, with a numbered error for each.
Check what you can before the lock; check state-dependent rules after it.
Lock the smallest thing that represents the group, and hold it briefly.
Keep unique constraints as the last guard, and translate their errors.
Test concurrency deterministically, by holding a lock in the test.

Recap

Enrolling checks the student, the section, duplicates, and capacity, then assigns the next roll number.
Check-then-act races appear when two requests act on the same data at once.
An UPDLOCK on the section row makes enrollments into one section run one at a time.
Removed students free capacity but keep their roll numbers.
A test proved the second clerk waited a full second for the first.

Homework

Library module task: Add POST /api/book-issues to a single-file library API. Lock the book copy's row with UPDLOCK, check it is Available, insert the issue, and commit. Then send two requests for Malgudi Days' only copy at the same moment, and show that one wins and the other gets 409.

A correct result looks like (the due date is 14 days from the day you run it):

Output
HTTP/1.1 201 Created
{"issueId":3,"bookCopyId":4,"studentId":6,"dueOn":"2026-10-22"}
HTTP/1.1 409 Conflict
{"type":"https://tools.ietf.org/html/rfc9110#section-15.5.10","title":"Conflict","status":409,"detail":"Copy 4 is Issued.","traceId":"00-d2ec64c131557b87c27acc3fba8cc7f4-2adc0386fd5af741-00"}

Show the solution

library-assignment/078-library-issue-lock.cs
// Library homework, Lesson 78: issue a book copy safely when two librarians try at the same moment.
// The copy's row is locked with UPDLOCK inside a transaction, so the second request waits, then sees
// the copy is gone. Uses SchoolDb's library tables and the trigger from the Lesson 23 homework.
// Run with:  dotnet run library-assignment/078-library-issue-lock.cs
#:project ../src/SchoolApp.Data/SchoolApp.Data.csproj
#:sdk Microsoft.NET.Sdk.Web
#:property PublishAot=false
using Dapper;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using SchoolApp.Data;

WebApplicationBuilder builder = WebApplication.CreateBuilder(args);
builder.Services.AddControllers();
builder.Services.AddProblemDetails();
builder.Services.AddSingleton(SqlConnectionFactory.FromEnvironment());
WebApplication app = builder.Build();
app.MapControllers();
app.Run("http://localhost:5078");

public record IssueRequest(int BookCopyId, int StudentId);
public record IssueResult(int IssueId, int BookCopyId, int StudentId, DateOnly DueOn);

[ApiController]
[Route("api/book-issues")]
public class BookIssuesController : ControllerBase
{
    private readonly SqlConnectionFactory _connections;

    public BookIssuesController(SqlConnectionFactory connections)
    {
        _connections = connections;
    }

    [HttpPost]
    public async Task<IActionResult> Issue(IssueRequest request)
    {
        await using SqlConnection connection = _connections.Create();
        await connection.OpenAsync();
        await using SqlTransaction transaction = connection.BeginTransaction();

        // Lock this copy's row until COMMIT. A second request for the same copy waits on this line.
        string? status = await connection.QuerySingleOrDefaultAsync<string>(
            "SELECT Status FROM dbo.BookCopies WITH (UPDLOCK, ROWLOCK) WHERE Id = @Id;",
            new { Id = request.BookCopyId }, transaction);

        if (status is null)
        {
            return Problem(statusCode: StatusCodes.Status404NotFound, detail: $"No book copy has id {request.BookCopyId}.");
        }
        if (status != "Available")
        {
            return Problem(statusCode: StatusCodes.Status409Conflict, detail: $"Copy {request.BookCopyId} is {status}.");
        }

        // 14-day loan. The trigger tr_BookIssues_SyncCopyStatus marks the copy Issued. A table with a
        // trigger refuses OUTPUT without INTO (Lesson 60), so SCOPE_IDENTITY() returns the new Id instead.
        DateOnly issuedOn = DateOnly.FromDateTime(DateTime.Today);
        int issueId = await connection.ExecuteScalarAsync<int>("""
            INSERT INTO dbo.BookIssues (BookCopyId, StudentId, IssuedOn, DueOn)
            VALUES (@BookCopyId, @StudentId, @IssuedOn, @DueOn);
            SELECT CAST(SCOPE_IDENTITY() AS INT);
            """, new { request.BookCopyId, request.StudentId, IssuedOn = issuedOn, DueOn = issuedOn.AddDays(14) }, transaction);

        // Hold the lock a little longer, so the second request in the homework surely arrives meanwhile.
        await Task.Delay(500);
        await transaction.CommitAsync();

        return StatusCode(StatusCodes.Status201Created,
            new IssueResult(issueId, request.BookCopyId, request.StudentId, issuedOn.AddDays(14)));
    }
}

library-assignment/078-issue-malgudi.json
{
  "bookCopyId": 4,
  "studentId": 6
}

Send both at once from two terminals, or start the first in the background. The first attempt at this solution used OUTPUT INSERTED.Id and failed with the trigger error from Lesson 60; SCOPE_IDENTITY() returns the new Id without it. Afterwards, delete the test issue and set copy 4 back to Available.

Quiz

What is a check-then-act race?
1. A slow query
2. Two requests both check a condition, then both act, and one acts on stale information
3. A deadlock
4. A failed validation
Show answer
Answer: B. Two requests both check a condition, then both act, and one acts on stale information Both clerks read highest roll number 0 and both chose 1.
What does SELECT ... WITH (UPDLOCK, ROWLOCK) inside a transaction do?
1. Deletes the row
2. Holds an update lock on that row until the transaction ends, so others wanting it wait
3. Reads without locks
4. Locks the whole database
Show answer
Answer: B. Holds an update lock on that row until the transaction ends, so others wanting it wait Enrollments into the same section run one at a time.
Why does GetSectionFillAsync ignore the query filter for the highest roll number?
1. For speed
2. Removed students' rows still hold their roll numbers in the unique constraint
3. The filter breaks MAX
4. Roll numbers are not filtered
Show answer
Answer: B. Removed students' rows still hold their roll numbers in the unique constraint Reusing a held number would make the insert fail.
Which status does enrolling into a full section return?
1. 400
2. 404
3. 409 Conflict, error 50034
4. 500
Show answer
Answer: C. 409 Conflict, error 50034 The request is valid; it clashes with the section's current state.
How did the test prove the lock works?
1. By sending many requests
2. Clerk A held the lock; clerk B was still waiting after one second, then finished when A released it
3. By reading the log
4. By measuring CPU
Show answer
Answer: B. Clerk A held the lock; clerk B was still waiting after one second, then finished when A released it Holding the lock in the test makes the timing certain.

Interview questions

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

Fresher (0-2 yrs)

F What is a race condition in a web API? Give an example. concept

Two requests run at the same time and the result depends on their timing. Two clerks enrolling into one section both read "highest roll number 0" and both save roll number 1; one of them acted on information that was no longer true.

See the example · Permalink

F Why put the enrollment rules in a service rather than in the controller? design

The rules (active student, not already enrolled, capacity, next roll number) are business logic that must hold for every caller, and they need several repository calls in one transaction. A service keeps them in one tested place; the controller only translates HTTP to the service call.

See the example · Permalink

F Which status codes fit 'section is full', 'student not found', and 'student not active'? concept

Section full: 409 Conflict, because the request clashes with the section's current state. Student not found: 404. Student not active: 422, a business rule. Each also carries an SMS error number, such as 50034, 50030, and 50032.

See the example · Permalink

Mid (3-5 yrs)

M How do you make 'check, then insert' safe when two requests act on the same group of rows? coding

Serialize them on something that represents the group: start a transaction and lock its row, such as SELECT ... FROM Sections WITH (UPDLOCK, ROWLOCK), then check and insert, then commit. Requests for the same section wait; other sections are unaffected. Keep a unique constraint as a final guard.

See the example · Permalink

M How do you generate the next number within a group, such as a roll number per section? coding

While the group is locked, read the highest existing number and add one, then insert, all in one transaction; back it with a unique constraint on (group, number). Without the lock, two requests compute the same number. A sequence or identity does not fit, because numbering restarts per group.

See the example · Permalink

M Why might a soft-delete query filter cause duplicate numbers, and how do you avoid it? concept

Rows hidden by the filter still exist and still hold their numbers in the unique constraint. If the highest number is read through the filter, a removed student's number can be handed out again and the insert fails. Read the highest number with IgnoreQueryFilters, while counting capacity with the filter.

See the example · Permalink

Senior (6-9 yrs)

S How do you test that a lock really serializes concurrent requests? design

Make the timing deterministic: in the test, take the lock with one connection and hold it, start the operation on a second connection, check that it has not finished after a delay, release the lock, and check the operation then completes correctly. Sending two requests by hand rarely overlaps reliably.

How to answer: Explain why naive parallel tests are flaky, describe the hold-the-lock pattern, and mention cleaning up test data when the code under test runs its own transaction.

See the example · Permalink

S When would you choose pessimistic locking over optimistic concurrency? design

When conflicts are likely and the work under the lock is short, such as many clerks enrolling into the same sections in admission week: waiting briefly is better than failing and retrying. Choose optimistic concurrency when conflicts are rare or when a user thinks between reading and saving, where holding a lock would block others for minutes.

How to answer: Compare conflict rate, lock duration, user experience, and deadlock risk, with an example of each from the SMS (enrollment versus fee payment screens).

See the example · Permalink

Architect / Lead (10+ yrs)

A Admission week overloads one popular section with waiting enrollments. How would you scale the design? design

Keep the lock scope minimal and the locked work short; move slow work (notifications, documents) after the commit or to a background queue; consider assigning roll numbers in a later batch step so admission only reserves a place; and use a queue per section if demand truly exceeds what one lock can serve.

How to answer: Identify the contention point, reduce work inside it, separate reservation from numbering, and discuss asynchronous processing and its effect on the user experience.

See the example · Permalink

Resources

Code for this lesson: git tag lesson-78-enrollments (code repository coming soon)
Files: src/SchoolApp.Core/Services/EnrollmentService.cs, src/SchoolApp.Data/EfCore/EfEnrollmentRepository.cs, src/SchoolApp.Api/Controllers/EnrollmentsController.cs, tests/SchoolApp.Tests/Integration/EnrollmentTests.cs, library-assignment/078-library-issue-lock.cs
Video: coming soon

What's next

Students are in their sections with roll numbers. Next: taking attendance for a whole section in one request, and reading attendance across the 600,000 records in SchoolDb. Lesson 79: Attendance

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

Introduction​

Why this matters​

The concept​

Example: the check-then-act race​

Example: locking the section​

Example: error numbers for enrollments​

Apply it to the SMS​

Step 1: the request and the interface​

Step 2: the service​

Step 3: the lock in EF Core​

Example: counts and roll numbers differ​

Step 4: the controller​

Step 5: every outcome​

Step 6: proving the lock​

Example: how long to hold a lock​

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: the check-then-act race

Example: locking the section

Example: error numbers for enrollments

Apply it to the SMS

Step 1: the request and the interface

Step 2: the service

Step 3: the lock in EF Core

Example: counts and roll numbers differ

Step 4: the controller

Step 5: every outcome

Step 6: proving the lock

Example: how long to hold a lock

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