INSERT ON DUPLICATE KEY UPDATE occurs a deadlock error, which is about record lock

Question

I use insert ... on duplicate key updateto insert data in batches. But I get a error when do it with more than one threads, actually just two threads do it.

The table is:

create table alarm {
id varchar(20) not null collate utf8_unicode_ci,
`date` date not null,
adas blob,
dsm blob,
bsd blob,
primary key(id, `date`)
) engine=innodb default charset=utf8 
collate=utf8_unicode_ci

The insert statement is like:

insert into alarm(id, `date`, adas) values
('10001', 20220105, 0x1231231),
('10002', 20220105, 0xAFED001),
....
on duplicate key update
adas=concat(ifnull(adas,''), ifnull(values(adas),''));

The another insert statement is like:

insert into alarm(id, `date`, dsm) values
('10001', 20220105, 0x1231231),
('10002', 20220105, 0xAFED001),
('10008', 20220105, 0xAFED001),
('00008', 20220105, 0xAFED34),
....
on duplicate key update
dsm=concat(ifnull(dsm,''), ifnull(values(dsm),''));

sometimes, it will occur a dead lock error. The database log as following:

> myql    show engine innodb status \G;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-02-22 16:49:29 0x1fac
*** (1) TRANSACTION:
TRANSACTION 3902837, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 23 lock struct(s), heap size 1080, 28 row lock(s), undo log entries 31
MySQL thread id 85930, OS thread handle 17464, query id 34130124 DESKTOP-AJ1S2TF 192.168.20.191 root update
INSERT INTO device_alarm (did, `date`, dsm) VALUES('4044383', '20220222', 0x3a000a043078313012043078303220422945b8c9a832303f4031832f4ca60a685e40389ac7d2900642120a0e33303334333133313336333233301001),('4050503', '20220222', 0x38000a043078313012043078303229342db1321a493f4031ca4e3fa88b685e4038a1c7d2900642120a0e33303334333133323331333633371001),('2000023', '20220222', 0x3a000a0430783034120430783031202f29e08618af79fd36403159ddea39e9695c4038a5c7d2900642120a0e33303332333333313333333433351005),('4026821', '20220222', 0x38000a04307831301204307830322982aca7565f293f40316347e350bf725e40388cc7d2900642120a0e34313432353934413337333833331001),('4051611', '20220222', 0x3a000a0430783130120430783032201229377007ea94473f403113d38558fd545e403891c7d2900642120a0e33303334333133313339333633351001),('6493492', '20220222', 0x38000a043078303412043078303220432939d4efc2d65e4040317e54c37e4f5e5c40388ec7d29006421
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 682 page no 31 n bits 112 index PRIMARY of table `httpserver`.`device_alarm` /*  `p20220223` */ trx id 3902837 lock_mode X locks rec but not gap waiting
Record lock, heap no 42 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 11; hex 3430373336373431363336; asc 4073674;;
 1: len 3; hex 8fcc56; asc   V;;
 2: len 6; hex 0000003b8d76; asc    ; v;;
 3: len 7; hex 5500000bba09b8; asc U      ;;
 4: len 30; hex 46000a04307830321204307830312a0430783031320430783030403149d1; asc F   0x02  0x01* 0x012 0x00@1I ; (total 576 bytes);
 5: len 30; hex 38000a043078313012043078303229fe7bf0daa5e13e403124d1cb289672; asc 8   0x10  0x02) {    >@1$  ( r; (total 412 bytes);
 6: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 3902838, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
36 lock struct(s), heap size 3296, 39 row lock(s), undo log entries 48
MySQL thread id 85931, OS thread handle 8108, query id 34130125 DESKTOP-AJ1S2TF 192.168.20.191 root update
INSERT INTO device_alarm (did, `date`, adas) VALUES('1810534', '20220222', 0x46000a04307830321204307830322a0430783032320430783030404349a2410a9e42aa424051a2410a9e42aa42405895c7d2900662120a0e33353333333433303338333633381005),('2000041', '20220222', 0x46000a04307830321204307830312a04307830323204307830304020491920d1048a343f40511920d1048a343f40589ac7d2900662120a0e33303334333133313334333333391005),('1800684', '20220222', 0x48000a0430783032120430783031200b2a0430783031320430783030402e498bfcfa2136b24240518bfcfa2136b2424058a5c7d2900662120a0e33363338333433393336333133381005),('1030014', '20220222', 0x48000a043078303112043078303120062a0430783030320430783030402c4945b8c9a832ac38405145b8c9a832ac38405892c7d2900662120a0e33303331333433303333333433311005),('2000024', '20220222', 0x46000a04307830321204307830312a0430783032320430783030401f49284696ccb100374051284696ccb10037405897c7d2900662120a0e33303332333
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 682 page no 31 n bits 112 index PRIMARY of table `httpserver`.`device_alarm` /*  `p20220223` */ trx id 3902838 lock_mode X locks rec but not gap
Record lock, heap no 42 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 11; hex 3430373336373431363336; asc 4073674;;
 1: len 3; hex 8fcc56; asc   V;;
 2: len 6; hex 0000003b8d76; asc    ; v;;
 3: len 7; hex 5500000bba09b8; asc U      ;;
 4: len 30; hex 46000a04307830321204307830312a0430783031320430783030403149d1; asc F   0x02  0x01* 0x012 0x00@1I ; (total 576 bytes);
 5: len 30; hex 38000a043078313012043078303229fe7bf0daa5e13e403124d1cb289672; asc 8   0x10  0x02) {    >@1$  ( r; (total 412 bytes);
 6: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 682 page no 136 n bits 120 index PRIMARY of table `httpserver`.`device_alarm` /*  `p20220223` */ trx id 3902838 lock_mode X locks rec but not gap waiting
Record lock, heap no 50 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 11; hex 3138373030383530303738; asc 1870085;;
 1: len 3; hex 8fcc56; asc   V;;
 2: len 6; hex 0000003b8d75; asc    ; u;;
 3: len 7; hex 540000223f2ba1; asc T  "?+ ;;
 4: len 30; hex 46000a04307830321204307830312a0430783032320430783030402f4983; asc F   0x02  0x01* 0x022 0x00@/I ; (total 360 bytes);
 5: len 30; hex 3a000a04307830341204307830312027292ae109bdfe0e4340317a8b87f7; asc :   0x04  0x01 ')*     C@1z   ; (total 602 bytes);
 6: SQL NULL;

*** WE ROLL BACK TRANSACTION (1)

The lock is lock_mode X locks rec but not gap, it's too strange for me to undertand it. Why did the dead lock happen?