Suppose there is a table,
Both A and B have 100, and A will transfer money to B twice.
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:
- Remained money is incorrect after updating.
- After remitting, the balance comes to a negative value.
Let's watch these two issues separately.
A's balance will become 30. This is the classic race condition. How to solve it?
In order to solve the race condition, there are three ways:
- Atomic Update
- Transaction plus Lock
- Version Scheme
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.
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
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.
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.
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`
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.
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.
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 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.