SQL: Incorrect by Construction? The Hidden Dangers of Concurrency

A common pattern in database programming is the "check-then-act" sequence: verify a condition (like an account balance) and then perform an update based on that condition. To a developer, this looks like a straightforward logical flow. However, in a concurrent environment, this pattern is a minefield of potential failures.

When we examine a standard money-transfer procedure in T-SQL, we find that what appears to be reasonable code is often "incorrect by construction" because it ignores the fundamental complexities of concurrency, isolation, and locking.

The Anatomy of a Failing Transaction

Consider a simple scenario: Alice wants to send $10 to Bob. A beginner might write the following:

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

While logically sound in a single-threaded world, this code suffers from three critical architectural flaws.

1. The Atomicity Gap

If the system crashes or the connection drops after the first UPDATE but before the second, money vanishes from Alice's account without ever reaching Bob. This violates the principle of atomicity—the "all or nothing" guarantee. The immediate fix is wrapping the logic in a BEGIN TRANSACTION and COMMIT TRANSACTION block.

2. TOCTOU (Time-of-Check to Time-of-Use)

Even with a transaction, a race condition exists. If Alice initiates two transfers simultaneously (T1 and T2), both might read the balance before either has performed a withdrawal.

1. T1 reads balance: $15
2. T2 reads balance: $15
3. T1 withdraws $10 (Balance: $5)
4. T2 withdraws $10 (Balance: -$5)

This is a classic TOCTOU bug. The precondition (balance $\ge$ 10) changes between the check and the action. To solve this, developers must use locking hints like UPDLOCK to ensure that the row is locked the moment it is read.

3. The Deadlock Trap

Locking introduces a new problem: deadlocks. If Alice transfers to Bob while Bob simultaneously transfers to Alice, T1 may lock Alice's account and wait for Bob's, while T2 locks Bob's account and waits for Alice's. Neither can proceed.

To mitigate this, locks must be acquired in a consistent, deterministic order (e.g., always locking the account with the lower alphabetical ID first) or acquired upfront for all involved parties.

The Debate: Is SQL the Problem?

These examples lead to a provocative question: Is SQL "incorrect by construction" because it allows these patterns to exist? The author of the original analysis suggests that we need a "fearless concurrency" model similar to Rust, where correct behavior is the default and unsafe operations are explicit.

However, this perspective is heavily contested by experienced database engineers. The counter-arguments generally fall into three categories:

The "SQL 101" Argument

Many argue that these aren't flaws in SQL, but rather a lack of fundamental knowledge. As one critic noted, the original snippet "absolutely does not" look reasonable to anyone familiar with client-server databases. From this view, the issues are not with the language, but with the developer's failure to apply basic transaction and locking principles.

The Implementation Argument

Critics point out that modern database engines have built-in safeguards. For instance, SQL Server and MySQL can detect deadlock cycles, automatically kill one of the transactions (the "victim"), and return an error code (e.g., Error 1205 in SQL Server), expecting the application to retry. Thus, a deadlock isn't a permanent system freeze, but a recoverable error.

The Idiomatic Argument

Experienced developers suggest that the "check-then-act" pattern itself is the mistake. Instead of selecting a value into a variable, the idiomatic SQL approach is to perform a conditional update in a single statement:

UPDATE accounts 
SET balance = balance - 10 
WHERE owner = 'alice' AND balance >= 10;

IF @@ROWCOUNT = 1
BEGIN
    UPDATE accounts SET balance = balance + 10 WHERE owner = 'bob';
END

This approach leverages the database's internal locking mechanisms more efficiently and eliminates the TOCTOU window entirely.

Beyond Updates: The Ledger Pattern

A recurring suggestion from the community is to move away from storing balances as mutable columns entirely. Instead, an append-only ledger is recommended. In this model, you never UPDATE a balance; you only INSERT transaction records.

"Generally I would recommend an append only data structure here, not a bunch of updates... insert parallel rows into the ledger."

By treating the balance as a derived value (the sum of all ledger entries), you eliminate many of the locking complexities associated with updating a single row and create a perfect audit trail.

Conclusion

Whether SQL is "incorrect by construction" or simply "powerful and dangerous" depends on the user's expertise. While the language provides the tools to ensure absolute correctness—through serializable isolation levels, check constraints, and atomic updates—the defaults are often tuned for performance over strict correctness. For systems where a single failure is catastrophic, the responsibility lies with the architect to move beyond basic SQL patterns and embrace rigorous concurrency controls or immutable ledger designs.