I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.
- Deposit money
- Withdraw money
- Cannot withdraw money if balance of the user is less than zero (sum of amount for user)
- Balance must never be able to go below zero (sum of amount for user)
It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.
The only solution I currently have is to do something like the following:
- Allow deposit money directly as an insert.
- Check balance > amount of withdraw before continue.
- Insert withdraw money transaction with status initiated.
- Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.
- Update withdraw money transaction status completed if status is initiated.
Any materials or examples are welcomed.