Continue answering questions from planet Water friends. Mr. Shen, we have a business, the same user in the case of “query, logical calculation, deduction”, the balance may be inconsistent, may I ask what optimization method?

What is the business scenario for deductions?

In the process of purchasing goods, users need to query and modify the balance. The general business process is as follows:

_SELECT money FROM T_YUE WHERE uid= uid; Set the uid of the query; _ set the uid of the query; Let old_money= $100.

The second step, the business layer to implement business logic calculation, such as: (1) first query the purchase price of goods, such as 80 yuan; (2) Check whether the product has activities and activity discounts, such as 10% discount; (3) If the balance is enough, go down only when it is enough;

if(Old_money > {80 * 0.9)New_money = $old_money – 80 * 0.9 = 28

} else {

return “Not enough minerals”;

}

Third, modify the balance in the database. UPDATE t_yue SET money= newmoneyWHEREuid=new_money WHERE uid=newmoneyWHEREuid=uid;

In the case of low concurrency, there was no problem with this process. The original amount of 100 yuan was purchased with a 10% discount of 80 yuan (72 yuan), and the rest was 28 yuan.

** The same user, concurrent deduction may occur what problems? 支那

In a distributed environment, if there is a large amount of concurrency, data inconsistency may occur in the “query and modify” service. In the extreme case, an abnormal flow like this may occur:

Step 1: Service 1 and service 2 query the balance of 100 yuan.

_

_

Voiceover: These concurrent queries, done on different site instances/service instances, are certainly not resolved by an in-process mutex.

In step 2, service 1 and service 2 perform logical calculations at the same time to calculate the balance of their respective services. Assume that the balance of service 1 is 28 YUAN and that of service 2 is 38 yuan.

Step 3: Service 1 modifies the balance in the database to 28 YUAN.

Service 2 Modifies the balance in the database and sets it to 38 yuan.

At this time, there was an anomaly. The original amount was 100 yuan, 72 yuan was deducted from business 1, 62 yuan was deducted from business 2, and 38 yuan was left. _ Voiceover: _ Assume that business 1 writes back the balance first, and then business 2 writes back the balance.

Common solutions?

For this case, when the same user concurrently deducts money, there is a small probability that there will be exceptions, so you can carry out distributed lock mutual exclusion for each user. For example, only when you grab a key in Redis/ZK can you continue to operate, otherwise, the operation is prohibited. This pessimistic locking scheme works, but it introduces additional components (Redis/ZK) and reduces throughput. Is there an optimistic locking scheme for small probability inconsistencies?

Further analysis of concurrent deductions found that:

(1) Business 1 is written back when the old balance is 100, which is an initial state; The new balance is 28, which is an end state. In theory, the new balance should be written back to success only if the old balance is 100. When business 1 writes back, the old balance is 100 and the write back should be successful.

(2) Business 2 is written back when the old balance is 100, which is an initial state; The new balance is 28, which is an end state. In theory, the new balance should be written back to success only if the old balance is 100. In fact, the amount in the database has already become 28, so the concurrent write back of business 2 should not succeed.

How to implement optimistic locking at low cost?

Compare And set (CAS) is a common method to reduce read/write lock conflicts And ensure data consistency.

How will the business change at this point?

When CAS is used to solve the problem of data consistency in high concurrency, it only needs to compare the initial value during the set operation. If the initial value is changed, the set is not allowed to succeed.

T_yue SET money= newmoneyWHEREuid=new_money WHEREuid= newmoneyWHEREuid=uid; Upgrade to:

UPDATE t_yue SET money= newmoneyWHEREuid=new_money WHERE uid=newmoneyWHEREuid=uid AND money=$old_money_; __. _

When a concurrent operation occurs:

UPDATE T_YUE SET money=28 WHERE uid=$uid AND money=100;

UPDATE T_YUE SET money=38 WHERE uid=$uid AND money=100;

Only one of the two operations can be executed successfully at the same time.

How do you determine which concurrency succeeds and which fails?

Set operations don’t really matter whether they succeed or fail. Businesses can be determined using Affect Rows:

  • Write back successful, affect Rows is 1

  • Write back failed, affect Rows is 0

conclusion

Compare and Set (CAS) can be used to solve data consistency problems in high-concurrency query and modify scenarios. Corresponding to the business, that is, when set, plus the comparison of initial conditions can be. The optimization wasn’t difficult, changing only half a line of SQL, but it did solve the problem. But I hope you learned something **, thought is more important than conclusion **.

UPDATE T_YUE SET money=money-$diff;