一、自我介绍
20年毕业，双非本，先后就职于金蝶、虾皮、快手，从事电商后端开发，北漂中。来戳我交个朋友~
二、一次死锁排查
执行show engine innodb status;后，将Status字段的关键信息截取出来（有局限性，Holds The Locks不一定是这条sql加的）：
LATEST DETECTED DEADLOCK
2022-04-20 13:53:45
*** (1) TRANSACTION:
TRANSACTION 42111123345124, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1（获取了一个表锁）
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)（获取了两个行锁-S）
MySQL thread id 3443, OS thread handle 13946546874344, query id 25911 Sending data
SELECT id,item_id,shop_id,region_id, channel_id FROM item_test_tab WHERE ((item_id=?) AND (shop_id=?)) AND (region_id=?)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 96 index PRIMARY of table item_test_tab trx id 423413945844 lock mode S locks rec but not gap waiting（在等待primary表的lock mode S locks rec but not gap，即 record lock-S）
Record lock
（事务一没有显示HOLDS THE LOCK，如果推断不出它获得的锁，可以自己在DBeaver上执行事务一然后看data_locks表会产生哪些锁）
*** (2) TRANSACTION:
TRANSACTION 177226, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 3395, OS thread handle 139645483345416, query id 25731 root updating
DELETE FROM item_test_tab WHERE (((item_id=?) AND (shop_id=?)) AND (region_id=?)) AND (channel_id IN (?,?))
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 72 index PRIMARY of table item_test_tab trx id 17726 lock_mode X locks rec but not gap
Record lock（持有primary表的lock mode X locks rec but not gap，即record lock-X）
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index idx_shop_id_item_id_channel_id of table item_test_tab trx id 17726 lock_mode X locks rec but not gap waiting
Record lock（在等待index表的lock mode X locks rec but not gap，即record lock）
*** WE ROLL BACK TRANSACTION (1)
助读:
lock mode S/X rec but not gap 是record lock，lock mode S/X 是next-keys lock，lock_mode S/X locks gap before rec 是gap lock，lock mode X locks gap before rec insert intention / lock_mode X insert intention 是插入意向锁
结论：
select获得了索引表的record lock-S然后回表等待主表的record lock-S；delete获得了主表的record lock-X然后等待索引表的record lock-X，死锁。
解决：
- 不用select * .. 利用索引覆盖避免回表。
- select ignore index禁止使用索引，直接查主表
三、立一个flag
- 不熬夜，早起健身，五一假期前减10斤
- 今年把《数据密集型应用系统设计》看完
