show engine innodb status的介绍与使用
很多时候我们的死锁出现后立马就会回滚，无法通过报错日志直接定位到异常的sql语句，这里就需要使用mysql为我们提供的分析工具show engine innodb status
1、内容介绍
------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-08-18 14:04:16 0x7f29f2ef5700
*** (1) TRANSACTION:
TRANSACTION 14235673, ACTIVE 0 sec starting index read
mysql tables in use 3, locked 3
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 10089425, OS thread handle 139823504013056, query id 157595766 10.75.34.61 dbroot updating
update table1 set aaa='4',bbb='121',ccc='合格',ddd='0',eee='' where bd='6f174b50-8d32' and dw='1e0adeed-3f0d-450a' and tag =1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14096 page no 57 n bits 272 index bd_index of table `mydatabase`.`table1` trx id 14235673 lock_mode X locks rec but not gap waiting
Record lock, heap no 114 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 36663137346235302d386433322d343763362d613062622d633962333238; asc 6f174b50-8d32-47c6-a0bb-c9b328; (total 36 bytes);
1: len 30; hex 30636538343031652d653061662d343733362d623031372d626464623663; asc 0ce8401e-e0af-4736-b017-bddb6c; (total 36 bytes);
*** (2) TRANSACTION:
TRANSACTION 14235674, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
9 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 2
MySQL thread id 10089424, OS thread handle 139818146158336, query id 157595768 10.75.34.61 dbroot updating
update table1 set aaa='10',bbb='12',ccc='合格',ddd='0',eee='' where bd='6f174b50-8d32' and dw='ffb27cdc-ba40-4e16' and tag =1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14096 page no 57 n bits 272 index bd_index of table `mydatabase`.`table1` trx id 14235674 lock_mode X locks rec but not gap
Record lock, heap no 114 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 36663137346235302d386433322d343763362d613062622d633962333238; asc 6f174b50-8d32-47c6-a0bb-c9b328; (total 36 bytes);
1: len 30; hex 30636538343031652d653061662d343733362d623031372d626464623663; asc 0ce8401e-e0af-4736-b017-bddb6c; (total 36 bytes);
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14096 page no 57 n bits 272 index bd_index of table `mydatabase`.`table1` trx id 14235674 lock_mode X locks rec but not gap waiting
Record lock, heap no 105 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 30; hex 36663137346235302d386433322d343763362d613062622d633962333238; asc 6f174b50-8d32-47c6-a0bb-c9b328; (total 36 bytes);
1: len 30; hex 66396262333533362d356137342d343633352d386335632d323666356537; asc f9bb3536-5a74-4635-8c5c-26f5e7; (total 36 bytes);
*** WE ROLL BACK TRANSACTION (1)
TRANSACTION 14235673, ACTIVE 0 sec starting index read
事务14235673，ACTIVE 0 sec表示事务处于活跃状态0s，starting index read表示正在使用索引读取数据行
mysql tables in use 3, locked 3
事务1正在使用3个表，且涉及锁的表有3个
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
这行表示在等待3把锁，占用内存1136字节，涉及2行记录，如果事务已经锁定了几行数据。
MySQL thread id 10089425, OS thread handle 139823504013056, query id 157595766 10.75.34.61 dbroot updating
该事务的线程ID信息，操作系统句柄信息，连接来源、用户等
update table1 set aaa='4',bbb='121',ccc='合格',ddd='0',eee='' where bd='6f174b50-8d32' and dw='1e0adeed-3f0d-450a' and tag =1
正在等待行锁的sql
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
*** (2) HOLDS THE LOCK(S):
正在等待的锁、目前保存的锁
RECORD LOCKS space id 14096 page no 57 n bits 272 index bd_index of table `mydatabase`.`table1` trx id 14235673 lock_mode X locks rec but not gap waiting
等待的锁是一个record lock，空间id是14096，页编号为57，大概位置在页的272位处，锁发生在表mydatabase.table1的bd_index 索引上，是一个X锁，但是不是gap lock。 waiting表示正在等待锁
Record lock, heap no 114 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
这行表示record lock的heap no 位置（可以用来对照事务2控制住的锁）
*** WE ROLL BACK TRANSACTION (1)
回滚了事务1
2、内容分析
通过观察事务1等待的行锁在堆中的位置，与事务2获取到的锁在堆中的位置，确定了事务2手中有事务1想要获取的锁。再来分析涉及到的sql，我们发现产生冲突的是同1条sql，在table1这张表中，bd与dw是一对多的关系（这里假设是1:10），也就导致要获取bd_index（基于bd的单列索引）时，要获取到全部的10个bd上的索引，也就导致产生了争用。
3、解决办法
问题的原因在于单列索引扫描的范围过大，要获取到全部10个db上的索引，那么我们只要减小获取锁的范围就好了，这里就到了我们的组合索引展示优势的地方了。我们建立bd,dw上的组合索引，这样一来，事务1和事务2中的2条sql都只要获取到自身涉及到的那1条组合索引即可。
create index complex_index on table1(bd,dw);
原文链接：https://blog.csdn.net/wzngzaixiaomantou/article/details/119983531
