3

What is the recommended way to handle double-spending issues in MySQL? Say, for example, I have an application that transfers $x from account A to B like so:

  1. Start a transaction
  2. Ensures account A has balance >= $x
  3. Add $x to B's balance
  4. Deduct $x from A's balance
  5. Commit (or rollback if a query failed)

I'm considering the scenario where two identical transactions are executed concurrently to transfer $x from A to B. As long as A's balance is at least $x, the check in step #2 will pass for each transaction, as neither has updated the balance yet. This would allow an attacker to issue two transactions for the entirety of A's balance and have them both executed, therefore enabling him to spend double the account's balance.

What is the recommended way to combat this? Would setting the MySQL isolation level to REPEATABLE_READ suffice to protect against this? Alternatively I could ensure the query that deducts from A's balance includes a WHERE balance > $x clause, and then rollback the transaction if there were zero rows affected. However, I'd ideally like to have this handled by the DBMS layer itself.

Thanks!

Paul B
  • 349
  • 4
  • 14

1 Answers1

1

maybe you could create a trigger that throws an error when balance is < 0 on update.

and then just update balance to balance -x in your query with no where clause and that would prevent balance from being updated to less than zero

always try to take money out of an account first before putting in another account. it makes more sense logically too as you need to take money out of bank before you could transfer or buy something. i don't know why you have it in reverse order right now.

Tin Tran
  • 6,194
  • 3
  • 19
  • 34