会计用mysql,MySQL会计应用交易

I have a table like the following:

transaction_id

user_id

other_user_id

trans_type

amount

This table is used to maintain the account transactions for a finance type app.

Its double entry accounting so a transfer from User A to B would insert two rows into the table looking like.

1, A, B, Sent, -100

1, B, A, Received, 100

The balance on any account is calculated by summing up the transactions for that account.

For example:

select sum(amount) from transactions where user_id=A

What is the best way to lock down transferring of funds? My current code looks like:

Start Transaction

Debit the sender's account

check the balance of the sender's account

if new balance is negative then the sender didn't have enough money and rollback

if the balance is positive then credit the receiver and commit

This seems not to be working exactly as expected. I see a lot of examples online about transactions that say basically: start, debit sender, credit receiver, commit. But what is the best way to check the sender's balance in between?

I have transactions getting through that shouldn't. Say a user has a balance of 3K and two transactions come in at exactly the same time for 3K, both of these are getting through when only one should.

Thank you

解决方案

Are you using InnoDB tables or MyISAM tables? MySQL doesn't support transactions on MyISAM tables (but it won't give you an error if you try to use them). Also, make sure your transaction isolation level is set appropriately, it should be SERIALIZABLE which is not the default for MySQL.

This article has a good example that explains the impact of the different isolation levels using an example very similar to yours.