关于MYSQL 死锁的问题 Deadlock found when trying to get lock； try restarting

2021/12/26 19:37:18

编程Tag： Set try where 死锁 Cont OID flag moid trying

本文主要是介绍关于MYSQL 死锁的问题 Deadlock found when trying to get lock； try restarting，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

定时执行的MYSQL 存储过程报错：
Deadlock found when trying to get lock; try restarting transaction

这个错误是在MYSQL 的错误日志中看到的，不知道要从哪里开始入手解决这个问题。暂时没发现这个错误对业务逻辑有什么影响，求高手指点。贴出过程代码如下：

DELIMITER $$
USE `sms`$$
DROP PROCEDURE IF EXISTS `proc_passout`$$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_passout`()
BEGIN
DECLARE flag_lt INT(4);
DECLARE flag_dx INT(4);
DECLARE flag_yd INT(4);
DECLARE flag INT(4) DEFAULT 0;
DECLARE allflag INT(4);
DECLARE cont_flag INT(4);
DECLARE cont_deliverflag INT;
DECLARE i_oid VARCHAR(20) DEFAULT 0;
DECLARE tmpName VARCHAR(20) DEFAULT '' ;
DECLARE selectmoid CURSOR FOR
SELECT oid FROM cont WHERE DELIVERFLAG=5 ORDER BY oid ASC LIMIT 100;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = NULL;
/*=========================*/
SET cont_deliverflag=9;
OPEN selectmoid;
FETCH selectmoid INTO i_oid;
/*处理黑名单*/
SET autocommit=0;
UPDATE mt a,blacklist b SET a.deliverflag=1,a.delivertime=LEFT(NOW()+0,14),a.msgid='blacklist'
WHERE a.phone=b.phone AND a.moid=i_oid;
COMMIT;
/*=========================*/
WHILE ( tmpName IS NOT NULL) DO
/*===========联通========*/
SELECT COUNT(*) INTO flag_lt FROM mt WHERE moid=i_oid
AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2));
/*==========电信==========*/
SELECT COUNT(*) INTO flag_dx FROM mt WHERE moid=i_oid
AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(3));
/*============总数=========*/
SELECT COUNT(*) INTO allflag FROM mt WHERE moid=i_oid;
IF flag_lt=allflag THEN #全是联通号
UPDATE cont SET channelid=1,deliverflag=cont_deliverflag WHERE oid=i_oid;
SET flag =1;
ELSEIF flag_dx=allflag THEN #全是电信号
UPDATE cont SET channelid=10,deliverflag=cont_deliverflag WHERE oid=i_oid ;
SET flag=1;
ELSE
/*处理联通号码*/
IF flag_lt>0 AND flag=0 THEN
SET @moid=seq('cont');
SET autocommit=0;
UPDATE mt SET moid=@moid WHERE moid=i_oid
AND SUBSTR(phone,1,3)
IN(SELECT subphone FROM telcom WHERE TYPE IN(2));
INSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)
SELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,1,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY
FROM cont WHERE oid=i_oid AND DELIVERFLAG=5;
COMMIT;
END IF;
/*处理CDMA号码*/
IF flag_dx>0 AND flag=0 THEN
SET @moid=seq('cont');
SET autocommit=0;
UPDATE mt SET moid=@moid WHERE moid=i_oid
AND SUBSTR(phone,1,3)
IN(SELECT subphone FROM telcom WHERE TYPE IN(3));
INSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)
SELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,10,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY
FROM cont WHERE oid=i_oid AND DELIVERFLAG=5;
COMMIT;
END IF;
END IF;
SELECT COUNT(*) INTO flag_yd FROM mt WHERE moid=i_oid
AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2,3));
/*处理移动号码*/
IF flag_yd=0 THEN
SET autocommit=0;
UPDATE cont SET DELIVERFLAG=cont_deliverflag WHERE cont.OID=i_oid AND DELIVERFLAG=5;
COMMIT;
END IF;
FETCH selectmoid INTO i_oid;
END WHILE;
CLOSE selectmoid;
END$$
DELIMITER ;

这篇关于关于MYSQL 死锁的问题 Deadlock found when trying to get lock； try restarting的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！

关于MYSQL 死锁的问题 Deadlock found when trying to get lock； try restarting

相关编程文章