sql语句为

   1: UPDATE `user`  SET `qr_status` = 20,`pre_qr_status` = 20,`qr_content` = 'xx内容',`qr_history_id` = 925291 WHERE  `id` = 410922
   2: insert ignore into `user` (user_id,market_id,pre_qr_status,qr_status,last_sync_success) values( 156715,8, 0,0, 0)

查看死锁日志：show engine innodb status;

*** (1) TRANSACTION:

TRANSACTION 58245399, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 12 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 12 MySQL thread id 1594873, OS thread handle 139639582721792, query id 467437072 10.28.1.55 root updating UPDATE user SET qr_status = 20,pre_qr_status = 20,qr_content = '最新操作报备单审核状态是待审核,变为黄码',qr_history_id = 925291 WHERE id = 410922

*** (1) HOLDS THE LOCK(S): RECORD LOCKS space id 2014 page no 98 n bits 176 index PRIMARY of table dili_trace.user trx id 58245399 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;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2014 page no 98 n bits 176 index PRIMARY of table dili_trace.user trx id 58245399 lock_mode X locks rec but not gap waiting Record lock, heap no 104 PHYSICAL RECORD: n_fields 43; compact format; info bits 0

*** (2) TRANSACTION:

TRANSACTION 58245373, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 19 lock struct(s), heap size 1136, 11 row lock(s), undo log entries 13 MySQL thread id 1594859, OS thread handle 139637178685184, query id 467437077 10.28.1.55 root update

*** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2014 page no 98 n bits 176 index PRIMARY of table dili_trace.user trx id 58245373 lock_mode X locks rec but not gap Record lock, heap no 104 PHYSICAL RECORD: n_fields 43; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2014 page no 98 n bits 176 index PRIMARY of table dili_trace.user trx id 58245373 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;;

分析

事务1 持有lock_mode X Record lock，在等待lock_mode X locks rec but not gap waiting Record lock

事务2 持有lock_mode X locks rec but not gap Record lock，在等待lock_mode X insert intention waiting Record lock

导致死锁产生