MySql教程

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

本文主要是介绍记一次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。

 

这篇关于记一次MySQL存储过程中显示使用事务,造成的锁等待排查的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!