记一次MySQL存储过程中显示使用事务，造成的锁等待排查

今天使用MySQL存储过程往innodb表t10中插入10w条数据，如下

BEGIN

DECLARE i INT;

SET i = 1;
while (i <= 100000) DO 
  INSERT INTO t10 (id, a, b) VALUES (i ,i ,i); ### 每次启动事务并提交
  SET i = i + 1;
END while;

END

调用存储过程，call idata(); 发现执行时间390s。其中的insert在每次执行都会启动事务并commit，同时会写redo log、同步binlog，刷盘10w次。为加快速度，选择批量方式，用显示声明事务的方式一次提交。如下，

BEGIN

DECLARE i INT;

START TRANSACTION;
SET i = 1;
while (i <= 100000) DO 
  INSERT INTO t10 (id, a, b) VALUES (i ,i ,i);
  SET i = i + 1;
END while;
COMMIT;

END

在10w条数据未删除的情况下，调用了修改后的存储过程。因为主键id，报错：SQL错误（1062）：Duplicate entry '1' for key 't10.PRIMARY'。在此情况下，使用另外的数据库会话执行t10表数据的删除，发现锁等待超时，如下：

 

看来出现了锁未释放的情形。按如下查找原因，

1. 出现锁未释放，大概率存在未提交的事务。

SELECT * FROM information_schema.innodb_trx;

 2. 再执行delete from t10; 在锁等待期间，查找performance_schema.data_locks和performance_schema.data_lock_waits确认

 

 

为啥会出现事务未提交的情形呢？期间只是执行了一次修改后的存储过程。再检查存储过程，发现报异常后会直接退出，未执行commit。。。

改进存储过程如下，

BEGIN

DECLARE i INT;
DECLARE _err INT DEFAULT 0;
declare continue handler for sqlexception, sqlwarning, not FOUND 
  begin 
      set _err=1;
  END;

SET i = 1;
START TRANSACTION;
while (i <= 100000) DO 
  INSERT INTO t10 (id, a, b) VALUES (i ,i ,i);
  SET i = i + 1;
END while;
if _err=1 then
  rollback;
ELSE 
  COMMIT;
end if;

END

加上异常处理，且发生异常时回滚，正常则提交，执行ok。