How to implement atomic transaction in mysql?

Question

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

Miscarriage

you should use TRANSACTIONs – Hopi

The selects are irrelevant, you wrap a transaction around the INSERTS and UPDATES so that they all get run successfully or the ones that comeplete before the error get rolled back so the db looks like nothing happened. You are only changeing Table C – Preoccupied

But someone can "break" data in select. Like, someone can change my datas like "critical section". – Miscarriage

You can use either insert ... select ... or locking reads (select ... for update). – Rennin

What is to stop whatever you call "break" the data happening 1 milli second after you release the transaction locks – Preoccupied

I updated my question, added examples. See Sad example part. – Miscarriage

This is what locking reads are for. – Rennin

Answer 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

Defeatist

Hi Bill, is "SERIALIZABLE" transaction solution for my example? – Miscarriage

SERIALIZABLE turns plain reads into locking reads as if you had done SELECT ... LOCK IN SHARE MODE for every query. This would block concurrent updates against the locked rows, but you'd still have to take care to make a single SELECT that would lock multiple resources. – Defeatist

Answer 2

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:

1. Begin a transaction.
2. UPDATE Account SET Balance = Balance - 5 WHERE User = 'A' AND Balance >= 5
3. If the rows affected returned by #2 is zero, rollback the transaction - this would indicate insufficient funds, otherwise continue
4. UPDATE Account SET Balance = Balance + 5 WHERE User = 'B'
5. Commit transaction

I believe that should eliminate the possibility of any race conditions while also eliminating unnecessary reads.

Azoth

Answer 3

There may be great options out there, but what I usually do is to put a lock on the read. You can set lock on the rows by using SELECT ... FOR UPDATE and this will put an exclusive lock on the row.

User see A = 1, B = 1 in dashboard
User will send request

SELECT A FOR UPDATE => no transaction can access A 
SELECT B FOR UPDATE => no transaction can access B 
INSERT A + B // result is 12

The downside of is the performance. Even though we are just selecting A and B, we are putting exclusive lock which will block all the readers and writers. So, set the transaction as short as possible.

Beside there can be some deadlock situation. For example, let's say there are 2 transactions Tx1 and Tx2

Tx1                                             Tx2
SELECT A FOR UPDATE
                                                SELECT B FOR UPDATE       
SELECT B FOR UPDATE 
                                                SELECT A FOR UPDATE

In above situation, because Tx1 and Tx2 has put exclusive lock on A and B, following transactions can't access A and B. Tx1 will try to access B but it's locked by Tx2, Tx2 will try to access A but it's locked by Tx1. This is the deadlock situation that might happen. Some DBMS such as MySQL automatically detects deadlocks and abort the transaction.

Unguent