Lobste.rs

SQL: Incorrect by Construction

6337041_58a0_4

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….

Continue reading at Lobste.rs chreke.com
Advertisement