mysql transaction with accounting application

Question

Hello,

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

Answer 1

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.

Answer 2

InnoDB tables are being used.

Answer 3

The problem is that the concept of the "user account" is "scattered" through many rows in your table. With the current representation, I think you can't "lock the user account" (so to speak), so you are open to race conditions when modifying them.

A possible solution would be to have another table with user accounts, and lock a row in that table , so anybody needing to modify the account can try to obtain the lock, do the operation, and release the lock.

For instance:

begin transaction;
update db.accounts set lock=1 where account_id='Bob' and lock=0;
if (update is NOT successful) # lock wasn't on zero
  {
  rollback;
  return;
  }
if (Bob hasn't enough funds)
  {
  rollback;
  return;
  }

insert into db.transactions value (?, 'Bob', 'Alice', 'Sent', -3000);
insert into db.transactions value (?, 'Alice', 'Bob', 'Received',  3000);
update db.accounts set lock=0 where account_id='Bob' and lock=1;

commit;

... or something like that.