0

I want to write a stored procedure:

Involving the data update of the two tables, it belongs to a transaction, and it is hoped that there will be a rollback if there is a problem in any place in the transaction (a problem is an exception or an update failure).

Problems encountered now:

I use ROW_COUNT() to monitor the execution of the second UPDATE statement. But this operation has some shortcomings. First: There is a problem with the first UPDATE statement, and I will not roll back immediately. Second: If there is a problem in the first sentence and the second sentence is executed normally, no rollback will occur. Neither of these is what I hoped for.

Current own solution ideas:

First: do a monitor under each UPDATE statement, but the shortcomings are very obvious. If there are many operations involved in my stored procedure in the future, the above solution is not very suitable. Second: monitor the exceptions that occur in the transaction, and deal with them uniformly (DECLARE EXIRHANDLER FOR SQLEXCEPTION ROLLBACK;). But this method also has a problem. For example, the condition in the WHERE in my second UPDATE statement is not met, and the number of affected rows is 0, but no exception will be thrown.

Would you like to ask everyone, is there any solution?The following is the stored procedure I wrote

CREATE DEFINER=`dev`@`%` PROCEDURE `sp_buyScore`(IN userID int, IN moeny int)
BEGIN
    DECLARE original_balance int;   
    
    START TRANSACTION; 
    SELECT balance.`balance` FROM balance WHERE balance.`UserID` = userID INTO original_balance;
    IF original_balance-moeny < 0 THEN  
        signal sqlstate '45000' SET message_text = 'Insufficient balance, unable to purchase ';
        
    ELSE
        UPDATE balance SET balance= balance - moeny WHERE balance.`UserID` = userID;
    
        UPDATE userScore SET Score= Score + moeny * 10 WHERE userScore.`UserID` = userID;       
        IF ROW_COUNT() = 0 THEN 
            ROLLBACK; 
            signal sqlstate '45000' SET message_text = 'Update failed ';        
        END IF;
        
    END IF;
  COMMIT;
END
Nathan
  • 1
  • 1
    About: "First: There is a problem with the first UPDATE statement, and I will not roll back immediately", I think this can be solved by using [DECLARE EXIT HANDLER FOR SQLEXCEPTION](https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html) ? Or are you referring to problems which do not raise SQLEXCEPTIONs ? – Luuk Aug 07 '21 at 11:05
  • Yes, if SQLEXCEPTION is generated, just capture it directly. My main problem now is that it is judged that the corresponding record cannot be found to be changed due to the where of the UPDATE statement. This is an error for me. If such a problem occurs, I hope to roll back. For example, "UPDATE balance SET balance = balance + transfer_money WHERE balance.`UserID` = `dest_userID`;" where dest_userID is 101. But there is no record with UserID of 101 in the balance table – Nathan Aug 07 '21 at 11:53
  • Maybe use an `INSERT... ON DUPLICATE KEY UPDATE....`, see: https://stackoverflow.com/a/1218909/724039 Because the balance on a non-existing record should always be 0. – Luuk Aug 07 '21 at 16:20

0 Answers0