3 ms·

At the same time, this is like... data integrity 101. For future reference if you want to build a system that manipulates valuable information (such as monetar

At the same time, this is like... data integrity 101. For future reference if you want to build a system that manipulates valuable information (such as monetary accounts), and you've never done anything like that before: Please pick a database (relational or otherwise) with ACID transactions and replication, buy at least two different textbooks on that specific database, read both, do any exercises they suggest, and then decide whether you still want to build that system. If yes, then cool: don't hold me responsible if anything goes wrong, but you need to learn somehow. Yes, it's possible to build systems like that on non-ACID-compliant databases, or without using the ACID functionality. It's much harder, though, and you really must understand what you're giving up in order to have a reasonable chance of success.

xyzzy4747 4y ago
Probably the code had logic such as the following, which from an amateur perspective, seems to work. After all, the first check prevents the withdrawal if the account balance is too low, so what could go wrong? $balance = sql_query(“SELECT balance FROM accounts WHERE account_id = “ . $account_id); if ($withdrawal_amount < $balance) { withdraw_funds($account_id, $withdrawal_amount); } The correct solution of course requires putting the checks in an ACID transaction along with the account balance updating. This requires more work upfront (SQL is harder to write than PHP) and an impatient amateur coder probably didn’t think of it. Also I’m guessing they didn’t properly index all the DB columns and this made transactions take longer which exacerbates race conditions.
lmm 4y ago
IMO this is backwards advice. Traditional relational databases with ACID go to great lengths to create the illusion that there's a single, global state, and that's what leads to people doing things like check-and-set that seem to work. You're far better off using a non-ACID database, simply because incorrect approaches will fail more visibly and more often. Ultimately you do the same amount of work either way, but using a non-ACID database forces you to do it upfront rather than letting you silently mostly succeed with a fundamentally broken data model.
- BoorishBears 4y ago
  This is a really optimistic take... Realistically your DB of choice will probably try and tackle ACID half-heartedly because it's what devs expect, and that will lull you into the same false sense of security. Many "eventually consistent" DBs will still have some concept that uses the word "transaction" that works like one until it doesn't (see DynamoDB and multi-region gotchas)
- miohtama 4y ago
  Your comment is not true for high quality databases like PostgrSQL. Check-and-set just works on the correct isolation level. There is no isolation, your transaction will fail and be rolled back. If you are new to the subject there are extensive reports available from Jepsten, one of the leading names in distributed system and ACID anomaly testing and research. https://jepsen.io/ https://jepsen.io/
  - lmm 4y ago
    > Check-and-set just works on the correct isolation level. Which is not the default one. > There is no isolation, your transaction will fail and be rolled back. How so? Isn't it more likely you'll silently corrupt your data? Even if it does fail noisily, this is a condition you're unlikely to hit in testing, so you'll probably be dealing with it in production.

xyzzy4747 4y ago

Probably the code had logic such as the following, which from an amateur perspective, seems to work. After all, the first check prevents the withdrawal if the account balance is too low, so what could go wrong? $balance = sql_query(“SELECT balance FROM accounts WHERE account_id = “ . $account_id); if ($withdrawal_amount < $balance) { withdraw_funds($account_id, $withdrawal_amount); } The correct solution of course requires putting the checks in an ACID transaction along with the account balance updating. This requires more work upfront (SQL is harder to write than PHP) and an impatient amateur coder probably didn’t think of it. Also I’m guessing they didn’t properly index all the DB columns and this made transactions take longer which exacerbates race conditions.

lmm 4y ago

IMO this is backwards advice. Traditional relational databases with ACID go to great lengths to create the illusion that there's a single, global state, and that's what leads to people doing things like check-and-set that seem to work. You're far better off using a non-ACID database, simply because incorrect approaches will fail more visibly and more often. Ultimately you do the same amount of work either way, but using a non-ACID database forces you to do it upfront rather than letting you silently mostly succeed with a fundamentally broken data model.

BoorishBears 4y ago
This is a really optimistic take... Realistically your DB of choice will probably try and tackle ACID half-heartedly because it's what devs expect, and that will lull you into the same false sense of security. Many "eventually consistent" DBs will still have some concept that uses the word "transaction" that works like one until it doesn't (see DynamoDB and multi-region gotchas)
miohtama 4y ago
Your comment is not true for high quality databases like PostgrSQL. Check-and-set just works on the correct isolation level. There is no isolation, your transaction will fail and be rolled back. If you are new to the subject there are extensive reports available from Jepsten, one of the leading names in distributed system and ACID anomaly testing and research. https://jepsen.io/ https://jepsen.io/
- lmm 4y ago
  > Check-and-set just works on the correct isolation level. Which is not the default one. > There is no isolation, your transaction will fail and be rolled back. How so? Isn't it more likely you'll silently corrupt your data? Even if it does fail noisily, this is a condition you're unlikely to hit in testing, so you'll probably be dealing with it in production.