How to avoid the race condition and the negative value

Scenario

Suppose there is a table, bank.

name	money
A	100
B	100

Both A and B have 100, and A will transfer money to B twice.

Simple Design: Wire after Checking Balance

It would be like this in psudo-code:

A_owned = `select money from bank where name = A`
B_owned = `select money from bank where name = B`
if A_owned >= 70:
    `update bank set money = A_owned - 70 where name = A`
    `update bank set money = B_owned + 70 where name = B`

This is the simplest design; however, there are two defects:

1. Remained money is incorrect after updating.
2. After remitting, the balance comes to a negative value.

Let's watch these two issues separately.

The root cause of the first issue is that if A initiates two transfers at the same time, both of the two times A_owned would be 100. We introduce this situation in time-series diagram below:

Finally, the A's balance will become 30. This is the classic race condition. How to solve it?

Solution of Race Condition

In order to solve the race condition, there are three ways:

1. Atomic Update
2. Transaction plus Lock
3. Version Scheme

Atomic Update

The problem of this situation is using the unreliable data. To prevent this from happening, we shouldn't update records via using the previous results but the current value in the database.

Change the origin statement:

update bank set money = A_owned - 70 where name = A

to the new statement:

update bank set money = money - 70 where name = A

This is the atomic update.

Transaction plus Lock

Before the introduction, I have to say, most people have a misunderstanding of the transaction. The transaction is not invincible and cannot avoid all race conditions. Thinking the transaction as a panacea is so wrong.

The transaction is invincible IF the isolation level of MySQL is set to Serializable Isolation; nevertheless, Serializable Isolation affects the database performance dramatically. Therefore, the most accepted compromise is Repeatable Read Isolation. On the other hand, MySQL is not able to prevent the race condition in the such criteria. According to the reference, InnoDB cannot handle Lost Update and Phantom, i.e., the race condition will still be happened.

If you say that, what is the use of the transaction?

The transaction is to protect the integrity of a bunch of database operations. We can rollback all the changing data if there is any error occurred, so that the dirty data will not commit into the database.

Let's get down to the business; how to leverage the transaction? The example is as follows:

start transaction
A_owned = `select money from bank where name = A for update`
if A_owned >= 70:
    `update bank set money = A_owned - 70 where name = A`
commit

By using for update, it can acquire an exclusive lock on the desired row. If another transaction with the same row entered, it must wait for the lock released. The working flow is like below:

From the diagram above, we can know A2 gets the value after A1 has been finished. In addition, A2 gets 30 which is not enough to the transfer, thus, A2 finishes its job without doing anything.

Version Scheme

This approach is much more complicated than the two mentioned. We have to alter table to add a new column, version, to record the changes.

name	money	version
A	100	1
B	100	1

Before updating data, we have to retrieve the version first then update the row on the specific version. The code is like:

A_owned, old_ver = `select money, version from bank where name = A`
if A_owned >= 70:
    `update bank set money = 30, version = version + 1 where name = A and version = old_ver`

Updating data and comparing the version must be executed Simultaneously; otherwise, the operation will be failed. Let's explain with a time-series diagram:

After A1 updates the row, the database returns 1 means there is one row affected, i.e., it updated successfully. But, A2 gets 0; in other words, no row is updated.

To Sum Up

We know there are three approaches to solve the race condition from the above introduction. However, the cost of the version scheme is too high, and the performance of the transaction plus lock is too poor. We really don't need the heavy synchronization like the exclusive lock on an essential operation.

The atomic update is a better solution to apply to most use cases. Although we will no longer lose updates, negative values may still occur. Because the money is deducted 70 twice, the money is turned into -40 which is not allowed.

Total Solution

In addition to use the atomic update to modify the money, we still need some tricks to refrain from the negative values. My personal recommendation is to define the column, money, as an unsigned integer. Besides, use the transaction to protect the transfer. The final solution of this article is:

`start transaction`
A_owned = `select money from bank where name = A`
if A_owned >= 70:
    `update bank set money = money - 70 where name = A`
    `update bank set money = money + 70 where name = B`
`commit`

Change two updates to the atomic update, and use transaction to guard the whole operation. Even if if A_owned >= 70 cannot stop A2, A2 will still get the failure due to the unsigned column. The transaction will be failed, and B won't receive the money does not belong to him.