Tuesday, July 17

Tag: concurrency

PostgreSQL anti-patterns: read-modify-write cycles

Craig's PlanetPostgreSQL
Shaun Thomas's recent post about client-side loops as an SQL anti-pattern is well worth a read if you're relatively new to SQL-based application development. It's reminded me of another SQL coding anti-pattern that I see quite a lot: the na├»ve read-modify-write cycle. Here I'll explain what this common development mistake is, how to identify it, and options for how to fix it. Imagine your code wants to look up a user's balance, subtract 100 from it if doing so won't make it negative, and save it. It's common to see this written as three steps: SELECT balance FROM accounts WHERE user_id = 1; -- in the application, subtract 100 from balance if it's above -- 100; and, where ? is the new balance: UPDATE accounts SET balance = ? WHERE user_id =1; and everything will appear to work fine to the developer. However, this code is critically wrong, and will malfunction as soon as the same user is updated by two different sessions at the same time. Imagine two concurrent sessions, each subtracting 100 from the user's balance, starting with an initial value of 300. Session 1Session 2 SELECT balance FROM accounts WHERE user_id = 1; (returns 300)   SELECT balance FROM accounts WHERE user_id = 1; (also returns 300) UPDATE balance SET balance = 200 WHERE user_id = 1; (300 - 100 = 200)   UPDATE balance SET balance = 200 WHERE user_id = 1; (300 - 100 = 200) Whoops!. The balance is 200, but you took out 200 from a starting point of 300. So 100 has just vanished. Most testing and development is done on standalone servers running single sessions, so unless you're doing rigorous testing this sort of thing often doesn't get noticed until production, and can be painful to debug. It's important to know about it so you can code defensively. (more…)