EF Core concurrency: preventing stock overselling with atomic updates and FOR UPDATE locks

// ======================================================================
// Q: 100 requests try to reserve the last items in stock. How do you avoid
// overselling?
//
// 1. Atomic conditional update: the best option for counters. One statement, no
// retries.
// 2. Pessimistic lock (SELECT ... FOR UPDATE) when you need complex logic on the
// row.
// Comparison of techniques (when to use / cost):
// - Atomic ExecuteUpdate: counters, stock, simple rules. Minimal cost, but it
//   bypasses the change tracker and domain events.
// - Optimistic (concurrency token + retry): low contention, edits by users.
//   Cost: retries when conflicts occur.
// - Pessimistic (FOR UPDATE): high contention, complex logic. Cost: locks,
//   waiting, deadlock risk. Keep transactions very short.
// - Database constraint: always, as the last line of defense. CHECK (available
//   >= 0), unique indexes.
// For worker queues: FOR UPDATE SKIP LOCKED, so two workers do not pick up the
// same rows (the demo does this in the OutboxProcessor). FromSql with
// interpolation is parameterized. FromSqlRaw with string concatenation is not
// (SQL injection).
// ======================================================================

// ✗ Problem: read-modify-write race
// ✗ read-modify-write: two requests both read 3, and both write 3 - 2 = 1
var stock = await db.Stock.SingleAsync(s => s.Sku == sku, ct);
if (stock.Available < qty) return false;
stock.Available -= qty;
await db.SaveChangesAsync(ct);

// Fix 1: atomic conditional update (best for counters)
var rows = await db.Stock
    .Where(s => s.Sku == sku && s.Available >= qty)
    .ExecuteUpdateAsync(s => s.SetProperty(x => x.Available, x => x.Available - qty), ct);
return rows == 1;       // 0 = there was not enough stock

-- SQL: UPDATE stock_items SET available = available - @qty
--      WHERE sku = @sku AND available >= @qty

// Fix 2: pessimistic lock with SELECT ... FOR UPDATE
await using var tx = await db.Database.BeginTransactionAsync(ct);
var stock = await db.Stock
    .FromSql($"SELECT * FROM stock_items WHERE sku = {sku} FOR UPDATE")  // parameterized
    .SingleAsync(ct);
// ... the others wait here until the commit
stock.Available -= qty;
await db.SaveChangesAsync(ct);
await tx.CommitAsync(ct);