3 ms·
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
by 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 agoSerialized 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.