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

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

 

3. DELIMITER $$

5. USE `sms`$$

7. DROP PROCEDURE IF EXISTS `proc_passout`$$

9. CREATE DEFINER=`root`@`%` PROCEDURE `proc_passout`()

10. BEGIN

11. DECLARE flag_lt INT(4);

12. DECLARE flag_dx INT(4);

13. DECLARE flag_yd INT(4);

14. DECLARE flag INT(4) DEFAULT 0;

15. DECLARE allflag INT(4);

16. DECLARE cont_flag INT(4);

17. DECLARE cont_deliverflag INT;

18. DECLARE i_oid VARCHAR(20) DEFAULT 0;

19. DECLARE tmpName VARCHAR(20) DEFAULT '' ;

20. DECLARE selectmoid CURSOR FOR

21. SELECT oid FROM cont WHERE DELIVERFLAG=5 ORDER BY oid ASC LIMIT 100;

22. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpName = NULL;

23. /*=========================*/

24. SET cont_deliverflag=9;

25. OPEN selectmoid;

26. FETCH selectmoid INTO i_oid;

27. /*处理黑名单*/

28. SET autocommit=0;

29. UPDATE mt a,blacklist b SET a.deliverflag=1,a.delivertime=LEFT(NOW()+0,14),a.msgid='blacklist'

30. WHERE a.phone=b.phone AND a.moid=i_oid;

31. COMMIT;

32. /*=========================*/

33. WHILE ( tmpName IS NOT NULL) DO

34. /*===========联通========*/

35. SELECT COUNT(*) INTO flag_lt FROM mt WHERE moid=i_oid

36. AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2));

37. /*==========电信==========*/

38. SELECT COUNT(*) INTO flag_dx FROM mt WHERE moid=i_oid

39. AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(3));

40. /*============总数=========*/

41. SELECT COUNT(*) INTO allflag FROM mt WHERE moid=i_oid;

42. IF flag_lt=allflag THEN #全是联通号

43. UPDATE cont SET channelid=1,deliverflag=cont_deliverflag WHERE oid=i_oid;

44. SET flag =1;

45. ELSEIF flag_dx=allflag THEN #全是电信号

46. UPDATE cont SET channelid=10,deliverflag=cont_deliverflag WHERE oid=i_oid ;

47. SET flag=1;

48. ELSE

49. /*处理联通号码*/

50. IF flag_lt>0 AND flag=0 THEN

51. SET @moid=seq('cont');

52. SET autocommit=0;

53. UPDATE mt SET moid=@moid WHERE moid=i_oid

54. AND SUBSTR(phone,1,3)

55. IN(SELECT subphone FROM telcom WHERE TYPE IN(2));

56. INSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)

57. SELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,1,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY

58. FROM cont WHERE oid=i_oid AND DELIVERFLAG=5;

59. COMMIT;

60. END IF;

61. /*处理CDMA号码*/

62. IF flag_dx>0 AND flag=0 THEN

63. SET @moid=seq('cont');

64. SET autocommit=0;

65. UPDATE mt SET moid=@moid WHERE moid=i_oid

66. AND SUBSTR(phone,1,3)

67. IN(SELECT subphone FROM telcom WHERE TYPE IN(3));

68. INSERT INTO cont(putintime,Oid, MsgType, MsgCont, ChannelId, ChName, ChPassword, SpNumber,DeliverFlag,PRIORITY)

69. SELECT PUTINTIME,@moid,MSGTYPE,MSGCONT,10,CHNAME,CHPASSWORD,SPNUMBER,cont_deliverflag,PRIORITY

70. FROM cont WHERE oid=i_oid AND DELIVERFLAG=5;

71. COMMIT;

72. END IF;

73. END IF;

74. SELECT COUNT(*) INTO flag_yd FROM mt WHERE moid=i_oid

75. AND SUBSTR(phone,1,3) IN(SELECT subphone FROM telcom WHERE TYPE IN(2,3));

76. /*处理移动号码*/

77. IF flag_yd=0 THEN

78. SET autocommit=0;

79. UPDATE cont SET DELIVERFLAG=cont_deliverflag WHERE cont.OID=i_oid AND DELIVERFLAG=5;

80. COMMIT;

81. END IF;

82. FETCH selectmoid INTO i_oid;

83. END WHILE;

84. CLOSE selectmoid;

86. END$$

88. DELIMITER ;