记一次生产Mysql死锁_nested exception is com.mysql.cj.jdbc.exceptions.m

系统死锁日志：

昨晚，生产监控发生告警，发生了死锁。错误日志是：

### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may involve com.zz.saas.shoplus.product.dao.ProductImgMapper.deleteByExample-Inline
### The error occurred while setting parameters
### SQL: DELETE FROM t_product_img  WHERE (       (  product_id = ?                                                and id not in               (                 ?               ,                 ?               ,                 ?               ,                 ?               ) ) )
### Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
com.sun.proxy.$Proxy131.delete(Unknown Source)
org.mybatis.spring.SqlSessionTemplate.delete(SqlSessionTemplate.java:310)
org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:68)
org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
com.sun.proxy.$Proxy164.deleteByExample(Unknown Source)

获取引擎死锁信息：

让运维执行以下命令

show engine innodb status;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-01-27 10:10:26 0x2abc393d2700
*** (1) TRANSACTION:
TRANSACTION 4510490, ACTIVE 1 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 509, OS thread handle 46988898408192, query id 531410 172.**.*.2** shoplus_p_rw updating
DELETE FROM t_product_img  WHERE (       (  product_id = 108506
 
 
 
              and id not in
              (
                963382
              ,
                963383
              ,
                963384
              ,
                963385
              ) ) )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 12021 n bits 408 index idx_pId of table `saas_shoplus_product`.`t_product_img` trx id 4510490 lock_mode X waiting
Record lock, heap no 152 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000001a7da; asc         ;;
 1: len 8; hex 80000000000eb322; asc        ";;
*** (2) TRANSACTION:
TRANSACTION 4510489, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
20 lock struct(s), heap size 1136, 26 row lock(s), undo log entries 17
MySQL thread id 533, OS thread handle 46987902527232, query id 531473 172.**.*.2** shoplus_p_rw update
INSERT INTO t_product_img  ( shop_id,product_id,alt,title,position,img_url,create_time,update_time )  VALUES   ( 1251,108505,null,null,3,'http://icdn.pearlgo.com/ec/dynamicRes-2/002/image/202009221600757986828505797499723730944.jpg','2021-01-27 18:10:25.975','2021-01-27 18:10:25.975' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 114 page no 12021 n bits 400 index idx_pId of table `saas_shoplus_product`.`t_product_img` trx id 4510489 lock_mode X
Record lock, heap no 148 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
 0: len 8; hex 800000000001a7d9; asc         ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 114 page no 12021 n bits 408 index idx_pId of table `saas_shoplus_product`.`t_product_img` trx id 4510489 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 152 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 800000000001a7da; asc         ;;
 1: len 8; hex 80000000000eb322; asc        ";;
 
*** WE ROLL BACK TRANSACTION (1)

引擎日志分析：

从上面信息看到，有事务（1）和事务（2）.

其中事务（1）：

①mysql tables in use 1, locked 1 用了一个表，并且锁了一个表。

②死锁的sql是DELETE FROM t_product_img WHERE product_id = 108506。。。。

③WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS。。。。trx id 4510490 lock_mode X waiting

需要一个X排他锁

其中事务（2）：

①HOLDS THE LOCK(S):

RECORD LOCKS 。。。。 index idx_pId of table `saas_shoplus_product`.`t_product_img` trx id 4510489 lock_mode X

持有排他锁

②WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS 。。。。 index idx_pId of table `saas_shoplus_product`.`t_product_img` trx id 4510489 lock_mode X locks gap before rec insert intention waiting

需要一个插入意向锁

日志分析总结：

事务1，持有间隙锁，需要获取排他锁

事务2，持有排他锁，需要获取插入意向锁。

间隙锁和插入意向锁互斥， 两个事务请求对方的锁且保持自己拥有的锁，最后导致死锁。

场景复现：

第一步（A事务）：

START TRANSACTION; 
DELETE FROM t_product_img WHERE ( ( product_id = 270 and id not in (1686,1687) ) );

第二步（B事务）：

START TRANSACTION; 
DELETE FROM t_product_img WHERE ( ( product_id = 271 and id not in (1696,1697) ));

第三步（A事务）：

INSERT INTO `saas_shoplus_product`.`t_product_img` (`shop_id`, `product_id`, `alt`, `title`, `position`, `img_url`, `create_time`, `update_time`) VALUES ( 148, 271, NULL, NULL, 2, 'https://cdn.shopify.com/s/files/1/0080/9458/0854/products/1559818291289-SokBp.jpg?v=1605782348', '2020-11-20 14:23:19', '2020-11-20 14:23:19');

最后B事务直接发生死锁，错误信息：

DELETE FROM t_product_img WHERE ( ( product_id = 271 
and id not in (1696,1697) 
				))
> 1213 - Deadlock found when trying to get lock; try restarting transaction
> 时间: 4.675s

最后解决方法：

①避免使用not in ,导致锁表或者锁索引树

②降低当前方法的隔离级别，由RR可重复读降到RC读已提交，避免间隙锁

最后补充：

①productId是普通索引,在RR可重复读的隔离级别下,根据productId删除会发生间隙锁（避免幻读）.

像上面productId=108506, 那么锁住的范围是[108505,108507)

如果另外一个事务插入数据，则需要获取插入意向锁。另外一个事务这时候插入108505，会阻塞。

②在删除后插入的场景特别注意死锁。