6

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.

3
  • Can you ever have negative balances? Jan 3 '17 at 16:39
  • I'm also not sure what you mean with status initialized/failed Jan 3 '17 at 20:27
  • what solution did you find for this? Jun 11 '18 at 18:23
4
  • 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.

2
  • No other UPDATEs from other transactions will work on that record until ...
    – Jasen
    Jan 27 '19 at 8:03
  • @Jasen good catch. that's exactly right, my own oversight. I blaze through answers sometimes to fast. Jan 28 '19 at 19:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.