Whenever multiple MySQL threads (connections) try to access the same resource (row) simultaneously, and at least one of these accesses being a write, it may be considered a race condition. A race condition may be hard to reproduce and locate because the end results are generally random and depend on the comparable timings in the intervening threads.
Data-races are not always harmful. For example, nothing breaks if your friend updates their post the same time you read it. However, there are some cases when it makes a big difference.
Imagine your Amazon balance is $100 and you’re paying a $100 for a gaming headphone. What if you open checkout page in two different tabs or devices and hit the Submit button on both the pages at the very same time!
You would probably expect one of the tabs to show an error message. Right? That’s what will happen unless the page is affected by a race condition.
Here’s the pseudocode of how the checkout script would look like:
1 FN GET_BALANCE(AMAZON_ID) {
2 VAR RECORDS = DB.QUERY(“SELECT BALANCE FROM ACCOUNTS WHERE ACCOUNT_NUMBER = ” + AMAZON_ID);
3 RETURN RECORDS[0][“BALANCE”];
4 }
1 VAR BALANCE = GET_BALANCE(AMAZON_ID);
2 IF (BALANCE >= 100) {
3 DEDUCT_BALANCE(AMAZON_ID, 100);
4 CREATE_ORDER(ORDER);
5 } else {
6 SHOW_ERROR();
7 }
If there are two threads that hit the same exact instruction to read the balance (at line 1), then both of these threads believe that the user has sufficient balance to make this order. The balance is then deducted twice at Line 3, so the first thread would make it from $100 to $0 (as expected) and then the second thread would once again take it from $0 to -$100.
So, in this case, a race condition helps you buy the same thing twice by paying for only one.
The problem with this logic is that all of the concurrent threads execute the SELECT (i.e., GET_BALANCE) get the same value for balance.
We can fix this by introducing a transaction and then locking the account row.
START TRANSACTION;
SELECT BALANCE FROM ACCOUNTS WHERE ACCOUNT_NUMBER = 500 FOR UPDATE;
UPDATE ACCOUNTS SET BALANCE = BALANCE – 100 WHERE ACCOUNT_NUMBER = 500;
COMMIT;
So now, when the balance is selected for update, it will lock that row until the transaction is committed (doesn't need to specifically update the row) or times out. This means that the other transactions will also be blocked, and they would automatically have to wait until this lock is released.