定时执行的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 ;