Mysql select+update Processing Concurrent update problem solution sharing _mysql

Problem background:

Suppose the MySQL database has a membership table Vip_member (InnoDB table), the structure is as follows:

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 older than the current time, set Start_at to the current time, end_at the current time plus the number of months to buy

• Set end_at=end_at+ number of months if end_at equals or is later than the current time

• Active_status must be 1 (ie activated) after purchase

Problem Analysis:

For the above case, we typically select the record first, and then update Start_at and End_at according to the end_at of the record, following the pseudo code (for 1 months for a member with a UID of 1001):

Copy Code code as follows:

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 # Check UID to 1001 members
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 u id=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, it is clear that there is a "data coverage" problem (that is, a 1-month continuation, a 2-month extension, but may end up being only 2 months, rather than adding up 3 months).

Solution:

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

Copy Code code 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, the second scenario: a transaction, that is, a transaction that wraps the select+update operation above.

So is it all right to wrap up the business?

Obviously not. Because if two transactions are individually select to the same vip_member record, the same data overwrite problem occurs. Is there any way to solve it? Do you want to set the transaction isolation level to serializable, considering that performance is unrealistic.

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

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

Copy Code code 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 shared lock, but the row data with read shared locks must be updated or deleted after transaction a commits or roll back.

Copy Code code as follows:

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

If transaction a first obtains a write-share lock for 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, can only write a shared lock, the previous SQL rewritten as follows:

Copy Code code as follows:

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 for UPDATE # Check UID to 1001 members
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 u id=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 here Special reminder: Update/delete SQL as far as possible with the where conditions and in the where conditions set index filter conditions, otherwise lock the table, the performance can be imagined how bad.

C, third scenario: optimistic lock, CAS-like mechanism

The second type of locking scheme is a pessimistic locking mechanism. and select ... For the Update method is also less common, Lenovo to the CAS implementation of optimistic locking mechanism, so I think of a third solution: optimistic lock.

In particular, the Select SQL does not make any modifications, and then adds the end_at condition of the Select Vip_memer in the Where condition of the update SQL. As follows:

Copy Code code as follows:

Vipmember = SELECT * from Vip_member WHERE uid=1001 LIMIT 1 # Check UID to 1001 members
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 u Id=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

This can be based on the update return value to determine whether the update succeeded, if the return value of 0 indicates that there is a concurrent update, then just have to try again.

Scenario Comparison:

Each of the three options may differ in their pros and cons, just say my own opinion:

• The first scenario uses a more complex SQL solution, which is not conducive to maintenance, because the specific business is Leelawadee in SQL, and future changes to the business need not only read this SQL, but may also be modified to more complex SQL

• The second scheme writes exclusive locks that can solve the problem but is not commonly used

• The third option should be a more moderate solution, and even without business, is also my personal recommendation of the program

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

• If the response to read is very high, such as a securities trading system, it is appropriate to use optimistic locks, because the pessimistic lock will block the read

• If you read far more than you write, it is also appropriate to use optimistic locks, because the use of pessimistic locks will result in a large number of reading by a small number of write blocking

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