mysql索引死锁分析

1.表结构

CREATE TABLE `dept_attr` (
  `id` int NOT NULL COMMENT '主键',
  `model_key` varchar(255),
  `model_name` varchar(255),
  PRIMARY KEY (`id`),
  KEY `modle_index` (`model_name`)
) ENGINE=InnoDB;
INSERT INTO `dept_attr1` (`id`, `model_key`, `model_name`) VALUES (1, "a", "22");

2.执行sql

sql1: UPDATE dept_attr SET model_name='11' WHERE  model_name = '22';

sql2: UPDATE dept_attr SET model_name='22' WHERE id = 1;

3.异常:

Exception in thread "Thread-16" org.springframework.dao.DeadlockLoserDataAccessException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
### The error may exist in com/example/demo/generator/mapper/DeptAttrMapper.java (best guess)
### The error may involve com.example.demo.generator.mapper.DeptAttrMapper.update-Inline
### The error occurred while setting parameters
### SQL: UPDATE dept_attr  SET model_name=?   WHERE  model_name = ?
### 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
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:271)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
	at com.sun.proxy.$Proxy63.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288)
	at com.baomidou.mybatisplus.core.override.PageMapperMethod.execute(PageMapperMethod.java:73)
	at com.baomidou.mybatisplus.core.override.PageMapperProxy.invoke(PageMapperProxy.java:64)
	at com.sun.proxy.$Proxy64.update(Unknown Source)
	at com.baomidou.mybatisplus.extension.service.impl.ServiceImpl.update(ServiceImpl.java:238)
	at com.example.demo.generator.service.impl.DeptAttrServiceImpl.modifyByIndex(DeptAttrServiceImpl.java:33)
	at com.example.demo.generator.service.impl.DeptAttrServiceImpl$$FastClassBySpringCGLIB$$f94cf99f.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
	at com.example.demo.generator.service.impl.DeptAttrServiceImpl$$EnhancerBySpringCGLIB$$b2bf6886.modifyByIndex(<generated>)
	at com.example.demo.controller.MysqlTestController$Test1.run(MysqlTestController.java:42)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:194)
	at sun.reflect.GeneratedMethodAccessor54.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
	... 13 more

4.原因: 

        1、InnoDB行锁是通过给索引项加锁来实现的，这一点mysql和oracle不同；

        2、InnoDB这种行级锁决定，只有通过索引条件来检索数据，才能使用行级锁，否则，直接使用表级锁。

        3、mysql的行锁是针对索引假的锁，不是针对记录

        4.、sql1获取普通索引锁，然后获取主键索引。 sql2获取主键索引，再获取普通索引锁。形成死锁。

5.发现分析和解决：

        执行 show engine innodb status;

2021-10-28 14:44:15 0x19c8
*** (1) TRANSACTION:
TRANSACTION 1876402, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s)
MySQL thread id 38, OS thread handle 35668, query id 3567934 localhost 127.0.0.1 root Searching rows for update
UPDATE dept_attr  SET model_name='11'   WHERE  model_name IN ('11','22')

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 111 page no 5 n bits 72 index modle_index of table `temp`.`dept_attr` trx id 1876402 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 3131; asc 11;;
 1: len 4; hex 80000001; asc     ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 111 page no 4 n bits 72 index PRIMARY of table `temp`.`dept_attr` trx id 1876402 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000001ca1b1; asc       ;;
 2: len 7; hex 010000017f2d54; asc      -T;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 2; hex 3132; asc 12;;
 6: len 2; hex 3232; asc 22;;
 7: len 4; hex 80000001; asc     ;;
 8: len 1; hex 32; asc 2;;
 9: len 5; hex 99aabb12f3; asc      ;;
 10: len 1; hex 31; asc 1;;
 11: len 1; hex 81; asc  ;;
 12: len 5; hex 99aa971300; asc      ;;
 13: len 1; hex 31; asc 1;;


*** (2) TRANSACTION:
TRANSACTION 1876401, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 37, OS thread handle 29700, query id 3567933 localhost 127.0.0.1 root updating
UPDATE dept_attr  SET dept_id=1,


model_name='22'   WHERE  id IN (1,2)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 111 page no 4 n bits 72 index PRIMARY of table `temp`.`dept_attr` trx id 1876401 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 0000001ca1b1; asc       ;;
 2: len 7; hex 010000017f2d54; asc      -T;;
 3: len 4; hex 80000001; asc     ;;
 4: len 4; hex 80000001; asc     ;;
 5: len 2; hex 3132; asc 12;;
 6: len 2; hex 3232; asc 22;;
 7: len 4; hex 80000001; asc     ;;
 8: len 1; hex 32; asc 2;;
 9: len 5; hex 99aabb12f3; asc      ;;
 10: len 1; hex 31; asc 1;;
 11: len 1; hex 81; asc  ;;
 12: len 5; hex 99aa971300; asc      ;;
 13: len 1; hex 31; asc 1;;


*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 111 page no 5 n bits 72 index modle_index of table `temp`.`dept_attr` trx id 1876401 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 3131; asc 11;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

6.解决死锁的方法

        超时等待：即当两个事务互相等待时，当一个事务等待时间超过设置的阈值时，就将其回滚，另外事务继续进行。（缺点：如果回滚的事务更新了很多行，占用了较多的undo log，那么在回滚的时候花费的时间比另外一个正常执行的事务花费的时间可能还要多，就不太合适）；
        wait-for graph（等待图）：死锁碰撞检测，是一种较为主动的死锁检测机制，要求数据库保存锁的信息链表和事务等待链表两部分信息，通过这两个部分信息构造出一张图，在每个事务请求锁并发生等待时都会判断是否存在回路，如果在图中检测到回路，就表明有死锁产生，这时候InnoDB存储引擎会选择回滚undo量最小的事务。

        再写sql的时候通过主键更新数据（通过索引查询出主键再更新）