关于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 ;

16 个解决方案

#1

自己顶一个先，怎么没人回答啊

#2

show engine innodb status

只里面得laster detected deadlock可以看到最近造成死锁的两条sql是什么

#3

引用 2 楼 rucypli 的回复:

show engine innodb status

只里面得laster detected deadlock可以看到最近造成死锁的两条sql是什么

show engine innodb status
 
没看到什么    看到都是空白的

#4

还是没人回答啊，分享一下经验也成啊。

#5

show innodb status;

贴出来看看。

#6

show innodb status;
没有东西，可能是数据库自己处理死锁了。     

错误日志中经常出现Deadlock found when trying to get lock; try restarting transaction，我担心会对数据库性能有影响，求高人指点

#7

这个存储过程看起来没什么问题，在各个事务里，对表的操作顺序都是先mt表,后cont表，不会因为多个线程同时调用这个存储过程而导致存储过程内部的死锁；
发生死锁的原因应该是别的线程某些事务先锁定cont，后锁定mt；或者多个线程循环锁定，导致类似于先锁定cont，后锁定mt的情况发生。
使用SHOW INNODB STATUS;命令后，在LASTEST DETECTED DEADLOCK节会看到最近发生的一个（最近的一个）死锁信息，在里面可以找到发生死锁的线程ID和SQL语句。
如果应用程序里对这两个表的修改不是很多的话，LZ可以把所有的修改语句列出来检查一下哪些语句会导致先锁定cont，后锁定mt。
在编写程序的时候，在各个事务内部，对表的修改顺序最好一致（比如对所有表进行编号，尽量先修改编号小或者大的表），这样可以避免大多数的死锁。
至于楼主说在发生死锁之后SHOW INNODB STATUS，却看不到死锁信息，我也不知是什么原因~

#8

按理说不会出现发生死锁后，SHOW INNODB STATUS里面没有死锁信息的情况。
如果不能查看死锁信息，很难确定是哪些语句导致死锁，LZ在发生死锁后再试一下，如果还看不到的话，QQ发给我，我帮你看看~

#9

谢谢各位 ，问题还未解决。暂时未发现对数据有什么影响。  会不会是数据库自己处理死锁了呢？

#10

有可能的吧

#11

我是linux服务器，在tomcat catalina.out日志中也发现如此问题，不知道是什么情况，截图如下。java.sql.SQLException: Deadlock found when trying to get lock; try restarting transaction at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2928) 
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
at com.mysql.jdbc.ServerPreparedStatement.serverExecut(ServerPreparedStatement.java:1124)
at com.mysql.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:676)

#12

由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。

举个例子:

假设有个表单products ，里面有id跟name二个栏位，id是主键。

例1: (明确指定主键，并且有此笔资料，row lock)

SELECT * FROM products WHERE id='3' FOR UPDATE;

SELECT * FROM products WHERE id='3' and type=1 FOR UPDATE;

 

例2: (明确指定主键，若查无此笔资料，无lock)

SELECT * FROM products WHERE id='-1' FOR UPDATE;

 

例2: (无主键，table lock)

SELECT * FROM products WHERE name='Mouse' FOR UPDATE;

 

例3: (主键不明确，table lock)

SELECT * FROM products WHERE id<>'3' FOR UPDATE;

 

例4: (主键不明确，table lock)

SELECT * FROM products WHERE id LIKE '3' FOR UPDATE;

 

注1: FOR UPDATE仅适用于InnoDB，且必须在交易区块(BEGIN/COMMIT)中才能生效。

注2: 要测试锁定的状况，可以利用MySQL的Command Mode ，开二个视窗来做测试。

 

在MySql 5.0中测试确实是这样的

另外：MyAsim 只支持表级锁，InnerDB支持行级锁

添加了(行级锁/表级锁)锁的数据不能被其它事务再锁定，也不被其它事务修改（修改、删除）

是表级锁时，不管是否查询到记录，都会锁定表

此外，如果A与B都对表id进行查询但查询不到记录，则A与B在查询上不会进行row锁，但A与B都会获取排它锁，此时A再插入一条记录的话则会因为B已经有锁而处于等待中，此时B再插入一条同样的数据则会抛出Deadlock found when trying to get lock; try restarting transaction然后释放锁，此时A就获得了锁而插入成功

#13

这个问题怎么避免呢

#14

求LZ回复最后的解决方法，谢谢，在线等

#15

我也碰到过类似问题，当时原因是update的时候，where后面的条件限制不严格，造成锁表，引起死锁提示。

#16

这个年轻人估计用的是myisam