mysql 如何让update 走主键索引 mysql主键更新死锁分析

前言：
最近项目上压测出现了数据库死锁情况，经过一番排查，最终定位在updateData方法上

分析该方法：

@Transactional(propagation = Propagation.REQUIRES_NEW)
public int udpateData(DataTrace trace){
	//根据索引更新数据，该子方法也加了@Transactional注解，使用默认事务机制:required
	updateOtherByIndex(trac);
	//根据主键更新数据
	updateOtherByPrimary(trac);
}

1.该方法执行了两个更新sql，一个根据索引，一个根据主键
2.外层调用udpateData方法，存在这种情况：首先异步调用，然后马上同步调用

分析死锁原因：

1.首先udpateData方法存在事务嵌套，子方法updateOtherByIndex使用默认事务机制required，会将已存在的事务加入当前事务中，所以两个更新sql是在一个事务中
2.既然没有开启一个新事务，则两个更新sql在一个事务中，并发调用udpateData方法就可能会出现问题(而且也是巧，第一个请求不会调用子方法updateOtherByIndex)：
第一个进来事务会执行updateOtherByPrimary方法，获取主键锁
第二个进行事务会执行updateOtherByIndex方法，获取索引锁，然后执行updateOtherByPrimary方法，获取主键锁
这种循环情况下在压测环境很容易出现死锁了…

虽然原因找到了，不过为了更好地理解和加深学习，我本地做了一个死锁测试：

一、前置条件：

1.创建表：

CREATE TABLE `test_deadlock_table` (
  `primary_no` char(16) NOT NULL COMMENT '主键号',
  `index_no` char(16) NOT NULL COMMENT '索引号',
  `status` tinyint(4) unsigned NOT NULL COMMENT '状态',
  PRIMARY KEY (`primary_no`),
  UNIQUE KEY `uk_index_no` (`index_no`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='死锁测试表';

2.插入一条数据：

INSERT INTO `test_deadlock_table` VALUES ('test_primary', 'test_index', '1');

二、模拟死锁：

1.开启事务A：

start transaction;
begin;
## 步骤1.根据主键字段primary_no更新status字段
UPDATE test_deadlock_table SET STATUS=2 WHERE primary_no='test_primary';
## 步骤2.休息十秒
select sleep(10);
## 步骤3.根据索引字段index_no更新status字段
UPDATE test_deadlock_table SET STATUS=4 WHERE index_no='test_index';

截图如下：

2.开启事务A后，立刻开启事务B：

start transaction;
begin;
## 根据索引字段index_no更新status字段
UPDATE test_deadlock_table SET STATUS=3 WHERE index_no='test_index';

截图如下：

三、结果：

1.事务A休息完十秒后，提交成功。

截图：

2.事务B出现死锁并回滚。

截图：

四、分析：

问题1. 为什么会发生死锁？

1. 首先因为表结构是主键和唯一索引，所以可以确认使用的是行锁，按照行锁逻辑进行判断
2. 执行事务A的步骤1时，会获取主键锁
3. 立刻执行事务B时，会获取index_no字段的索引锁，然后获取主键锁，因为事务A的步骤1已经获取主键锁，所以事务B会等起
4. 事务A等待十秒后，执行步骤3，会尝试获取index_no字段的索引，但是index_no字段的索引已经被事务B获取了，这时，事务B由于无法获取主键锁但又无法释放索引锁，
   事务B和事务A就产生循环了，死锁发生。

问题2. 为什么发生死锁后，滚事务B为什么会回滚？

参考mysql5.6官方文档(我测试使用的mysql版本5.6.34)：

1. 文中提到：当启用死锁检测时（默认），InnoDB会自动检测事务死锁并回滚一个或多个事务来打破死锁。InnoDB会尝试选择小型事务进行回滚，其中事务的大小由插入，更新或删除的行数决定。
2. 因为事务B只有一条更新sql，事务A有两条更新sql，所以mysql选择回滚事务B
3. 另外默认情况下，InnoDB存储引擎不会回滚超时引发的异常，除死锁外

五、记录下mysql-show engine innodb status结果日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-01-04 14:45:55 7fec4f1c7700
*** (1) TRANSACTION:
TRANSACTION 115800640, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 661131, OS thread handle 0x7feca612c700, query id 219167220 10.10.1.1 testdat updating
UPDATE test_deadlock_table SET STATUS=7 WHERE index_no='test_index'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
       //这里在等待主键锁
RECORD LOCKS space id 6598 page no 3 n bits 72 index `PRIMARY` of table `testdata`.`test_deadlock_table` trx id 115800640 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 12; hex 746573745f7072696d617279; asc test_primary;;
 1: len 6; hex 000006e6fa3c; asc      <;;
 2: len 7; hex 75000002f614cd; asc u      ;;
 3: len 10; hex 746573745f696e646578; asc test_index;;
 4: len 1; hex 86; asc  ;;

*** (2) TRANSACTION:
TRANSACTION 115800636, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 661122, OS thread handle 0x7fec4f1c7700, query id 219167278 10.10.1.1 testdata updating
UPDATE test_deadlock_table SET STATUS=7 WHERE index_no='test_index'
        //这里持有主键锁
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6598 page no 3 n bits 72 index `PRIMARY` of table `test`.`test_deadlock_table` trx id 115800636 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 12; hex 746573745f7072696d617279; asc test_primary;;
 1: len 6; hex 000006e6fa3c; asc      <;;
 2: len 7; hex 75000002f614cd; asc u      ;;
 3: len 10; hex 746573745f696e646578; asc test_index;;
 4: len 1; hex 86; asc  ;;
 
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
 //这里在等待索引锁
RECORD LOCKS space id 6598 page no 4 n bits 72 index `index_index_no` of table `test`.`test_deadlock_table` trx id 115800636 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 10; hex 746573745f696e646578; asc test_index;;
 1: len 12; hex 746573745f7072696d617279; asc test_primary;;
      //回滚事务B
*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------

扩展：

1. 锁等待超时导致的死锁：
   官方文档提到：
   如果InnoDB Monitor输出的LATEST DETECTED DEADLOCK部分包含一条消息，指出TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH, WE WILL ROLL BACK FOLLOWING TRANSACTION，这表明等待中的事务数量已达到200的限制。超过200个事务的等待列表被视为死锁
2. 事务回滚，默认只会回滚到事务中的前一条sql：
   参数配置：innodb_rollback_on_timeout
   如果开启该配置，事务回滚会会回滚整个事务
3. 默认开启死锁检测，在大并发下会影响性能：
   官方文档提到：
   在高并发系统上，当大量线程等待相同的锁时，死锁检测会导致速度下降。有时，在死锁发生时，禁用死锁检测并依赖innodb_lock_wait_timeout设置进行事务回滚可能更有效。使用innodb_deadlock_detect配置选项可以禁用死锁检测。
   innodb_deadlock_detect=1关闭死锁检测，mysql 5.7.15 之后支持该配置，但官方版本直到5.7.15才增加了这个参数