一次死锁的分析

现象

应用程序报错：

MySQLTImeoutException：Statement cacelled due to timeout or client request。

数据库状态

通过以下语句查看当前数据库事务状态

 

SELECT * FROM information_schema.INNODB_TRX;

发现出现等待的事务，具体如下：

具体再使用以下语句查看当前数据库状态

 

show engine innodb status 

看到核心的两段记录如下所示：

 

------------------

---TRANSACTION 3668254134, ACTIVE 2.345 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

LOCK BLOCKING MySQL thread id: 84382589 block 83894908

MySQL thread id 83894908, OS thread handle 0x7f9b4f683700, query id 45871363240 10.27.86.11 kdzs_web updating

DELETE FROM yfh_orderlist         WHERE         id=5209962 and taobao_id = 2857352293

------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 29771 page no 1239 n bits 120 index `PRIMARY` of table `kdzs_web`.`yfh_orderlist` trx id 3668254134 lock_mode X locks rec but not gap waiting

​

​

​

---TRANSACTION 3668252865, ACTIVE 6.665 sec starting index read

mysql tables in use 1, locked 1

LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)

LOCK BLOCKING MySQL thread id: 84382589 block 67142085

MySQL thread id 67142085, OS thread handle 0x7f9513efb700, query id 45871317351 10.27.86.11 kdzs_web updating

DELETE FROM yfh_orderlist         WHERE         id=5211156 and taobao_id = 2857352293

------- TRX HAS BEEN WAITING 7 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 29771 page no 1092 n bits 112 index `PRIMARY` of table `kdzs_web`.`yfh_orderlist` trx id 3668252865 lock_mode X locks rec but not gap waiting

以上两个事务分别在等待84382589这个Mysql线程。

查看对应的线程可以看到锁的行数和修改行数较多，可见是对应的线程的问题。

对应的线程不存在trx_started时间  2017/9/21  04:57:14。推断基本是晚上的计划任务删除数据的时候发生了全表锁导致的死锁。