MySQL Bugs: #107656: insert into on duplicate key update with PRIMARY cause a deadlock

Bug #107656	insert into on duplicate key update with PRIMARY cause a deadlock
Submitted:	25 Jun 2022 13:55	Modified:	1 Jul 2022 6:22
Reporter:	明曹	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.7.29	OS:	Linux
Assigned to:		CPU Architecture:	x86
Tags:	deadlock

Description:
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-06-25 04:15:10 0x7fdefc9b6700
*** TRANSACTION:
TRANSACTION 366948712, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
300 lock struct(s), heap size 41168, 1673 row lock(s), undo log entries 1146
MySQL thread id 4928530, OS thread handle 140595697510144, query id 932890374 25.34.86.112 neplms_colldb update
insert into lmr_ori_real_power_cp (ID, COLL_OBJ_TYPE, COLL_OBJ_ID,
    COLL_TYPE, DATA_DATE, CURVE_DENSITY,
    COLL_ITEM_CODE, DATA_VALUE, DATA_TIME,
    PT, CT, INSERT_TIME
    )
    values
      
      (replace(uuid(), '-', ''), '13', '8000000045875347',
      '2', '2022-06-25', '15',
      'B630', 1.3448,'2022-06-25 03:00:00',
      '1', '200', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000045875347',
      '2', '2022-06-25', '15',
      'B630', 1.297,'2022-06-25 03:15:00',
      '1', '200', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000045875347',
      '2', '2022-06-25', '15',
      'B630', 1.3238,'2022-06-25 03:30:00',
      '1', '200', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000045875347',
      '2', '2022-06-25', '15',
      'B630', 1.2881,'2022-06-25 03:45:00',
      '1', '200', now()
      )
     , 
      (replace(uu
*** HOLDS THE LOCK:
RECORD LOCKS space id 55852 page no 1086 n bits 216 index PRIMARY of table `neplms_colldb`.`lmr_ori_real_power_cp` /* Partition `p2022062503` */ trx id 366948712 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;;

***  WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 366948713:
RECORD LOCKS space id 55852 page no 1262 n bits 216 index PRIMARY of table `neplms_colldb`.`lmr_ori_real_power_cp` /* Partition `p2022062503` */ trx id 366948712 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;;

*** TRANSACTION:
TRANSACTION 366948713, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 252 lock struct(s), heap size 24784, 1193 row lock(s), undo log entries 894
MySQL thread id 4928533, OS thread handle 140595697780480, query id 932890375 25.34.86.112 neplms_colldb update
insert into lmr_ori_real_power_cp (ID, COLL_OBJ_TYPE, COLL_OBJ_ID,
    COLL_TYPE, DATA_DATE, CURVE_DENSITY,
    COLL_ITEM_CODE, DATA_VALUE, DATA_TIME,
    PT, CT, INSERT_TIME
    )
    values
      
      (replace(uuid(), '-', ''), '13', '8000000044661706',
      '2', '2022-06-25', '15',
      'B630', 0.1034,'2022-06-25 03:00:00',
      '100', '8', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000044661706',
      '2', '2022-06-25', '15',
      'B630', 0.113,'2022-06-25 03:15:00',
      '100', '8', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000044661706',
      '2', '2022-06-25', '15',
      'B630', 0.126,'2022-06-25 03:30:00',
      '100', '8', now()
      )
     , 
      (replace(uuid(), '-', ''), '13', '8000000044661706',
      '2', '2022-06-25', '15',
      'B640', 0.0496,'2022-06-25 03:00:00',
      '100', '8', now()
      )
     , 
      (replace(uui
*** HOLDS THE LOCK:
RECORD LOCKS space id 55852 page no 1262 n bits 216 index PRIMARY of table `neplms_colldb`.`lmr_ori_real_power_cp` /* Partition `p2022062503` */ trx id 366948713 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;;

***  WAITING FOR THIS LOCK TO BE GRANTED, WHICH CONFLICTS WITH THE LOCK HELD BY TRANSACTION 366948712:
RECORD LOCKS space id 55852 page no 1086 n bits 216 index PRIMARY of table `neplms_colldb`.`lmr_ori_real_power_cp` /* Partition `p2022062503` */ trx id 366948713 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 366948713

document describes :
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row. Suppose that an InnoDB table t1 has the following structure:

How to repeat:
two insert into  on duplicate key update with PRIMARY cause a deadlock

INSERT ... ON DUPLICATE KEY UPDATE differs from a simple INSERT in that an exclusive lock rather than a shared lock is placed on the row to be updated when a duplicate-key error occurs. An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

transaction isolation is 'READ-COMMITTED';

The blog may be helpful for you: http://mysql.taobao.org/monthly/2022/05/02/

HI Mr. 明 曹

Thank you for your bug report.

However, it is not a bug.

Whenever a duplicate key is encountered, a rows is not inserted but updated. Each row that should be updated must first acquire an exclusive lock. That is because it is not about a new row, but it is about changing the existing row, which may be required by other concurrent transaction.

This is all explained in our Reference Manual.

Not a bug.

it is primary key in the example.pk and uk should be diffrent.

Just as the official description makes sense:
 An exclusive index-record lock is taken for a duplicate primary key value. An exclusive next-key lock is taken for a duplicate unique key value.

Hi,

Our description is correct. Primary and unique indices behave the same, except for the NULL values.

Also, there can not be a duplicate primary key values !!!!!!