SQL's design makes it easy to write code that looks correct but contains serious concurrency bugs. A recent analysis highlights three common pitfalls in a simple money-transfer procedure: missing atomicity, time-of-check to time-of-use (TOCTOU) errors, and deadlocks. Each requires explicit workarounds that complicate otherwise straightforward queries.
The three bugs
Consider a textbook transfer: Alice sends $10 to Bob, with a check that Alice has sufficient funds. The naive TSQL code:
DECLARE @balance INT;
SET @balance = (SELECT balance FROM accounts WHERE owner = 'alice');
IF @balance >= 10
BEGIN
UPDATE accounts SET balance = balance - 10 WHERE owner = 'alice';
UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
END
1. Missing atomicity. If the procedure aborts after debiting Alice but before crediting Bob, money is destroyed. The fix is wrapping the operations in a transaction (BEGIN TRANSACTION / COMMIT TRANSACTION).
2. TOCTOU race condition. If two parallel transfers both check Alice's balance before either withdraws, both may proceed, potentially overdrafting the account. The fix is locking Alice's row during the check, e.g., using SELECT ... WITH (UPDLOCK) in SQL Server. This ensures other transactions block until the lock is released.
3. Deadlocks. If Alice and Bob simultaneously transfer to each other, each transaction may lock one account and wait for the other, causing a standstill. The fix is acquiring all locks upfront in a consistent order (e.g., lock both accounts before any balance check). Note that locks are acquired in the order rows are read by the database, not the order in the result set, so an ORDER BY clause may not prevent deadlocks.
The cost of correctness
After applying all three fixes, the original 10-line procedure grows by about 50% and becomes harder to read. The author argues that for systems where correctness matters—medical records, financial transactions—SQL's default behavior is dangerous. The proposed alternative is a language that adopts Rust's "fearless concurrency" approach: make correct behavior the default, provide explicit escape hatches, and use static analysis to detect potential deadlocks. Deterministic database systems are one possible solution, though they may trade throughput for safety.
Bottom line
SQL's concurrency model requires developers to manually manage transactions, locks, and ordering—tasks that are easy to get wrong. For high-stakes applications, consider using database features like SELECT FOR UPDATE, check constraints, or deterministic systems to reduce risk. The safest approach is to assume your SQL is buggy until proven otherwise.