MySQL Subversion learning record: User Recharge function Realization (a): When the server stuck 7 seconds what happened 11

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