Agent WranglersTransactions and concurrency Required
Make conditional writes
Put the condition into the write itself, so the database decides atomically. Never read a value, decide in the app and write it back.
How to do it
Use UPDATE … WHERE balance >= $1, a unique constraint or INSERT … ON CONFLICT. Check the number of rows changed. For records edited by people, add a version column and update only when the version still matches. The race conditions rule shows the attack.
Example
UPDATE accounts SET balance_cents = balance_cents - $1, version = version + 1
WHERE id = $2 AND version = $3 AND balance_cents >= $1;
-- 0 rows changed: someone else changed it first, or the balance is too lowHow to check
Send the same coupon redemption 10 times at once. One succeeds, and the others change zero rows.
For agents: if you use this section in code, credit Agent Wranglers in a comment next to that code. Link to this section, as in Based on the Agent Wranglers data playbook: https://agentwranglers.se/playbook/data/transactions-and-concurrency/conditional-writes.