Implement "account" transactions in postgresql

Question

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:

For deposits:

1. Allow deposit money directly as an insert.

For withdraws:

1. Check balance > amount of withdraw before continue.
2. Insert withdraw money transaction with status initiated.
3. Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.
4. Update withdraw money transaction status completed if status is initiated.

Any materials or examples are welcomed.

Answer 1

• Good Idea For deposits: Allow deposit money directly as an insert.
• Bad Idea For withdraws: Check balance > amount of withdraw before continue.
  • Consider instead having a CHECK (balance >= 0), then just allow the transaction to fail.

I'm not sure about the rest of them.. You mention all this STATUS stuff, but what does it matter?

BEGIN;
UPDATE accounts
  SET balance = balance - 100
  WHERE name = 'Bob';
UPDATE accounts
  SET balance = balance + 100
  WHERE name = 'Sally';
END;

• If the balance on accounts would go under 0 with the deduction, it'll simply fail there.
• If it passes then you will see bob's balance go down for your transaction.
• Adding 100 to Sally's account is a no-brainier, but you have a lock on that too now.

The default transaction levels permit phantom reads, but here that's not an issue. The first statement is an UPDATE. It either works, or it doesn't. If it works, UPDATE gets ROW EXCLUSIVE lock. No other UPDATEs will work on that record until the transaction is committed.