I'm not a developer (I generally only write shell/Perl/Python scripts to make my own job easier) or a database expert but wouldn't this issue be pretty easy to avoid if the whole process were wrapped up in a transaction? E.g.:
BEGIN TRANSACTION;
SELECT balance AS balance1 FROM giftcards WHERE gift_card_id = 1;
SELECT balance AS balance2 FROM giftcards WHERE gift_card_id = 2;
UPDATE giftcards SET balance = balance1 - 5 WHERE gift_card_id = 1;
UPDATE giftcards SET balance = balance2 + 5 WHERE gift_card_id = 2;
END TRANSACTION;
Obviously, this is somewhat simplified and you'd have various checks to make sure balance1 was actually >= 5, etc.
Transactions do not necessarily get processed serially. For example, if there were two concurrent requests and request #1 had just completed the first UPDATE when request #2 jumps in and performs the first two SELECTs, then request #2 will incorrectly update the balance for card #2. Different database systems offer different ways to serialize transactions, usually with a cost of performance and complexity.
Note that the post says:
> The only right way to do it is a pessimistic lock (FOR UPDATE clause).
This is not true. Banks deal with this problem all the time. You don't have to use a database engine as the serializer, despite what all the books tell you. My preference would be to explicitly serialize transactions rather than rely on database tricks - i.e. write accounting entries to ledgers and have a service that processes those entries on a single thread. For many scenarios this is more than good enough. If you needed lower latency, you could process this all in memory and use the database purely to replay the transaction log on restart for unprocessed transactions. In either implementation you could implement optimizations to process entries on different threads.
If you make 2 updates, check the balance to ensure > 0, and rollback if < 0, and you do all of this in a transaction, doesn't concurrency no longer matter? If another transaction beats you to the punch, won't the balance check query reflect that?
The semantics of the SQL you wrote depends on the transaction isolation level and flavour of transaction implementation. Read committed vs repeatable read (common terms for specific semantics) makes a difference as to what selects can see with respect to concurrent transactions. The way the SQL is written can also affect how reads and writes interleave.
Repeatable read isn't super-expensive in modern databases with MVCC support - these should prevent the situation in the article if the SQL is as simple as you write.
There's a strong hint that it isn't, though; there appears to be a two-phase state machine involved, with two requests. No sane developer will write a transaction that starts in one request and commits in another.
You're not missing anything. This is what transactions are for. If you need to make multiple changes to a database and you need all of them or none of them to happen, you need a transaction.
Don't know what they're teaching the kids nowadays that they would let money be moved around without using transactions.
You know what they say about assumptions, of course not, they use message queues for transactions and have a great queuing system built on acid databases
Again, I'm not a developer, so what am I missing?