Select+update processing concurrency update problem solution in MySQL

This article mainly describes the MySQL select+update processing concurrency update problem solution sharing, the need for friends can refer to.

Issue Background

Suppose the MySQL database has a membership table Vip_member (InnoDB table) with the following structure:

When a member wants to renew a member (only 1 months, 3 months, or 6 months), the following business requirements must be met:

• If End_at is earlier than the current time, set Start_at to the current time, end_at to the current time plus the number of months to continue buying
• If End_at is equal to or later than the current time, set end_at=end_at+ number of months to continue buying
• Active_status must be 1 (i.e. activated) after purchase

Problem Analysis

For this case, we will generally select the record first, and then update Start_at and End_at according to the end_at of the recorded records, the Pseudocode as follows (for the UID is 1001 of the members for 1 months):

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 # Check UID 1001 member 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 els E:     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 code above, it is obvious that there is a "data overlay" problem (that is, a 1-month extension, a 2-month extension, but it may last only 2 months, not 3 months together).

Solution Solutions

A, the first scenario I think of is to synthesize select and update into a SQL

As follows:

UPDATE vip_member SET  start_at = case-    End_at < Now ()    ELSE start_at END,    end_at = case when End_at < now () then Date_add (now (), INTERVAL #duration: integer# MON  TH) ELSE Date_add (End_at, INTERVAL #duration: integer# MONTH) End, Active_status=1, Updated_at=now () WHERE uid= #uid: Bigint#limit 1;

B, the second scenario: a transaction that uses a transaction to wrap the above select+update operation

So is it all right to wrap up the business?

Obviously not. Because if two transactions are simultaneously select to the same vip_member record, then the same data coverage problem occurs. So what can be solved? Do you want to set the transaction isolation level to serializable, considering that performance is unrealistic.

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

Read shared locks are obtained by using SQL such as the following:

SELECT * from the parent WHERE NAME = ' Jones ' LOCK in SHARE MODE;

If transaction a obtains a read shared lock, then transaction B can still read the row data with the read shared lock, but it must wait for transaction a commit or roll back to update or delete the row data with the read shared lock.

SELECT Counter_field from Child_codes for UPDATE; UPDATE child_codes SET Counter_field = Counter_field + 1;

If transaction a first obtains a write-shared lock on a row, then transaction B must wait for transaction a commit or roll back before it can access the row data.

Obviously to solve the membership status update problem, can not read the shared lock, only write the shared lock, the preceding SQL is rewritten as follows:

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 for UPDATE # Check UID 1001 member if Vipmember.end_at < Now (): Up DATE vip_member SET Start_at=now (), End_at=date_add (now (), INTERVAL 1 MONTH), Active_status=1, Updated_at=now () WHERE UID  =1001else:  UPDATE vip_member SET end_at=date_add (end_at, INTERVAL 1 MONTH), Active_status=1, Updated_at=now () WHERE uid=1001

In addition here Special reminds: Update/delete SQL as far as possible with the Where condition and set the index filter in the Where condition, otherwise it will lock the table, performance can imagine how bad.

C, the third scenario: optimistic lock, class CAS mechanism

The second locking scheme is a pessimistic locking mechanism. and select ... The For Update method is also less common, associating with the optimistic locking mechanism implemented by CAS, so I thought of a third solution: optimistic lock.

In particular, it is quite simple, first select SQL does not make any changes, and then in the where condition of the update SQL, add the Vip_memer end_at condition of the Select. As follows:

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 # Check UID 1001 member Cur_end_at = vipmember.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

This can be based on update return value to determine whether the update is successful, if the return value is 0 indicates that there is a concurrent update, then only need to retry a bit better.

Scenario Comparison

There may be a divergence of views on the merits of the three options, but only my own:

• The first solution uses a more complex SQL to solve the problem, is not conducive to maintenance, because the specific business melted in SQL, and later to modify the business not only need to read this SQL, but also likely to be modified into more complex SQL
• The second scenario is to write an exclusive lock that solves the problem but is not commonly used
• The third scenario should be a more moderate solution, and even without the business, and I personally recommend the solution

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

• If the response to reading is very high, such as a securities trading system, then the optimistic lock is appropriate, because the pessimistic lock will block the read
• If read far more than write, then also suitable for optimistic lock, because with pessimistic lock will cause a lot of read by a small number of write block
• If write operations are frequent and conflict proportions are high, it is appropriate to write exclusive locks with pessimism

Select+update processing concurrency update problem solution in MySQL