How to properly handle money transactions with MySQL?

Question

I have the following tables:

CREATE Account (id INTEGER, balance DECIMAL(13, 4))
CREATE Transaction (id INTEGER, user INTEGER, balance DECIMAL(13, 4), amount DECIMAL(13, 4), peer INTEGER)

So when a user transfers money to his peer, there will be 2 transactions in the system (1 for each) and both accounts will update to reflect the latest balance.

My question is how to make everything atomic, to avoid invalid balances or duplicates. I don't think I can use MySQL transactions due to the fact they don't work across tables - especially when one statement fails and we need a rollback.

I'm not sure how to implement such mechanism to make sure this feature is solid and won't break in weird corner cases.

Double is worse if anything money fields are exact so decimal is appropriate https://stackoverflow.com/questions/5150274/difference-between-float-and-decimal-data-type — P.Salmon, Jan 30 '18 at 16:09
If both 'transactions' have to succeed or on failure of either rollback wrap them both in a database transaction. — P.Salmon, Jan 30 '18 at 16:11

score 2 · Answer 1 · answered Jan 30 '18 at 16:23

2

You can't be sure that inserted data is not a duplicate unless you can control your whole stack. Having worked in several financial companies, here are my suggestions:

Add a client-side id for every post, store this in the db, then check for it in new posts.
Only ever insert (so version updates) and use soft deletes. you never want to delete the paper trail.
STORE MONEY AS DECIMAL, NOT DOUBLE

answered Jan 30 '18 at 16:23

catbadger

1,662
2
18
27

I've updated my question to use decimals. As for the transactions, I don't intend to delete, I just want an easy way to find current balance and making sure everything is atomic (I don't want to withdraw money from user A and failing to deposit to user B without the rolling back the first one) – Gilad Novik Jan 30 '18 at 16:33
I wouldn't store the balance in the account table, instead i would calculate it every time in the models (using bcmath in PHP to avoid those awful rounding errors in FP math). it's too easy for things to get out of sync otherwise. – catbadger Jan 30 '18 at 16:41
what if while I fetch the latest balance, I receive another transaction which changes the balance? it will end up with invalid balance – Gilad Novik Jan 30 '18 at 16:44
Well you COULD use locking, but that's a whole other box of problems. – catbadger Jan 30 '18 at 21:07

How to properly handle money transactions with MySQL?

1 Answers1