在EF Core中实现悲观并发控制的方案咨询

Absolutely, you can solve this concurrency issue without adding a database unique constraint—let’s walk through practical, actionable approaches that fit your Open method’s requirements.

Option 1: Application-Level Locking (Single Instance Only) #

If your app runs on a single server, you can use a per-resource lock to ensure only one request processes a specific CashDrawerId at a time. We’ll use ConcurrentDictionary to manage locks per drawer, paired with SemaphoreSlim for lightweight locking:

First, add a static concurrent dictionary to your controller (or a dedicated service):

private static readonly ConcurrentDictionary<int, SemaphoreSlim> _cashDrawerLocks = new();

Then modify your Open method to acquire the lock for the target drawer before executing the check and insert:

[HttpPost("open")]
public async Task<IActionResult> Open(ReportCashDrawerStateRequest request)
{
    // Get or create a semaphore for the specific CashDrawerId
    var semaphore = _cashDrawerLocks.GetOrAdd(request.CashDrawerId, _ => new SemaphoreSlim(1, 1));
    
    try
    {
        await semaphore.WaitAsync();
        
        // Now this check is safe from concurrent requests for the same drawer
        if (await _db.CashDrawerStates.AnyAsync(s => s.CashDrawerId == request.CashDrawerId && s.EndTime == null))
            return UnprocessableEntity("Already open");
        
        var cashDrawerState = new CashDrawerState
        {
            CashDrawerId = request.CashDrawerId,
            StartTime = DateTime.UtcNow,
            StartedById = User.GetUserId(),
            StartingCashAmount = request.CashAmount
        };
        
        _db.CashDrawerStates.Add(cashDrawerState);
        await _db.SaveChangesAsync();
        
        return Ok();
    }
    finally
    {
        semaphore.Release();
        // Optional: Clean up unused semaphores to avoid memory leaks
        if (_cashDrawerLocks.TryGetValue(request.CashDrawerId, out var lockObj) && lockObj.CurrentCount == 1)
        {
            _cashDrawerLocks.TryRemove(request.CashDrawerId, out _);
            lockObj.Dispose();
        }
    }
}

Caveats: This only works for single-instance apps. If you scale to multiple servers, each instance will have its own lock dictionary, so concurrent requests across instances can still slip through.

Option 2: Database-Level Pessimistic Locking (Works for Multi-Instance) #

For multi-instance deployments, database-level locking is the most reliable approach. We’ll use a transaction with a locking query to prevent concurrent inserts of the same drawer’s open state.

The key is to use UPDLOCK and HOLDLOCK hints in your check query—this locks the range where the new record would be inserted, blocking other transactions from inserting or reading until your transaction completes:

[HttpPost("open")]
public async Task<IActionResult> Open(ReportCashDrawerStateRequest request)
{
    using var transaction = await _db.Database.BeginTransactionAsync();
    
    try
    {
        // Use locking hints to prevent concurrent checks/inserts
        var exists = await _db.CashDrawerStates
            .FromSqlRaw(@"SELECT 1 FROM CashDrawerStates 
                          WHERE CashDrawerId = @cashDrawerId AND EndTime IS NULL 
                          WITH (UPDLOCK, HOLDLOCK)", 
                          new SqlParameter("@cashDrawerId", request.CashDrawerId))
            .AnyAsync();
        
        if (exists)
        {
            await transaction.RollbackAsync();
            return UnprocessableEntity("Already open");
        }
        
        var cashDrawerState = new CashDrawerState
        {
            CashDrawerId = request.CashDrawerId,
            StartTime = DateTime.UtcNow,
            StartedById = User.GetUserId(),
            StartingCashAmount = request.CashAmount
        };
        
        _db.CashDrawerStates.Add(cashDrawerState);
        await _db.SaveChangesAsync();
        
        await transaction.CommitAsync();
        return Ok();
    }
    catch (Exception)
    {
        await transaction.RollbackAsync();
        throw;
    }
}

Alternatively, if you prefer to avoid raw SQL, you can use EF Core's LockMode (available in EF Core 7+ for some providers like SQL Server):

var exists = await _db.CashDrawerStates
    .Where(s => s.CashDrawerId == request.CashDrawerId && s.EndTime == null)
    .With(LockMode.UpdLock, LockMode.HoldLock)
    .AnyAsync();

How this works:

• UPDLOCK takes an update lock on the rows (or range) returned by the query, preventing other transactions from acquiring update or exclusive locks.
• HOLDLOCK ensures the lock is held until the end of the transaction, not just the query.
• Together, these hints block any concurrent transaction from checking or inserting a matching record until your transaction commits or rolls back.

Option 3: Distributed Locking (Multi-Instance, No Database Lock Hints) #

If you don’t want to use database lock hints, you can implement a distributed lock using tools like Redis (with RedLock.net) or Azure Blob Storage. This works similarly to the application-level lock but is shared across all instances.

For example, using RedLock.net:

1. Add the NuGet package RedLock.net
2. Configure the lock client in your startup
3. Modify your Open method to acquire a distributed lock for the CashDrawerId before processing

Caveats: Adds an external dependency on a distributed lock service, but keeps your database logic clean.

Final Notes #

While you asked to avoid unique constraints, it’s worth mentioning that adding a unique index on (CashDrawerId, EndTime) (with a filter for EndTime IS NULL) can act as a safety net—if something slips past your locking logic, the database will reject the duplicate insert. But if you strictly don’t want to add it, the above locking approaches will work.

内容的提问来源于stack exchange，提问作者Konrad