MySQL存储过程中START TRANSACTION无法正常工作问题排查

以下是针对你提供的存储过程中事务相关问题的排查点及修复方案：

核心问题分析 #

• CONTINUE HANDLER引发事务状态混乱
  当前定义的CONTINUE HANDLER FOR SQLWARNING ROLLBACK;和CONTINUE HANDLER FOR SQLEXCEPTION ROLLBACK;为CONTINUE类型，执行ROLLBACK后会继续运行后续代码。但ROLLBACK会终止当前事务，后续的COMMIT会因无活跃事务报错，同时循环持续执行导致逻辑混乱。

• DELETE语句无日期限定，存在误删风险
  动态生成的DELETE仅通过mt_pr关联删除，未限定ata_mmt_tran的日期范围，会删除所有匹配mt_pr的记录，包括非当前循环批次的数据。

• DDL操作隐式提交事务风险
  若bzt_ato_log_create存储过程包含CREATE TABLE这类DDL语句，MySQL会自动提交当前活跃事务。即使该调用在事务外执行，也需确保其不会干扰后续事务启动；若放在事务内，会直接导致后续事务操作失效。

• 用户变量残留问题
  @sql_insert是会话级用户变量，若某次循环赋值失败，下一次循环可能复用旧的SQL语句，引发逻辑错误。

修复后的代码示例 #

DECLARE v_done              INT DEFAULT 0;
DECLARE v_date_client_req   VARCHAR(8);
DECLARE v_log_table         VARCHAR(6);
DECLARE v_cnt               INT;
DECLARE v_table_name        VARCHAR(18);
DECLARE v_sql_insert        TEXT; -- 改用局部变量存储动态SQL

DECLARE v_cursor CURSOR FOR
SELECT DATE_FORMAT(A.date_client_req, '%Y%m%d') as date_client_req
       , count(*) AS cnt
  FROM   ( SELECT DATE_FORMAT(date_client_req, '%Y%m%d') AS date_client_req
           FROM   ata_mmt_tran
           WHERE  ata_id = p_ata_id
           AND    msg_status = '3'
           LIMIT  1000
        ) A
  GROUP BY DATE_FORMAT(A.date_client_req, '%Y%m%d')
  LIMIT 5;

-- 修改为EXIT HANDLER，遇到错误回滚并终止循环
DECLARE EXIT HANDLER FOR NOT FOUND SET v_done = 1;
DECLARE EXIT HANDLER FOR SQLWARNING 
BEGIN
    ROLLBACK;
    SET v_done = 1;
END;
DECLARE EXIT HANDLER FOR SQLEXCEPTION 
BEGIN
    ROLLBACK;
    SET v_done = 1;
END;

SET v_done=0;

OPEN v_cursor;
cursor_loop:LOOP
FETCH v_cursor INTO v_date_client_req, v_cnt;
IF v_done = 1 THEN
    LEAVE cursor_loop;
END IF;

IF v_date_client_req IS NULL THEN
    LEAVE cursor_loop;
END IF;

SET v_log_table = SUBSTRING(v_date_client_req,1,6);
CALL bzt_ato_log_create(v_log_table);

-- 拼接INSERT语句，使用局部变量
SET v_sql_insert = CONCAT("INSERT IGNORE INTO ata_mmt_log_", v_log_table , "
        SELECT mt_pr
            , mt_refkey
            , priority
            , date_client_req
            , subject
            , content
            , callback
            , msg_status
            , recipient_num
            , date_mt_sent
            , date_rslt
            , date_mt_report
            , report_code
            , rs_id
            , country_code
            , msg_type
            , crypto_yn
            , ata_id
            , reg_date
            , sysdate()
            , sender_key
            , template_code
            , response_method
            , message_group_code
            , attachment_type
            , attachment_name
            , attachment_url
            , img_url
            , img_link
            , etc_text_1
            , etc_text_2
            , etc_text_3
            , etc_num_1
            , etc_num_2
            , etc_num_3
            , etc_date_1
        FROM  ata_mmt_tran
        WHERE ata_id = ?
        AND   msg_status = '3'
        AND   DATE_FORMAT(date_client_req, '%Y%m%d') = ?
        LIMIT 2000");

SET @p_ata_id = p_ata_id;
SET @v_date_client_req = v_date_client_req;

START TRANSACTION;

-- 使用不同的PREPARE语句名，避免冲突
PREPARE stmt_insert FROM v_sql_insert;
EXECUTE stmt_insert USING @p_ata_id, @v_date_client_req;
DEALLOCATE PREPARE stmt_insert;

-- 拼接DELETE语句，增加日期限定条件
SET v_sql_insert = CONCAT("DELETE FROM ata_mmt_tran USING ata_mmt_tran A
INNER JOIN ata_mmt_log_", v_log_table ," B
ON A.mt_pr = B.mt_pr
WHERE DATE_FORMAT(A.date_client_req, '%Y%m%d') = '", v_date_client_req, "'");

PREPARE stmt_delete FROM v_sql_insert;
EXECUTE stmt_delete;
DEALLOCATE PREPARE stmt_delete;

COMMIT;

END LOOP cursor_loop;

CLOSE v_cursor;

SET v_done=0;

关键修改说明 #

1. 事务错误处理优化：将CONTINUE HANDLER改为EXIT HANDLER，错误发生时回滚并终止循环，避免事务状态混乱。
2. DELETE语句增加日期限定：添加日期匹配条件，仅删除当前批次的记录，防止误删其他数据。
3. 改用局部变量存储动态SQL：使用v_sql_insert局部变量替代会话级用户变量，避免残留值影响循环逻辑。
4. 区分PREPARE语句名：将两次PREPARE分别命名为stmt_insert和stmt_delete，避免重复标识符引发的潜在问题。

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