SQL: Incorrect by Construction

The design of SQL and relational database systems makes it easy to accidentally introduce serious concurrency bugs. Below is a textbook money-transfer procedure in TSQL; Alice wants to send ten dollars to Bob, and to keep Alice from overdrafting her account, we first check that she has enough money. The code looks completely reasonable, but it has several critical bugs. Can you spot them?

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

Atomicity

First, if this procedure aborts midway through, we might transfer money from Alice’s account without transferring any to Bob. Alice won’t be happy about that, and we’ve destroyed money in the process. We want all of the transfers to succeed, or none of them; the fix is to wrap the procedure in a transaction:

BEGIN TRANSACTION ; 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 COMMIT TRANSACTION ;

TOCTOU

Are we done yet? Not quite. Suppose Alice fires off two transfers to Bob in parallel, T1 and T2. Let’s map out what happens:

T1: Check Alice’s account balance T2: Check Alice’s account balance T1: Withdraw 10 from Alice’s account T2: Withdraw 10 from Alice’s account T1: Deposit 10 in Bob’s account T2: Deposit 10 in Bob’s account

Note how T2 checks the balance before T1 has withdrawn any money from Alice’s account—so when T2 finally withdraws, the account might become overdrafted. This is a Time-of-check to time-of-use (TOCTOU) bug: The precondition changes between when we check it and when we act on it.

The fix is to lock Alice’s account until the transaction completes. We can change the isolation level so locks are acquired automatically, or lock the account row by hand:

... continue reading