3 ms·

Well the irony of this example is that Postgres and MySQL struggle with it too because they use row-level locking by default, not serializable (full ACID). You'

Well the irony of this example is that Postgres and MySQL struggle with it too because they use row-level locking by default, not serializable (full ACID). You'd be ok if you repeatedly updated a balance row as a way to lock things, but if you relied on summing withdrawal/deposit rows to determine balances as you would in a fully normalized schema, you'd get a race condition allowing Bob to overdraw. You can put the transaction in serializable mode, but it becomes very slow and non-scalable. So you have to work around this with a little denormalization or separate pending/active balances. But yes it'd be awful doing this with files.

kortilla 4y ago
They don’t “struggle” with it at all. You presented two approaches that work fine (a balance row or serialized transactions). It can also be done with monotonic “account transaction” counters with unique constraints to prevent the concurrent inserts of withdrawals. It’s trivially solvable and it never results in complete DB corruption by getting the ordering wrong.
- hot_gril 4y ago
  Serialized transactions don't work fine. That's one thing Postgres itself struggles with. Your xacts will conflict and both fail, and you might even exceed queue limits as you retry. It's just not optimized for serializable xacts, which is a conscious decision because you inevitably sacrifice too much performance with it. The working approaches aren't so obvious to users unfamiliar with the details of Postgres. People think they have full ACID, and it's not apparent that rows and uniqueness constraints are magically transaction-safe while aggregates are not. If you're following the usual advice of schema normalization, you will get something with race conditions. Postgres is still my favorite DBMS regardless. Maybe there can be a clearer way to advise users.
  - kortilla 4y ago
    > Serialized transactions don't work fine. That's one thing Postgres itself struggles with. Your xacts will conflict and both fail, and you might even exceed queue limits as you retry. Of course they work fine. You’re getting hung up in optimizing for scale when you’re already way beyond solving the two threads one file issue.

kortilla 4y ago

They don’t “struggle” with it at all. You presented two approaches that work fine (a balance row or serialized transactions). It can also be done with monotonic “account transaction” counters with unique constraints to prevent the concurrent inserts of withdrawals. It’s trivially solvable and it never results in complete DB corruption by getting the ordering wrong.

hot_gril 4y ago
Serialized transactions don't work fine. That's one thing Postgres itself struggles with. Your xacts will conflict and both fail, and you might even exceed queue limits as you retry. It's just not optimized for serializable xacts, which is a conscious decision because you inevitably sacrifice too much performance with it. The working approaches aren't so obvious to users unfamiliar with the details of Postgres. People think they have full ACID, and it's not apparent that rows and uniqueness constraints are magically transaction-safe while aggregates are not. If you're following the usual advice of schema normalization, you will get something with race conditions. Postgres is still my favorite DBMS regardless. Maybe there can be a clearer way to advise users.
- kortilla 4y ago
  > Serialized transactions don't work fine. That's one thing Postgres itself struggles with. Your xacts will conflict and both fail, and you might even exceed queue limits as you retry. Of course they work fine. You’re getting hung up in optimizing for scale when you’re already way beyond solving the two threads one file issue.