Mysql-problems encountered during the interview, about PHP concurrency

A table in mysql stores the balance of a user. I am writing pseudocode below. $ Sqlselect * fromuserwherer balance & amp; gt; 10andid1; if the balance exists, perform logical processing and deduct the fee. If ($ SQL) {echo & #039; request its interface & #039; $ sqlupdat... A table in mysql stores the balance of a user. I am writing pseudocode below.

$ SQL = select * from user wherer balance> 10 and id = 1;
// If there is a balance for logical processing, deduct the fee at the end.
If ($ SQL ){
Echo 'request its interface ';
$ SQL = update user set balance = balance-10; // The Key is here. If the amount is reduced, it is equivalent to no balance, but due to concurrency, the second person has this condition when querying. How to avoid this situation

} Else {

Echo 'Insufficient balance ';
}

Reply content:

A table in mysql stores the balance of a user. I am writing pseudocode below.

$ SQL = select * from user wherer balance> 10 and id = 1;
// If there is a balance for logical processing, deduct the fee at the end.
If ($ SQL ){
Echo 'request its interface ';
$ SQL = update user set balance = balance-10; // The Key is here. If the amount is reduced, it is equivalent to no balance, but due to concurrency, the second person has this condition when querying. How to avoid this situation

} Else {

Echo 'Insufficient balance ';
}

Two solutions: "for update" pessimistic lock, or use the "version" optimistic lock, http://segmentfault.com/q/1010000002905539

$ SQL =select ... for updateFirst, try to lock

The "version number" mentioned above has a good optimistic lock and does not require database transaction support:
Just like preventing multiple users from editing, give the table a version number field.
Get the version number and balance when you get the data, compare the version number when you write the data, insert the same version number, and add the version number to 1.

SELECT balance,version AS last_version FROM user WHERE id=1 AND balance>10;UPDATE user SET balance=balance-10,version=version+1 WHERE id=1 AND version=last_version;

Alas, you have been pitted on the subject. Where is the PHP problem? This is obviously a database problem.

The correct method is to use stored procedures + transactions + locks.
It is not simple at all. When using stored procedures, you need to judge in sequence:
----> Whether the account exists
----> Insufficient balance
----> After the data is modified, you must verify whether the data is successfully modified and whether the modification is correct (some databases have bugs that may cause no modification or errors. Therefore, you must use SQL to calculate the Modification result for verification)

There are several alternative answers:
1. Use optimistic locks. This requires modifying the table and adding the last_version field, which is obviously unscientific.
2. It is not scientific to record the balance or where amount> 10 during update. What if the user's account was deleted before?
3. Use a queue. In the future, system bottlenecks will be concentrated here to see if the boss will kill you.

Large concurrency queue-able operation library.

The simplest way is to add a judgment in SQL
update user set amount=amount-10 where amount > 10
If the number of columns affected by execution is 0, an error is returned.

There are two ways
Method 1:
First query the balance, recorded as the initial balance.
Then
$ SQL = update user set balance = balance-10 where initial balance> 0
Then, you can determine the number of rows affected by SQL Execution to avoid the negative balance caused by concurrency.
Method 2:
$ SQL = update user set balance = case when balance> 10 then balance-10 else balance end where...
Then, check the number of affected rows.