How to solve the problem of concurrent UPDATE using SELECT + UPDATE in MySQL

Problem Background:

Assume that the MySQL database has a vip_member (InnoDB table) member table. The structure is as follows:

When a member wants to buy a new member (only one month, three months, or six months can be bought), the following business requirements must be met:

• If end_at is earlier than the current time, start_at is set to the current time, And end_at is the current time plus the number of months for continued purchase.

• If end_at is equal to or later than the current time, set end_at = end_at + the number of months for continued purchases.

• Active_status must be 1 (activated) after the purchase)

Problem Analysis:

In this case, we usually SELECT to find this record, and then UPDATE start_at and end_at Based on the end_at of the record. The pseudocode is as follows (uid is 1001 of the member for 1 month):

Copy codeThe Code is as follows:
VipMember = SELECT * FROM vip_member WHERE uid = 1001 LIMIT 1 # Check members whose uid is 1001
If vipMember. end_at <NOW ():
UPDATE vip_member SET start_at = NOW (), end_at = DATE_ADD (NOW (), INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001
Else:
UPDATE vip_member SET end_at = DATE_ADD (end_at, INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001

If there are two threads executing the above Code at the same time, it is obvious that there is a "Data overwrite" Problem (that is, one is continued for 1 month, one is continued for 2 months, but it may only last for two months, instead of three months ).

Solution:

A. the first method I think of is to combine SELECT and UPDATE into an SQL statement, as shown below:

Copy codeThe Code is as follows:
UPDATE vip_member
SET
Start_at = CASE
WHEN end_at <NOW ()
Then now ()
ELSE start_at
END,
End_at = CASE
WHEN end_at <NOW ()
THEN DATE_ADD (NOW (), INTERVAL # duration: INTEGER # MONTH)
ELSE DATE_ADD (end_at, INTERVAL # duration: INTEGER # MONTH)
END,
Active_status = 1,
Updated_at = NOW ()
WHERE uid = # uid: BIGINT #
LIMIT 1;

So easy!

B. solution 2: transaction, that is, a transaction is used to wrap the SELECT + UPDATE operation above.

So is it all right to pack the transaction?

Apparently not. If both transactions SELECT the same vip_member record, data Overwrite will occur. So what can be done? Is it necessary to set the transaction isolation level to SERIALIZABLE, considering the unrealistic performance.

We know that InnoDB supports row locks. Check the MySQL official documentation (innodb locking reads) to learn that InnoDB can add two locks when reading row data: Read shared locks and write exclusive locks.

The read share lock is obtained through the following SQL:

Copy codeThe Code is as follows:
SELECT * FROM parent where name = 'Jones 'lock in share mode;

If transaction A obtains the read share lock first, then transaction B can still read the row data with the read share lock, however, you must wait for transaction A commit or roll back to update or delete the row data with the read share lock.

Copy codeThe Code is as follows:
SELECT counter_field FROM child_codes for update;
UPDATE child_codes SET counter_field = counter_field + 1;

If transaction A obtains the write share lock of A row, transaction B must wait for transaction A commit or roll back to access the row data.

Obviously, to solve the member status update problem, the Read/share lock cannot be applied, but only the write share lock can be applied. The preceding SQL statement is rewritten as follows:

Copy codeThe Code is as follows:
VipMember = SELECT * FROM vip_member WHERE uid = 1001 LIMIT 1 for update # Check members whose uid is 1001
If vipMember. end_at <NOW ():
UPDATE vip_member SET start_at = NOW (), end_at = DATE_ADD (NOW (), INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001
Else:
UPDATE vip_member SET end_at = DATE_ADD (end_at, INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001

In addition, we would like to remind you that the UPDATE/delete SQL statements should include the WHERE condition and set the index filtering conditions in the WHERE condition as much as possible. Otherwise, the table will be locked, and the performance may be much worse.

C. solution 3: Optimistic Locking and CAS-like Mechanism

The second Locking Scheme is a pessimistic locking mechanism. In addition, the SELECT... for update method is not very commonly used. I think of the Optimistic Locking Mechanism Implemented by CAS, So I think of the third solution: Optimistic Locking.

Specifically, it is quite simple. First, select SQL is not modified, and then add the vip_memer end_at condition selected in the WHERE condition of update SQL. As follows:

Copy codeThe Code is as follows:
VipMember = SELECT * FROM vip_member WHERE uid = 1001 LIMIT 1 # Check members whose uid is 1001
Cur_end_at = vipMember. end_at
If vipMember. end_at <NOW ():
UPDATE vip_member SET start_at = NOW (), end_at = DATE_ADD (NOW (), INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001 AND end_at = cur_end_at
Else:
UPDATE vip_member SET end_at = DATE_ADD (end_at, INTERVAL 1 MONTH), active_status = 1, updated_at = NOW () WHERE uid = 1001 AND end_at = cur_end_at

In this way, you can determine whether the UPDATE is successful Based on the UPDATE return value. If the return value is 0, it indicates that there is a concurrent UPDATE, so you only need to try again.

Comparison of solutions:

The advantages and disadvantages of each of the three solutions may be different. I just want to talk about my own views:

• The first solution uses a complicated SQL statement to solve the problem, which is not conducive to maintenance, because the specific business is integrated into SQL. In the future, you will not only need to read this SQL statement when modifying the business, it is also likely to be modified to a more complex SQL statement.

• The second scheme writes an exclusive lock, which can solve the problem but is not commonly used

• The third solution should be a moderate solution, and can even be done without transactions, which is also recommended by me.

In addition, the choice of optimistic locks and pessimistic locks is generally like this (refer to the second article at the end of the article ):

• Optimistic locks are suitable for high read response requirements, such as securities and exchange systems, because pessimistic locks block read operations.

• If reading far exceeds writing, optimistic locks are also suitable, because pessimistic locks can cause a large number of reads to be blocked by a small number of writes.

• If the write operation is frequent and the conflict ratio is high, it is suitable to use the pessimistic write exclusive lock.