Why RepeatableRead isolation level not working with EF Core when trying to avoid inserting entities with the same key into the database?

Question

I'm trying to avoid multiple row insertion with the same Identifier field value into the database. Here is the code:

public async Task InsertTransaction(Transaction transaction)
    {
        await using var dbTransaction = await _dbContext.Database.BeginTransactionAsync(IsolationLevel.RepeatableRead);
                 
        var existing = await _dbContext.Set<Transaction>()
            .AsNoTracking()
            .FirstOrDefaultAsync(t => t.Identifier == transaction.Identifier);

        if (existing != null) {
            return;
        }

        _dbContext.Set<Transaction>().Add(transaction);
        await _dbContext.SaveChangesAsync();
        
        await dbTransaction.CommitAsync();
        return;
    }

I'm assuming that RepeatableRead isolation level should be enough here, as it should lock for reading queries with search criteria containing Identifier and all requests after first one stepping into transaction will wait for it to finish. However when running concurrent tests I'm getting multiple rows inserted with the same Identifier and things work properly only after changing transaction isolation level to Serializable.

Answer 1

I'm assuming that RepeatableRead isolation level should be enough here

No. REPEATABLE READ doesn't use key range locks for non-existent data. SERIALIZABLE is the only isolation level that does. Although SERIALIZABLE will take Shared (S) range locks, and so after multiple sessions hold the same S lock, the conflicting inserts will create a deadlock, instead of blocking the second session at the SELECT query.

I'm getting multiple rows inserted with the same Identifier

In addition to an insert-if-not-exists transaction, you should also have a unique index on Identifier to prevent duplicates.

In SQL Server the lock hints to use are updlock,holdlock, which force restrictive update (U) locks, and key range locking. So something like:

public bool Exists<TEntity>(int Id) where TEntity: class
{
    var et = this.Model.FindEntityType(typeof(TEntity));
    return this.Set<TEntity>().FromSqlRaw($"select * from [{et.GetSchema()??"dbo"}].[{et.GetTableName()}] with(updlock,holdlock) where Id = @Id",new SqlParameter("@Id",Id)).Any();
}

You still need a transaction or the U locks will be released immediately, but the transaction can be at the default READ COMMITTED isolation level.