In MySQL, SELECT + UPDATE is concurrently updated. _ MySQL

Concurrent UPDATE of 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, set start_at to the current time, and end_at to 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):

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:

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 1 MONTH)            ELSE DATE_ADD(end_at, INTERVAL 1 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:

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.

The exclusive write lock is obtained through SELECT... for update:

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:

VipMember = SELECT * FROM vip_member WHERE uid = 1001 LIMIT 1 for update # Query members with uid 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

Note:UPDATE/delete SQL should include the WHERE condition and set the index filtering condition in the WHERE condition whenever possible. Otherwise, the table will be locked.Performance can be imagined.

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:

VipMember = SELECT * FROM vip_member WHERE uid = 1001 LIMIT 1 # Check member cur_end_at = vipMember whose uid is 1001. end_atif 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_atelse: 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 incorporated into the SQL statement. when modifying the business in the future, you not only need to read this SQL statement, it is also likely to be modified to a more complex SQL statement.

• Solution 2: write exclusive locks, which can solve the problem but are not commonly used

• The third solution should be a moderate solution, and even 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 ):

• If the read response requirements are very high, such as the Securities and Exchange System, optimistic locks are suitable, because pessimistic locks block reading.

• 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.
  

References:

Innodb locking reads

Application of MVCC in distributed systems