1. First create two tables, fill the Log table (User_balance_log), User Balance table (user_balance)
The table structure is as follows:
(1) User_balance:
(2) User_balance_log:
Note: The Log_type log Type 1 represents a 2 on behalf of the consumer 3 for the transfer
2. Write stored procedure to simulate user recharge
BEGIN
#Routine body goes here ...
DECLARE t_error int default 0;
DECLARE usercount int default 0;
Declare CONTINUE HANDLER for SQLEXCEPTION set t_error=1; #当发生错误时, set to 1
Start TRANSACTION;
#插入用户充值日志表
Insert into User_balance_log (User_id,log_type,log_des,log_value) values (_user_id,_log_type,_log_des,_log_value);
If Row_count () >0 then #插入成功
Select COUNT (*) into UserCount from user_balance where user_id = _user_id;
If UserCount > 0 Then #用户存在
Update user_balance set user_money=user_money+_log_value where user_id=_user_id;
Else
Insert into user_balance (User_id,user_money) values (_user_id,_log_value);
End If;
End If;
Select Sleep (7), ' End of Lag ';
If T_error=1 Then
ROLLBACK;
Else
COMMIT;
End If;
END
Note: parameters in _user_id int,in _log_type tinyint,in _log_des varchar ($), in _log_value decimal (10,2)
3. Execute the Stored procedure
Call Sp_add_usermoney (3,1, ' User recharge ', 10);
After the execution 5 times, the User Charge Log table (User_balance_log) is as follows,
The User Balance table (user_balance) is as follows: That is, the execution of five stored procedures, each recharge 10 yuan, accumulated 5 times is 50 yuan.
4. Analog Lag
Simulate the system to stutter for 7 seconds before commit, as follows:
Select Sleep (7), ' End of Lag ';
Executes a stored procedure once, then opens another session, executes
This results in the following:
(1) The user is charged on duty log table:
(2) User Balance table:
At this point, the user Balance table inserts two rows of data from the same user_id. Since the system is stuck, the operation to insert data in the stored procedure is not committed and the data is inserted in another session, which creates a problem.
MySQL Subversion learning record: User Recharge function Realization (a): When the server stuck 7 seconds what happened 11