Mysql update后insert造成死锁原因分析及解决
系统中出现死锁的日志如下：
*** (1) TRANSACTION: TRANSACTION 1331088253, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 4 MySQL thread id 14699751, OS thread handle 0x7fc5eaeda700, query id 382901670 172.18.140.10 bms update INSERT INTO `finance_settlement_detail` (`order_detail_id`, `tenant_id`, `store_id`, `order_id`, `working_type_id`, `working_item_id`, `working_item_base_id`, `working_type_name`, `item_code_all`, `achievements_code`, `price_code`, `item_name_all`, `copies_num`, `one_copies_num`, `show_copies_num`, `unit_num`, `unit_price`, `discount`, `amount`, `standard_unit_price`, `standard_total_price`, `item_code1`, `item_name1`, `item_code2`, `item_name2`, `item_code3`, `item_name3`, `item_code4`, `item_name4`, `item_code5`, `item_name5`, `make_info`, `material_id`, `material_name`, `cost_price`, `cost_amount`, `unit`, `make_order_id`, `complete_num`, `outsourcing`, `create_time`, `customer_hash`, `settlement_hash`, `rate`, `product_name`, `below_lowest_price`, `item_lowest_price`, `type_lowest_discount`, `finance_settlement_id`) VALUES (1236472, 125, 1046, 451483, 655, 54047, NULL, '设计/影像', 'custom', NUL *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1156 page no 24892 n bits 760 index `finance_settlement_id` of table `test`.`finance_settlement_detail` trx id 1331088253 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 1331088264, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 4 MySQL thread id 14699754, OS thread handle 0x7fc5eacd2700, query id 382901673 172.18.140.10 bms update INSERT INTO `finance_settlement_detail` (`order_detail_id`, `tenant_id`, `store_id`, `order_id`, `working_type_id`, `working_item_id`, `working_item_base_id`, `working_type_name`, `item_code_all`, `achievements_code`, `price_code`, `item_name_all`, `copies_num`, `one_copies_num`, `show_copies_num`, `unit_num`, `unit_price`, `discount`, `amount`, `standard_unit_price`, `standard_total_price`, `item_code1`, `item_name1`, `item_code2`, `item_name2`, `item_code3`, `item_name3`, `item_code4`, `item_name4`, `item_code5`, `item_name5`, `make_info`, `material_id`, `material_name`, `cost_price`, `cost_amount`, `unit`, `make_order_id`, `complete_num`, `outsourcing`, `create_time`, `customer_hash`, `settlement_hash`, `rate`, `product_name`, `below_lowest_price`, `item_lowest_price`, `type_lowest_discount`, `finance_settlement_id`) VALUES (1247931, 118, 240, 455597, 961, 115484, 40698, '彩色快印', 'csdy a4dm 80 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1156 page no 24892 n bits 760 index `finance_settlement_id` of table `test`.`finance_settlement_detail` trx id 1331088264 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 1156 page no 24892 n bits 760 index `finance_settlement_id` of table `test`.`finance_settlement_detail` trx id 1331088264 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 (2) 2018-10-26 12:21:54 7fc5eaf5c700InnoDB: transactions deadlock detected, dumping detailed information. 2018-10-26 12:21:54 7fc5eaf5c700
死锁日志分析：
1、事务1执行insert语句等待获得X锁；
2、事务2现持有S锁，但执行insert语句也在等待X锁，这样就存在两个事务间相互等待，死锁产生，Mysql自动回滚了事务2；
3、表引擎为innodb，行锁，在字段finance_settlement_id形成，普通索引而非主键索引；
4、因为Mysql死锁日志打印不完全，无法知道上文死锁产生前的sql语句的执行情况，根据以上还无法完全分析出死锁产生的根本原因。
根据使用Xdebug工具进一步调试跟踪代码执行情况，发现在死锁前有update finance_settlement_detail where finance_settlement_Id = XXX这样的语句，这应该就是死锁产生的”凶手“了。
最终原因分析：
1、innodb引擎下update在默认情况下是行锁，但是在Mysql默认隔离级别（可重复读）下，一旦update更新的数据行不存在，则会产生间隙锁（Gap lock）；
2、事务1 update不存在的数据行，产生了Gap lock，事务2 update不存在的数据行，也产生了Gap lock；
3、事务1 insert操作需要等待对方释放X锁，事务2 insert操作也需要等待对方释放X锁，死锁产生，Mysql自动回滚了事务2；
如何解决死锁：
解决死锁的原则就是破坏死锁产生的条件，在以上案例中，只需要判断当对应数据行不存在时，不执行update语句即可。
