MySQL存储过程游标执行异常时的事务回滚问题咨询

待分析的存储过程代码 #

DELIMITER ;;
CREATE DEFINER=root@localhost PROCEDURE ExecuteDataFill(IN p_internalCompanyId INT)
BEGIN
    DECLARE u_cursor CURSOR FOR
        SELECT name, address, area, pincode, city, state, state_code, country,
               pan_number, gst_number, bank_name, account_number, ifsc_code,
               phone_number, role
        FROM user_data;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        DECLARE p_name VARCHAR(225);
        DECLARE p_address VARCHAR(225);
        DECLARE p_area VARCHAR(50);
        DECLARE p_pincode VARCHAR(10);
        DECLARE p_city VARCHAR(20);
        DECLARE p_state VARCHAR(20);
        DECLARE p_state_code VARCHAR(10);
        DECLARE p_tin VARCHAR(10);
        DECLARE p_country VARCHAR(20);
        DECLARE p_pan_number VARCHAR(20);
        DECLARE p_gst_number VARCHAR(20);
        DECLARE p_bank_name VARCHAR(20);
        DECLARE p_account_number VARCHAR(20);
        DECLARE p_ifsc_code VARCHAR(20);
        DECLARE p_phone_number VARCHAR(20);
        DECLARE p_latest_org_external_id VARCHAR(50);
        DECLARE p_role VARCHAR(10);
        DECLARE done INT DEFAULT 0;
        DECLARE p_returned_sqlstate CHAR(5) DEFAULT '00000';
        DECLARE p_message_text TEXT DEFAULT '';
        DECLARE p_mysql_errno INT DEFAULT 0;

        GET DIAGNOSTICS CONDITION 1
            p_returned_sqlstate = RETURNED_SQLSTATE,
            p_message_text = MESSAGE_TEXT,
            p_mysql_errno = MYSQL_ERRNO;

        -- Log the error
        SELECT CONCAT('Error: ', p_returned_sqlstate, ', Message: ', p_message_text, ', MySQL Error: ', p_mysql_errno) AS Error_Message;

        -- Rollback the transaction
        ROLLBACK;

        -- Return the error details
        SELECT
            p_returned_sqlstate AS RETURNED_SQLSTATE,
            p_message_text AS MESSAGE_TEXT,
            p_mysql_errno AS MYSQL_ERRNO;
    END;

    -- Start the transaction
    START TRANSACTION;
    OPEN u_cursor;

    read_loop: LOOP
        -- Fetch the next row
FETCH u_cursor INTO p_name, p_address, p_area, p_pincode, p_city, p_state,p_state_code,p_country, p_pan_number, p_gst_number, p_bank_name, p_account_number,p_ifsc_code, p_phone_number, p_role;

        -- Exit the loop if no more rows
        IF done = 1 THEN
            LEAVE read_loop;
        END IF;

        BEGIN
            -- Insert into users table
            INSERT INTO users (username, name, whatsapp_number, created_by, updated_by, code, created_at, updated_at)
            VALUES (p_phone_number, p_name, p_phone_number, p_internalCompanyId, p_internalCompanyId, '', current_timestamp, current_timestamp);

            -- Insert into procedure_log
            INSERT INTO procedure_log (log_message) VALUES ('Inserted user: ' || p_name);
        END;

    END LOOP read_loop;

    CLOSE u_cursor;
    COMMIT;
END;;
DELIMITER ;

用户疑问与解答 #

该存储过程用于将user_data表数据批量插入到users和procedure_log表，期望任意插入失败时回滚所有已执行操作，以下是针对疑问的解答：

1. 当前异常处理逻辑中调用ROLLBACK的影响 #

当前存储过程的所有操作都包裹在一个显式事务中（从START TRANSACTION开始），当任意插入失败触发EXIT HANDLER时，调用ROLLBACK会撤销事务启动后所有已插入到users和procedure_log的数据，数据库会回到事务执行前的状态。

但要注意两个问题：

• 循环内的BEGIN/END块没有独立异常处理，所以任何插入失败都会直接触发外层的EXIT HANDLER；
• 存储过程缺少DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;，游标取完所有行后会抛出NOT FOUND异常，同样触发EXIT HANDLER导致全量回滚，这大概率是你之前没实现预期效果的核心原因。

2. 执行失败时的程序行为 #

当插入操作失败触发EXIT HANDLER FOR SQLEXCEPTION时，由于这是EXIT类型的处理器，程序会立即终止整个存储过程：不会继续处理游标后续的行，也不会重新读取所有行执行插入，直接跳转到异常处理块执行日志输出、回滚操作，最后返回错误信息。

内容的提问来源于stack exchange，提问作者Chayan Chakraborty