一、死锁现象描述

1.1 基本环境信息

1、数据库版本以及隔离级别

mysql>select version();
+---------------------+
| version()           |
+---------------------+
| 5.7.26-log          |
+---------------------+
返回行数：[1]，耗时：3 ms.

mysql>show variables like '%iso%';
+-------------------------+-----------------+
| Variable_name           | Value           |
+-------------------------+-----------------+
| transaction_isolation   | READ-COMMITTED  |
| tx_isolation            | READ-COMMITTED  |
+-------------------------+-----------------+
返回行数：[2]，耗时：7 ms.

2、发生死锁表结构信息

该表结构贴出了本地测试的表结构，与线上保持一致

mysql> show create table deadlock_test\G
*************************** 1. row ***************************
       Table: deadlock_test
Create Table: CREATE TABLE `deadlock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info_uuid` varchar(50) DEFAULT NULL,
  `view_date` date DEFAULT NULL,
  `view_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_view_date` (`view_date`),
  KEY `idx_info_uuid` (`info_uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=908189 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

3、死锁日志

死锁日志中对部分信息做了替换，不影响问题分析。

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-14 07:25:04 0x7f588e4f9700
*** (1) TRANSACTION:
TRANSACTION 51481020, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 15 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 2557156, OS thread handle 140017821800192, query id 538638931 192.168.22.51 update_user updating
UPDATE deadlock_test
        SET VIEW_COUNT = VIEW_COUNT + 1
        WHERE INFO_UUID = '1a973b987d1d11eaa8c07cd30ad3aa18'
        AND VIEW_DATE = '2020-05-14'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1879 page no 9047 n bits 304 index PRIMARY of table `db1`.`deadlock_test` trx id 51481020 lock_mode X locks rec but not gap waiting
Record lock, heap no 22 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800dbba4; asc     ;;
 1: len 6; hex 0000031189af; asc       ;;
 2: len 7; hex 5b000001552b82; asc [   U+ ;;
 3: len 30; hex 323763336366316536336661313165613862396137636433306165303037; asc 27c3cf1e63fa11ea8b9a7cd30ae007; (total 32 bytes);
 4: len 3; hex 8fc8ae; asc    ;;
 5: len 4; hex 80000222; asc    ";;

*** (2) TRANSACTION:
TRANSACTION 51481021, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
34 lock struct(s), heap size 3520, 3 row lock(s)
MySQL thread id 2557158, OS thread handle 140018321430272, query id 538638932 192.168.22.51 update_user updating
UPDATE deadlock_test
        SET VIEW_COUNT = VIEW_COUNT + 1
        WHERE INFO_UUID = '27c3cf1e63fa11ea8b9a7cd30ae0074a'
        AND VIEW_DATE = '2020-05-14'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1879 page no 9047 n bits 304 index PRIMARY of table `db1`.`deadlock_test` trx id 51481021 lock_mode X locks rec but not gap
Record lock, heap no 22 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 800dbba4; asc     ;;
 1: len 6; hex 0000031189af; asc       ;;
 2: len 7; hex 5b000001552b82; asc [   U+ ;;
 3: len 30; hex 323763336366316536336661313165613862396137636433306165303037; asc 27c3cf1e63fa11ea8b9a7cd30ae007; (total 32 bytes);
 4: len 3; hex 8fc8ae; asc    ;;
 5: len 4; hex 80000222; asc    ";;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1879 page no 8442 n bits 1272 index idx_view_date of table `db1`.`deadlock_test` trx id 51481021 lock_mode X locks rec but not gap waiting
Record lock, heap no 853 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8fc8ae; asc    ;;
 1: len 4; hex 800dbba4; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

二、原因分析

2.1 死锁日志分析

1、事务1

##业务SQL id=900006
    UPDATE deadlock_test
    SET VIEW_COUNT = VIEW_COUNT + 1
    WHERE INFO_UUID = '1a973b987d1d11eaa8c07cd30ad3aa18'
        AND VIEW_DATE = '2020-05-14'

##等待锁
    *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
    -- 等待deadlock_test表在primary key上的行锁
    RECORD LOCKS space id 1879 page no 9047 n bits 304 index PRIMARY of table `db1`.`deadlock_test` trx id 51481020 lock_mode X locks rec but not gap  waiting
    Record lock, heap no 22 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    -- 锁定的行锁记录为 (id=900004) 
    0: len 4; hex 800dbba4; asc     ;;
    1: len 6; hex 0000031189af; asc       ;;
    2: len 7; hex 5b000001552b82; asc [   U+ ;;
    
##持有锁
    
    -- 从死锁发生的情况分析，说明该事务持有了(view_date='2020-05-14',id=900004)该记录的锁。
    
##MySQL处理方案
    回滚

2、事务2

##业务SQL id=900004
    UPDATE deadlock_test
    SET VIEW_COUNT = VIEW_COUNT + 1
    WHERE INFO_UUID = '27c3cf1e63fa11ea8b9a7cd30ae0074a'
        AND VIEW_DATE = '2020-05-14'

##持有锁
    *** (2) HOLDS THE LOCK(S):
    -- 持有deadlock_test表在parimary key上的行锁
    RECORD LOCKS space id 1879 page no 9047 n bits 304 index PRIMARY of table `db1`.`deadlock_test` trx id 51481021 lock_mode X locks rec but not gap
    Record lock, heap no 22 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
    -- 持有的行锁记录为：(id=900004)
    0: len 4; hex 800dbba4; asc     ;;
    1: len 6; hex 0000031189af; asc       ;;
    2: len 7; hex 5b000001552b82; asc [   U+ ;;
    
##等待锁
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    -- 等待deadlock_test表在idx_view_date上的行锁
    RECORD LOCKS space id 1879 page no 8442 n bits 1272 index idx_view_date of table `db1`.`deadlock_test` trx id 51481021 lock_mode X locks rec but not gap waiting
    Record lock, heap no 853 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
    -- 等待的行锁记录为：(view_date='2020-05-14',id=900004)
    0: len 3; hex 8fc8ae; asc    ;;
    1: len 4; hex 800dbba4; asc     ;;
    
##MySQL处理方案
    不回滚，执行成功

3、发生死锁SQL以及数据分布分析

1）业务SQL执行计划

从SQL执行的执行计划中我们可以看到，SQL使用的是index_merge，也就是说MySQL会分别使用idx_info_uuid、index_view_date，然后取其交集。

mysql>explain
select * from deadlock_test  WHERE INFO_UUID = '27c3cf1e63fa11ea8b9a7cd30ae0074a'
        AND VIEW_DATE = '2020-05-14'
+--------------+-----------------------+-------------------------------+----------------------+----------------+-------------------------------+-------------------------------+-------------------+---------------+----------------+--------------------+-------------------------------------------------------------+
| id           | select_type           | table                         | partitions           | type           | possible_keys                 | key                           | key_len           | ref           | rows           | filtered           | Extra                                                       |
+--------------+-----------------------+-------------------------------+----------------------+----------------+-------------------------------+-------------------------------+-------------------+---------------+----------------+--------------------+-------------------------------------------------------------+
| 1            | SIMPLE                | deadlock_test                 |                      | index_merge    | idx_info_uuid  ,idx_view_date | idx_info_uuid  ,idx_view_date | 153,4             |               | 1              |                100 | Using intersect(idx_info_uuid  ,idx_view_date); Using where |
+--------------+-----------------------+-------------------------------+----------------------+----------------+-------------------------------+-------------------------------+-------------------+---------------+----------------+--------------------+-------------------------------------------------------------+
返回行数：[1]，耗时：5 ms.

2）业务SQL数据分布

从数据分布来看其实INFO_UUID字段的过滤性已经比较好了，但是MySQL仍然选择了使用INFO_UUID字段与VIEW_DATE两个字段的单列索引去做index merge操作。并且从这里我们可以看到，如果INFO_UUID与VIEW_DATE如果复合索引做条件过滤，基本可以定位到只有1条记录。

mysql>select count(*) from deadlock_test WHERE INFO_UUID = '27c3cf1e63fa11ea8b9a7cd30ae0074a'
+--------------------+
| count(*)           |
+--------------------+
| 28                 |
+--------------------+
返回行数：[1]，耗时：4 ms.

mysql>select count(*) from deadlock_test WHERE INFO_UUID = '1a973b987d1d11eaa8c07cd30ad3aa18'
+--------------------+
| count(*)           |
+--------------------+
| 9                  |
+--------------------+
返回行数：[1]，耗时：4 ms.

mysql>select count(*) from deadlock_test WHERE  VIEW_DATE = '2020-05-14'
+--------------------+
| count(*)           |
+--------------------+
| 8224               |
+--------------------+
返回行数：[1]，耗时：6 ms.

mysql>select * from deadlock_test WHERE INFO_UUID = '1a973b987d1d11eaa8c07cd30ad3aa18' AND VIEW_DATE = '2020-05-14'
+--------------+----------------------------------+---------------------+----------------------+
| ID           | INFO_UUID                        | VIEW_DATE           | VIEW_COUNT           |
+--------------+----------------------------------+---------------------+----------------------+
|       900006 | 1a973b987d1d11eaa8c07cd30ad3aa18 | 2020-05-14          |                  xxx |
+--------------+----------------------------------+---------------------+----------------------+
返回行数：[1]，耗时：4 ms.
mysql>select * from deadlock_test WHERE INFO_UUID = '27c3cf1e63fa11ea8b9a7cd30ae0074a' AND VIEW_DATE = '2020-05-14'
+--------------+----------------------------------+---------------------+----------------------+
| ID           | INFO_UUID                        | VIEW_DATE           | VIEW_COUNT           |
+--------------+----------------------------------+---------------------+----------------------+
|       900004 | 27c3cf1e63fa11ea8b9a7cd30ae0074a | 2020-05-14          |                  xxx |
+--------------+----------------------------------+---------------------+----------------------+
返回行数：[1]，耗时：4 ms.

4、初步的分析判断

1）通过RDS的SQL审计日志，我们发现以上两个业务SQL并没有放在事务里去执行；

2）事务1中执行的业务SQL中，需要变更的记录为id=900006，死锁日志中我们却发现需要等待primary key索引中id=900004的锁；

3）事务2中已经持有了primary key中id=900004的锁，他们却仍然需要等待idx_view_date上(view_date,id)的锁；

4）上述2、3两点都是比较奇怪的，但是结合SQL执行计划中index_merge的影响，我们推测SQL在执行中是否需要对idx_info_uuid和idx_view_date进行扫描加锁，一定程度上扩大了锁范围，从而导致了事务1事务2中业务SQL并发请求时出现了死锁的情况

三、测试验证

3.1 数据准备

CREATE TABLE `deadlock_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `info_uuid` varchar(50) DEFAULT NULL,
  `view_date` date DEFAULT NULL,
  `view_count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_view_date` (`view_date`),
  KEY `idx_info_uuid` (`info_uuid`)
) ENGINE=InnoDB AUTO_INCREMENT=30000 DEFAULT CHARSET=utf8

mysql> select * from deadlock_test where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04';
+-------+-----------+------------+------------+
| id    | info_uuid | view_date  | view_count |
+-------+-----------+------------+------------+
| 13908 | aa381     | 2020-05-04 |         77 |
+-------+-----------+------------+------------+
1 row in set (0.02 sec)

mysql> select * from deadlock_test where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04';
+-------+-----------+------------+------------+
| id    | info_uuid | view_date  | view_count |
+-------+-----------+------------+------------+
| 29033 | aa269     | 2020-05-04 |          5 |
+-------+-----------+------------+------------+
1 row in set (0.05 sec)

3.2 模拟index_merge下加锁的情况

Session A	Session B
begin;	-
select id from deadlock_test force index(idx_info_uuid) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update;	begin;
·	select id from deadlock_test force index(idx_info_uuid) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update;
select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update; --被hang住	-
·	select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update; --死锁发生

3.3 死锁日志查看

------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-05-14 22:31:06 0x70000fc2f000
*** (1) TRANSACTION:
TRANSACTION 39033, ACTIVE 30 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 58 lock struct(s), heap size 8400, 4 row lock(s)
MySQL thread id 5145, OS thread handle 123145565908992, query id 183203 localhost root Sending data
select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 291 page no 65 n bits 488 index PRIMARY of table `db1`.`deadlock_test` trx id 39033 lock_mode X locks rec but not gap waiting
Record lock, heap no 324 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80003654; asc   6T;;
 1: len 6; hex 000000009857; asc      W;;
 2: len 7; hex 2a000001ed2e34; asc *    .4;;
 3: len 5; hex 6161333831; asc aa381;;
 4: len 3; hex 8fc8a4; asc    ;;
 5: len 4; hex 8000004d; asc    M;;

*** (2) TRANSACTION:
TRANSACTION 39034, ACTIVE 18 sec fetching rows
mysql tables in use 1, locked 1
58 lock struct(s), heap size 8400, 3 row lock(s)
MySQL thread id 5153, OS thread handle 123145566744576, query id 183204 localhost root Sending data
select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 291 page no 65 n bits 488 index PRIMARY of table `db1`.`deadlock_test` trx id 39034 lock_mode X locks rec but not gap
Record lock, heap no 324 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80003654; asc   6T;;
 1: len 6; hex 000000009857; asc      W;;
 2: len 7; hex 2a000001ed2e34; asc *    .4;;
 3: len 5; hex 6161333831; asc aa381;;
 4: len 3; hex 8fc8a4; asc    ;;
 5: len 4; hex 8000004d; asc    M;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 291 page no 37 n bits 1272 index idx_view_date of table `db1`.`deadlock_test` trx id 39034 lock_mode X locks rec but not gap waiting
Record lock, heap no 692 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8fc8a4; asc    ;;
 1: len 4; hex 80003654; asc   6T;;

*** WE ROLL BACK TRANSACTION (2)

3.4 锁等待分析

为了分析锁等待的情况，我们把innodb_deadlock_detect参数关闭，MySQL不做死锁检测，这时候我们就可以查看相应的一些锁等待信息

1、Session A 执行通过idx_view_date索引扫描被hang住时刻的锁等待信息

可以看到Session A的第二步已经被SessionB的第一步hang住，此时需要获取的锁资源就是primary key索引上id=13908的行锁。

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 39036
 waiting_thread: 5145
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39037
blocking_thread: 5153
 blocking_query: NULL
1 row in set, 1 warning (0.00 sec)


mysql> select * from information_schema.innodb_lock_waits ;                                                                                                                                            +-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 39036             | 39184:291:65:324  | 39037           | 39185:291:65:324 |
+-------------------+-------------------+-----------------+------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from information_Schema.innodb_locks;
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index | lock_space | lock_page | lock_rec | lock_data |
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
| 39184:291:65:324 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY    |        291 |        65 |      324 | 13908     |
| 39185:291:65:324 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY    |        291 |        65 |      324 | 13908     |
+------------------+-------------+-----------+-----------+-----------------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.01 sec)

2、Session B 执行执行通过idx_view_date索引扫描被hang住时刻的锁等待信息

可以看到，两个事务锁与被锁形成了一个死锁闭环，若MySQL死锁检测打开的情况下，会立刻对其中权重较低的事务进行回滚。Session B通过idx_view_date扫描时需要获取的锁资源为(1034404, 13908),但是该资源已经被Session A持有。

mysql> SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id\G
*************************** 1. row ***************************
 waiting_trx_id: 39037
 waiting_thread: 5153 (rollback)
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39036
blocking_thread: 
 blocking_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
*************************** 2. row ***************************
 waiting_trx_id: 39036
 waiting_thread: 5145
  waiting_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update
blocking_trx_id: 39037
blocking_thread: 5153
 blocking_query: select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_schema.innodb_lock_waits ;                                                                                                                                            +-------------------+-------------------+-----------------+------------------+
| requesting_trx_id | requested_lock_id | blocking_trx_id | blocking_lock_id |
+-------------------+-------------------+-----------------+------------------+
| 39037             | 39037:291:37:692  | 39036           | 39036:291:37:692 |
| 39036             | 39036:291:65:324  | 39037           | 39037:291:65:324 |
+-------------------+-------------------+-----------------+------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> select * from information_Schema.innodb_locks;
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
| lock_id          | lock_trx_id | lock_mode | lock_type | lock_table            | lock_index    | lock_space | lock_page | lock_rec | lock_data      |
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
| 39037:291:37:692 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | idx_view_date |        291 |        37 |      692 | 1034404, 13908 |
| 39036:291:37:692 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | idx_view_date |        291 |        37 |      692 | 1034404, 13908 |
| 39036:291:65:324 | 39036       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY       |        291 |        65 |      324 | 13908          |
| 39037:291:65:324 | 39037       | X         | RECORD    | `db1`.`deadlock_test` | PRIMARY       |        291 |        65 |      324 | 13908          |
+------------------+-------------+-----------+-----------+-----------------------+---------------+------------+-----------+----------+----------------+
4 rows in set, 1 warning (0.00 sec)

3.5 整体分析处理

1、案例分析

Session A:(5145)

1) select id from deadlock_test force index(idx_info_uuid) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update; 锁资源：idx_info_uuid索引的(idx_info_uuid='aa269',id=29033)获取成功,primary索引的(id=29033)获取成功

3) select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa269' and VIEW_DATE = '2020-05-04' for update; 锁资源：idx_view_date索引的(view_date='2020-05-04',id=13908)获取成功，primary索引的(id=13908)获取被Session B的2）被hang住 --view_date索引从头到尾扫描，扫描到该记录时被hang住。

Session B:(5153) -- rollback

2) select id from deadlock_test force index(idx_info_uuid) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update; 锁资源：idx_info_uuid索引的(idx_info_uuid='aa381',id=13908)获取成功,primary索引的(id=13908)获取成功

4) select id from deadlock_test force index(idx_view_date) where info_uuid = 'aa381' and VIEW_DATE = '2020-05-04' for update; 锁资源：扫描idx_view_date索引的(view_date='2020-05-04',id=13908)获取被SessionA的3)hang住 --view_date索引从头到尾扫描，扫描到该记录时被hang住

2、处理方法

基于以上的分析，我们可以推断MySQL在使用index_merge进行扫描加锁的期间，一定程度上会扩大锁的范围。为了避免MySQL使用index_merge，我们可以将其调整为复合索引来进行优化。

index_merge导致死锁案例分析