[MySQL] RC级别下MySQL死锁问题的解决

• 背景
  
• 死锁分析
  
• 死锁解决
  

2021-03-15 16:40:49 0x7f17e97ff700
*** (1) TRANSACTION:
TRANSACTION 2120576727, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2
MySQL thread id 9384894, OS thread handle 139741055362816, query id 309547615 10.96.197.241 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(       cmmdty_code, business_field,       business_field_desc,       keyword_code,       lastmodifier,       lastmodified ) VALUES ( "12256633711", "TAX_CODE", "1040201230000000000", "000001", "sys",       now() )  ON DUPLICATE KEY UPDATE        business_field = "TAX_CODE",         business_field_desc = "1040201230000000000",         keyword_code = "000001",         lastmodifier = "sys",        lastmodified = now()
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576727 lock_mode X waiting
*** (2) TRANSACTION:
TRANSACTION 2120576728, ACTIVE 0 sec inserting, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 2
MySQL thread id 9481029, OS thread handle 139740678452992, query id 309547616 10.98.61.213 nsfbususr update
INSERT INTO MD_CMMDTY_OTHER19(       cmmdty_code, business_field,       business_field_desc,       keyword_code,       lastmodifier,       lastmodified ) VALUES ( "12256633763", "TAX_CODE", "1040201230000000000", "000001", "sys",       now() )  ON DUPLICATE KEY UPDATE        business_field = "TAX_CODE",         business_field_desc = "1040201230000000000",         keyword_code = "000001",         lastmodifier = "sys",        lastmodified = now()
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X locks rec but not gap  //持有记录锁
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 306 page no 1310102 n bits 496 index idx_cmmdty_code_business_field of table `nsfbusprd`.`md_cmmdty_other19` trx id 2120576728 lock_mode X waiting  //等待X锁
*** WE ROLL BACK TRANSACTION (2)

Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED or enable the innodb_locks_unsafe_for_binlog system variable (which is now deprecated).

In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.