Friday, June 23

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 appea