I am working on my demo project - its simple bank.
I have one problem.
I need to add some virtual money into my account.
But I need to do it "like atomic operation", I need to query some data before update.
Like:
Query table A // select from table A
Query table B // select from table B
if (A B > X)
Add money // insert into table C
Problem is, that during the query A or B another thread can start some work.
Which technique of mysql should I use?
Example: Happy example
User see A = 1, B = 1 in dashboard
User will send request
SELECT A
SELECT B
INSERT A B // result is 2
Sad example
User see A = 1, B = 1 in dashboard
User will send request
SELECT A
// SOMEONE CHANGED B RIGHT NOW TO 10 !
SELECT B
INSERT A B // result is 12
CodePudding user response：
Transactions alone will not do what you need. Plain read queries in MySQL do not prevent other sessions from updating the rows.
Read about locking reads in MySQL here: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking-reads.html
To prevent the race condition you mention, you would need to acquire the locks on A and B in a single atomic action. You could do this by doing a locking read on the two resources with JOIN or UNION.
You can also lock whole tables, and lock multiple tables atomically. see https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
CodePudding user response：
If I understand you correctly, I've done this in the past without using locking reads like so:
Assume user A wants to transfer $5 from their account to user B's account. Pseudocode for doing that safely could look like this:
- Begin a transaction.
UPDATE Account SET Balance = Balance - 5 WHERE User = 'A' AND Balance >= 5- If the rows affected returned by #2 is zero, rollback the transaction - this would indicate insufficient funds, otherwise continue
UPDATE Account SET Balance = Balance 5 WHERE User = 'B'- Commit transaction
I believe that should eliminate the possibility of any race conditions while also eliminating unnecessary reads.