MySQL 死锁问题的系统化排查与并发优化
现象 补全翻译接口（填充空白的语料）接口执行失败，MySQL 检测到死锁快速抛异常，接口执行耗时 426ms。 无论是从接口、具体表现、根因，都与上个问题有明显区别。 分析 1、查看死锁日志，可以看到有两个事务，是对同一个项目的不同语言的批量插入更新操作（INSERT ON DUPLICATE KEY UPDATE）。 ------------------------ LATEST DETECTED DEADLOCK ------------------------ <timestamp> <os_thread_hex> *** (1) TRANSACTION: TRANSACTION <trx_1>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_1>, OS thread handle <handle_1>, query id <qid_1> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_1>, <corpus_id>, 'my-MM', 'default', '下一步', '', 0, 1) , (<item_id_2>, <corpus_id>, 'my-MM', 'default', '分组竞争', '', 0, 1) , (<item_id_3>, <corpus_id>, 'my-MM', 'default', '判断对错', '', 0, 1) , (<item_id_4>, <corpus_id>, 'my-MM', 'default', '完成', '', 0, 1) , (<item_id_5>, <corpus_id>, 'my-MM', 'default', '球球拼词', '', 0, 1) , (<item_id_6>, <corpus_id>, 'my-MM', 'default', '知识排序', '', 0, 1) , (<item_id_7>, <corpus_id>, 'my-MM', 'default', '知识配对', '', 0, 1) , (<item_id_8>, <corpus_id>, 'my-MM', 'default', '记忆卡片', '', 0, 1) , (<item_id_9>, <corpus_id>, 'my-MM', 'default', '试玩', '', 0, 1) , (<item_id_10>, <corpus_id>, 'my-MM', 'default', '超级分类', '', 0, 1) , (<item_id_11>, <corpus_id>, 'my-MM', 'default', '趣味分类', '', 0, 1) , (<item_id_12>, <corpus_id>, 'my-MM', 'default', '趣味选择', '', 0, 1) , (<item_id_13>, <corpus_id>, 'my-MM', 'default', '返回', '', 0, 1) , (<item_id_14>, <corpus_id>, 'my-MM', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_1> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION <trx_2>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_2>, OS thread handle <handle_2>, query id <qid_2> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_a1>, <corpus_id>, 'ar-EG', 'default', '下一步', '', 0, 1) , (<item_id_a2>, <corpus_id>, 'ar-EG', 'default', '分组竞争', '', 0, 1) , (<item_id_a3>, <corpus_id>, 'ar-EG', 'default', '判断对错', '', 0, 1) , (<item_id_a4>, <corpus_id>, 'ar-EG', 'default', '完成', '', 0, 1) , (<item_id_a5>, <corpus_id>, 'ar-EG', 'default', '球球拼词', '', 0, 1) , (<item_id_a6>, <corpus_id>, 'ar-EG', 'default', '知识排序', '', 0, 1) , (<item_id_a7>, <corpus_id>, 'ar-EG', 'default', '知识配对', '', 0, 1) , (<item_id_a8>, <corpus_id>, 'ar-EG', 'default', '记忆卡片', '', 0, 1) , (<item_id_a9>, <corpus_id>, 'ar-EG', 'default', '试玩', '', 0, 1) , (<item_id_a10>, <corpus_id>, 'ar-EG', 'default', '超级分类', '', 0, 1) , (<item_id_a11>, <corpus_id>, 'ar-EG', 'default', '趣味分类', '', 0, 1) , (<item_id_a12>, <corpus_id>, 'ar-EG', 'default', '趣味选择', '', 0, 1) , (<item_id_a13>, <corpus_id>, 'ar-EG', 'default', '返回', '', 0, 1) , (<item_id_a14>, <corpus_id>, 'ar-EG', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) 根据死锁日志画出时序图，从下面这个时序图可以看到死锁的形成过程。 ...