- 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.