EF Core: None of the IsolationLevel works when adding new entry to database

Question

I have a County table and have this piece of code to create new entries in the table, using ASP.NET Core 6 and EF Core 6:

public async Task<County> Add(County entity, CancellationToken cancellationToken)
{
    await using (var transaction = await _dbContext.Database.BeginTransactionAsync(IsolationLevel.ReadCommitted, cancellationToken))
    {
            var maxGroupId = await _dbContext.County.MaxAsync(c => c.GroupId, cancellationToken);
            entity.GroupId = maxGroupId + 1;
            await _dbContext.County.AddAsync(entity, cancellationToken);
            await transaction.CommitAsync(cancellationToken); // --> BREAKPOINT <--
    }
    return (await _dbContext.AddAsync(entity, cancellationToken)).Entity;
}

The idea is that every new entry should get a GroupId that is bigger by 1 than the previous one, so after inserting NewCounty and AnotherNewCounty, the table should look like this:

Id |       Name         | GroupId
1  | County             |    1
2  | NewCounty	        |    2
3  | AnotherNewCounty	|    3

(Two entries can have the same GroupId with another use-case, that's why it's not just an auto-incremented value by 1, but i need to ask the db for the biggest GroupId every time i insert a new value.)

This works as expected with 1 instance of the application, but let's say I want to make sure that entries are added with the correct GroupId even if 2 people want to add a new entry at the same time. I'm trying to simulate that use-case like this:

1. I launch instance1 of the application in debug, set the breakpoint at the CommitAsync() (please see the code at the beginning for the breakpoint)
2. I call the endpoint by inserting a new County with a Name : NewCounty1
3. The execution is now at the breakpoint
4. I launch instance2 of the application
5. I call the endpoint by inserting a new County with a Name : NewCounty2
6. NewCounty2 is inserted
7. I switch to instance1 and release the execution from the breakpoint, so NewCounty1 is inserted

The table looks like this, both of them got the same GroupId, which is incorrect:

Id |    Name    | GroupId
1  | County     |   1
2  | NewCounty2 |   2
3  | NewCounty1 |   2

Should look like this:

Id |    Name    | GroupId
1  | County     |    1
2  | NewCounty2	|    2
3  | NewCounty1	|    3

I went through the different IsolationLevel-s when calling BeginTransactionAsync() but none of them works for me. I know that there is a description for them, but because of the lack of my knowledge, they are not really descriptive for me in terms of which database operations they translate to exactly.

In this use-case, i would like instance2 to see that instance1 already "reserved" that GroupId in its transaction and is locking that GroupId to prevent instance2 to assign that same GroupId to a new County.

Is there something wrong with my code or it's the way that I'm trying to replicate the use case is what's wrong? Or maybe both? Thank you!

Answer 1

Hi @Márton Szigeti,

What you expect is actually called Dirty Reads. In Read Committed Isolation Level, databases prevent Dirty Reads by ensuring that every read returns only the values that were committed to the database and not the value that is being written by an ongoing transaction.

For more details, you can check this article: Database Isolation Levels explained.

I think it may not be appropriate to use transactions in this case. You can save it in the database directly after adding, so as to ensure that the data read by instance 2 is the data after instance 1 is inserted:

public async Task<County> Add(County entity, CancellationToken cancellationToken)
{
    var maxGroupId = await _dbContext.County.MaxAsync(c => c.GroupId, cancellationToken);
    entity.GroupId = maxGroupId + 1;
    await _dbContext.County.AddAsync(entity, cancellationToken);
    await _dbContext.SaveChangesAsync();
    return (await _dbContext.AddAsync(entity, cancellationToken)).Entity; // --> BREAKPOINT <-
}

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,

Chen Li

Answer 2

the isolation level that you need is SERIALIZABLE. this may greatly limit database performance. If you can allow gaps in the number, you should use a sequence.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver16