死锁问题分析-
线下测试库执行优化器如下：
2、线上执行优化器如下：
show engine innodb status;日志如下：
-----------------------
LATEST DETECTED DEADLOCK
------------------------
2020-10-20 19:14:11 7f66192cc700
*** (1) TRANSACTION:
TRANSACTION 2930691069, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s)
MySQL thread id 8635369, OS thread handle 0x7f65c80e7700, query id 399419122 192.168.36.156 aiwriter Searching rows for update
UPDATE `mix_class_course` SET `status` = 4 WHERE (`mini_class_number` = 1219935844681215 AND `course_lesson_number` = 1183463075197439)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 362 page no 5915 n bits 920 index `idx_lesson` of table `aiclass`.`mix_class_course` trx id 2930691069 lock_mode X waiting
Record lock, heap no 80 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b0; asc ;;
*** (2) TRANSACTION:
TRANSACTION 2930691065, ACTIVE 0 sec fetching rows, thread declared inside InnoDB 5000
mysql tables in use 3, locked 3
13 lock struct(s), heap size 2936, 68 row lock(s)
MySQL thread id 8635363, OS thread handle 0x7f66192cc700, query id 399419119 192.168.36.149 aiwriter Searching rows for update
UPDATE `mix_class_course` SET `status` = 4 WHERE (`mini_class_number` = 1213655378639359 AND `course_lesson_number` = 1183463075197439)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 362 page no 5915 n bits 920 index `idx_lesson` of table `aiclass`.`mix_class_course` trx id 2930691065 lock_mode X
Record lock, heap no 80 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b0; asc ;;
Record lock, heap no 81 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b1; asc ;;
Record lock, heap no 82 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b2; asc ;;
Record lock, heap no 83 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b3; asc ;;
Record lock, heap no 84 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b4; asc ;;
Record lock, heap no 85 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b5; asc ;;
Record lock, heap no 86 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b6; asc ;;
Record lock, heap no 87 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b7; asc ;;
Record lock, heap no 88 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b8; asc ;;
Record lock, heap no 89 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606b9; asc ;;
Record lock, heap no 90 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606ba; asc ;;
Record lock, heap no 91 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606bb; asc ;;
Record lock, heap no 92 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606bc; asc ;;
Record lock, heap no 93 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 800606bd; asc ;;
Record lock, heap no 342 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b26; asc &;;
Record lock, heap no 343 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b27; asc ';;
Record lock, heap no 344 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b28; asc (;;
Record lock, heap no 345 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b29; asc );;
Record lock, heap no 346 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b2a; asc *;;
Record lock, heap no 347 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b2b; asc +;;
Record lock, heap no 348 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80060b2c; asc ,;;
Record lock, heap no 798 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061175; asc u;;
Record lock, heap no 799 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061176; asc v;;
Record lock, heap no 800 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061177; asc w;;
Record lock, heap no 801 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061178; asc x;;
Record lock, heap no 802 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061179; asc y;;
Record lock, heap no 803 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117a; asc z;;
Record lock, heap no 804 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117b; asc {;;
Record lock, heap no 805 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117c; asc |;;
Record lock, heap no 806 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117d; asc };;
Record lock, heap no 807 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117e; asc ~;;
Record lock, heap no 808 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 8006117f; asc ;;
Record lock, heap no 809 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
1: len 4; hex 80061180; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 362 page no 6707 n bits 200 index `PRIMARY` of table `aiclass`.`mix_class_course` trx id 2930691065 lock_mode X locks rec but not gap waiting
Record lock, heap no 89 PHYSICAL RECORD: n_fields 21; compact format; info bits 0
0: len 4; hex 80061180; asc ;;
1: len 6; hex 0000aeab508f; asc P ;;
2: len 7; hex fb000002210110; asc ! ;;
3: len 8; hex 80044648f703e1ff; asc FH ;;
4: len 8; hex 8004558672abc5ff; asc U r ;;
5: len 8; hex 8000000000000000; asc ;;
6: len 8; hex 8004345a71025fff; asc 4Zq _ ;;
7: len 8; hex 8004345a7833b1ff; asc 4Zx3 ;;
8: len 4; hex 80001877; asc w;;
9: len 4; hex 800005dc; asc ;;
10: len 4; hex 5f8ebc28; asc _ (;;
11: len 4; hex 5f8ec588; asc _ ;;
12: len 4; hex 00000000; asc ;;
13: len 1; hex 81; asc ;;
14: len 4; hex 80000000; asc ;;
15: len 4; hex 80000000; asc ;;
16: len 4; hex 80000000; asc ;;
17: len 4; hex 80000000; asc ;;
18: len 8; hex 8004558672bbc5ff; asc U r ;;
19: len 4; hex 5f8ebb33; asc _ 3;;
20: len 4; hex 00000000; asc ;;
看日志能发现用到了idx_lesson索引，结合优化器，说明类型走的index_merge。
研发同学在测试库复现死锁，加联合索引后类型走range,没有联合索引走index_merge。证明死锁是index_merge导致，加联合索引解决。
