mysql8 数据死锁

可直接在mysql命令行执行：show engine innodb status\G; 

 查看造成死锁的sql语句，分析索引情况，然后优化sql 

 然后 

 show processlist; 

 kill processid; 

 另外可以打开慢查询日志，linux下打开需在my.cnf的[mysqld]里面加上以下内容： 

 long_query_time = 2   
log-slow-queries = /usr/local/mysql/mysql-slow.log

最近在项目开发过程中，碰到了数据库的死锁问题，在解决问题的过程中，加深了对MySQLInnoDB引擎锁机制的理解。

 我们使用Show innodb status检查引擎状态时，发现了死锁问题：

 *** (1) TRANSACTION:
 TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS threadid 278546 starting index read
 mysql tables in use 1, locked 1
 LOCK WAIT 3 lock struct(s), heap size 320
 MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rowsfor update
 update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () whereSTATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL30 minute)
 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
 RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY`of table `dcnet_db/TSK_TASK` trx id 0 677833455 lock_mode X locksrec but not gap waiting
 Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compactformat; info bits 0
 0: len 8; hex 800000000097629c; asc  
  
    
  b ;; 1: len 6; hex00002866eaee; asc  
  (f  
  ;; 2: len7; hex 00000d40040110; asc  
    
 @  
  ;; 3: len 8; hex 80000000000050b2;asc  
    
    
  P ;; 4:len 8; hex 800000000000502a; asc  
   
    
  P*;; 5: len 8; hex8000000000005426; asc  
    
   
  T&;; 6: len 8; hex800012412c66d29c; asc  
    
 A,f  
  ;; 7: len 23; hex75706c6f6164666972652e63 
 6f6d2f6 8616e642e706870; asc xxx.com/;; 8:len 8; hex 800000000000042b; asc  
   
    
    
  +;; 9: len4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24;asc  
    
    
 N$;; 
 

 *** (2) TRANSACTION: 
 
 TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS threadid 344086 updating or deleting, thread declared inside InnoDB499 
 
 mysql tables in use 1, locked 1 
 
 3 lock struct(s), heap size 320, undo log entries 1 
 
 MySQL thread id 84, query id 162348739 dcnet03 dcnetUpdating 
 
 update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where ID in(9921180) 
 
 *** (2) HOLDS THE LOCK(S): 
 
 RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY`of table `dcnet_db/TSK_TASK` trx id 0 677833454 lock_mode X locksrec but not gap 
 
 Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compactformat; info bits 0 
 
 0: len 8; hex 800000000097629c; asc  
   
    
  b ;; 1: len 6; hex00002866eaee; asc  
  (f  
  ;; 2: len7; hex 00000d40040110; asc  
    
 @  
  ;; 3: len 8; hex 80000000000050b2;asc  
    
    
  P ;; 4:len 8; hex 800000000000502a; asc  
   
    
  P*;; 5: len 8; hex8000000000005426; asc  
    
   
  T&;; 6: len 8; hex800012412c66d29c; asc  
    
 A,f  
  ;; 7: len 23; hex75706c6f6164666972652e63 
 6f6d2f6 8616e642e706870; ascuploadfire.com/hand.php;; 8: len 8; hex 800000000000042b;asc  
    
    
   
  +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len8; hex 8000000000004e24; asc  
    
   
  N$;; 
 

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: 
 
 RECORD LOCKS space id 0 page no 843102 n bits 600 index`KEY_TSKTASK_MONTIME2` of table `dcnet_db/TSK_TASK` trx id 0677833454 lock_mode X locks rec but not gap waiting 
 
 Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compactformat; info bits 0 
 
 0: len 8; hex 8000000000000425; asc  
   
    
    
  %;; 1: len8; hex 800012412c66d29c; asc  
    
 A,f  
  ;; 2: len 8; hex 800000000097629c;asc  
    
    
  b;; 
 

 *** WE ROLL BACK TRANSACTION (1) 
 

 该死锁问题涉及TSK_TASK表，该表用于保存系统监测任务，相关字段及索引如 下： 
 


 ID：主键； 
 

 MON_TIME：监测时间； 
 

 STATUS_ID：任务状态； 
 

 索 引：KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。 
 


 经分析，涉及的两条语句应该不会 涉及相同的TSK_TASK记录，那为什么会造成死锁呢？ 
 

 查询MySQL官网文档，发现这跟MySQL的索引机制有关。MySQL的InnoDB引擎是行级锁，我原来的理解是直接对记录进行锁定，实际上不是这样的，其要点如下： 
 


 不是对记录进行锁定，而是对索引 进行锁定； 
 

 在UPDATE、DELETE操作时，MySQL不仅锁定WHERE条件扫描过的所有索引记录，而且会锁定相邻的键值，即所谓的next-key locking； 
 
 如语句UPDATE TSK_TASK SET UPDATE_TIME = NOW() WHERE ID>10000会锁定所有主键大于等于1000的所有记录，在该语句完成之前，你就不能对主键等于10000的记录进行操作； 
 

 当 非簇索引(non-cluster index)记录被锁定时，相关的簇索引(clusterindex)记录也需要被锁定才能完成相应的操作。 
 

 再 分析一下发生问题的两条SQL语句，就不难找到问题所在了： 
 

 当“update TSK_TASK set STATUS_ID=1064,UPDATE_TIME=now () whereSTATUS_ID=1061 and MON_TIME<date_sub(now(), INTERVAL30 minute)”执行时，MySQL会使用KEY_TSKTASK_MONTIME2索引，因此首先锁定相关的索引记录，因为KEY_TSKTASK_MONTIME2是非簇索引，为执行该语句，MySQL还会锁定簇索引（主键索引）。 
 

 假设“update TSK_TASK set STATUS_ID=1067,UPDATE_TIME=now () where IDin (9921180)”几乎同时执行时，本语句首先锁定簇索引(主键)，由于需要更新STATUS_ID的值，所以还需要锁定KEY_TSKTASK_MONTIME2的某些索引记录。 
 

 这样第一条语句锁定了KEY_TSKTASK_MONTIME2的记录，等待主键索引，而第二条语句则锁定了主键索引记录，而等待KEY_TSKTASK_MONTIME2的记录，这样死锁就产生了。 
 

 我们通过拆分 第一条语句解决了死锁问题：即先查出符合条件的ID：select ID from TSK_TASK whereSTATUS_ID=1061 and MON_TIME < date_sub(now(),INTERVAL 30 minute)；然后再更新状态：update TSK_TASK set STATUS_ID=1064where ID in (….) 
 

 这样就不会产生索引的竞争问题，死锁问题就 解决了