How to implement atomic transaction in mysql?

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

Solution 1:

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