MySQL operation with accounting application

Question

I have a table like the following:

transaction_id
user_id
other_user_id
trans_type
amount

This table is used to conduct transactions with an account for an application of type financing.

Its accounting for double entry, so moving from user A to B will insert two rows into a table similar to.

1, A, B, Sent, -100
1, B, A, Received, 100

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

For instance:

select sum(amount) from transactions where user_id=A

What is the best way to block a transfer of funds? My current code is as follows:

Start Transaction
Debit the sender account
check the balance of the sender 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 does not seem to work as expected. I see many online examples of transactions that speak mostly: start, debit sender, credit receiver, commit. But what is the best way to check the sender balance between them?

, . , 3K, 3K, , .

Answer 1

InnoDB MyISAM? MySQL MyISAM ( , ). , , , SERIALIZABLE, MySQL.

article , , , .

Answer 2

, " " "" . , " " ( ), .

, , , , , .

:

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;

... - .