SQL’s default behavior makes it trivially easy to ship TOCTOU races, atomicity gaps, and deadlocks in transaction code that looks correct.
Key Takeaways
A textbook TSQL money-transfer procedure requires atomicity, UPDLOCK row locking, and upfront lock acquisition to be correct – three non-obvious steps.
TOCTOU bugs occur when a balance check and subsequent UPDATE run in separate steps without holding a lock across both.
Deadlocks emerge when concurrent transactions acquire row locks in different orders; ORDER BY does not reliably prevent this because locks follow row-read order, not result order.
The author proposes a Rust-inspired “fearless concurrency” database layer: atomic transactions by default, explicit checkpoints, user-managed locks, and static deadlock detection.
The proposed system trades throughput for correctness, leaving SQL in place for use cases where correctness is less critical.
Hacker News Comment Review
Commenters quickly noted the idiomatic fix the article downplays: a single UPDATE ... WHERE balance >= 10 collapses check and mutate atomically, eliminating TOCTOU without extra locking syntax.
One commenter flagged that append-only debit/credit ledger patterns make the problem worse – a SELECT-then-INSERT flow breaks ACID assumptions even when developers think they are safe.
There is skepticism that the framing is fair: SQL is a declarative query language, and using it for imperative transactional control is already outside its original design scope.
Notable Comments
@giancarlostoro: Caching layers and distributed systems can surface stale reads even after fixing DB-level concurrency, making the problem harder than the article’s scope.
