Executing multiple "insert into on duplicate key update" statements using transactions results in a deadlock error

translator_bot · June 25, 2024, 1:16pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 多个insert into on duplicate key update语句用事务的方式执行, 报deadlock错误

| username: egmkang

【TiDB Usage Environment】
Test Environment
【TiDB Version】
v6.5.8 Docker Version
【Reproduction Path】
Hard to reproduce, occurred only once in the intranet (with fewer people)
【Encountered Problem: Problem Phenomenon and Impact】
SQL statement did not execute successfully
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】

The SQL statement is roughly written as:

begin;
insert into on duplicate key update ;
insert into on duplicate key update ;
insert into on duplicate key update ;
commit;

The actual insert SQL statement is:

insert into `t_building_v2` ( `roleid` , `buildingid` , `apprenticesjson` , `fairysjson` , `itemsjson` , `level` , `upgradeendtime` , `lastcollecttime` , `lastautoclicktime` ) 
values ( ... ) 
on duplicate key update 
`apprenticesjson` = values ( `apprenticesjson` ) , 
`fairysjson` = values ( `fairysjson` ) , 
`itemsjson` = values ( `itemsjson` ) , 
`level` = values ( `level` ) , 
`upgradeendtime` = values ( `upgradeendtime` ) , 
`lastcollecttime` = values ( `lastcollecttime` ) , 
`lastautoclicktime` = values ( `lastautoclicktime` )

By querying the recent deadlocks with select * from information_schema.deadlocks;, it was found:

DEADLOCKS

DEADLOCK_ID	OCCUR_TIME	TRY_LOCK_TRX_ID	CURRENT_SQL_DIGEST	CURRENT_SQL_DIGEST_TEXT	KEY	KEY_INFO	TRX_HOLDING_LOCK
1	2024-06-24 02:59:31.051365	450677432920571911	60ab95dfb3e20101b23f4eff38da7edd7a4e46ba60d4554355ba8645f7d4b179	insert into `t_building_v2` ( `roleid` , `buildingid` , `apprenticesjson` , `fairysjson` , `itemsjson` , `level` , `upgradeendtime` , `lastcollecttime` , `lastautoclicktime` ) values ( … ) on duplicate key update `apprenticesjson` = values ( `apprenticesjson` ) , `fairysjson` = values ( `fairysjson` ) , `itemsjson` = values ( `itemsjson` ) , `level` = values ( `level` ) , `upgradeendtime` = values ( `upgradeendtime` ) , `lastcollecttime` = values ( `lastcollecttime` ) , `lastautoclicktime` = values ( `lastautoclicktime` )	74800000000000023C5F72038000000000041ECC0380000000000003E9	{“db_name”:“yooserver”,“table_name”:“t_building_v2”,“handle_type”:“common”,“handle_value”:“{270028, 1001}”,“db_id”:355,“table_id”:572}	450677432920571910
1	2024-06-24 02:59:31.051365	450677432920571910	60ab95dfb3e20101b23f4eff38da7edd7a4e46ba60d4554355ba8645f7d4b179	insert into `t_building_v2` ( `roleid` , `buildingid` , `apprenticesjson` , `fairysjson` , `itemsjson` , `level` , `upgradeendtime` , `lastcollecttime` , `lastautoclicktime` ) values ( … ) on duplicate key update `apprenticesjson` = values ( `apprenticesjson` ) , `fairysjson` = values ( `fairysjson` ) , `itemsjson` = values ( `itemsjson` ) , `level` = values ( `level` ) , `upgradeendtime` = values ( `upgradeendtime` ) , `lastcollecttime` = values ( `lastcollecttime` ) , `lastautoclicktime` = values ( `lastautoclicktime` )	74800000000000023C5F72038000000000041ECC038000000000002329	{“db_name”:“yooserver”,“table_name”:“t_building_v2”,“handle_type”:“common”,“handle_value”:“{270028, 9001}”,“db_id”:355,“table_id”:572}	450677432920571911
2	2024-06-24 12:32:28.451232	450686444751290371	60ab95dfb3e20101b23f4eff38da7edd7a4e46ba60d4554355ba8645f7d4b179	insert into `t_building_v2` ( `roleid` , `buildingid` , `apprenticesjson` , `fairysjson` , `itemsjson` , `level` , `upgradeendtime` , `lastcollecttime` , `lastautoclicktime` ) values ( … ) on duplicate key update `apprenticesjson` = values ( `apprenticesjson` ) , `fairysjson` = values ( `fairysjson` ) , `itemsjson` = values ( `itemsjson` ) , `level` = values ( `level` ) , `upgradeendtime` = values ( `upgradeendtime` ) , `lastcollecttime` = values ( `lastcollecttime` ) , `lastautoclicktime` = values ( `lastautoclicktime` )	74800000000000023C5F72038000000000061AA70380000000000003E9	{“db_name”:“yooserver”,“table_name”:“t_building_v2”,“handle_type”:“common”,“handle_value”:“{400039, 1001}”,“db_id”:355,“table_id”:572}	450686444750766080
2	2024-06-24 12:32:28.451232	450686444750766080	60ab95dfb3e20101b23f4eff38da7edd7a4e46ba60d4554355ba8645f7d4b179	insert into `t_building_v2` ( `roleid` , `buildingid` , `apprenticesjson` , `fairysjson` , `itemsjson` , `level` , `upgradeendtime` , `lastcollecttime` , `lastautoclicktime` ) values ( … ) on duplicate key update `apprenticesjson` = values ( `apprenticesjson` ) , `fairysjson` = values ( `fairysjson` ) , `itemsjson` = values ( `itemsjson` ) , `level` = values ( `level` ) , `upgradeendtime` = values ( `upgradeendtime` ) , `lastcollecttime` = values ( `lastcollecttime` ) , `lastautoclicktime` = values ( `lastautoclicktime` )	74800000000000023C5F72038000000000061AA7038000000000001771	{“db_name”:“yooserver”,“table_name”:“t_building_v2”,“handle_type”:“common”,“handle_value”:“{400039, 6001}”,“db_id”:355,“table_id”:572}	450686444751290371

Table creation statement:

CREATE TABLE `t_building_v2` (
	`RoleId` BIGINT(20) NOT NULL,
	`BuildingId` INT(11) NOT NULL,
	`ApprenticesJson` LONGTEXT NOT NULL COLLATE 'utf8mb4_bin',
	`FairysJson` LONGTEXT NOT NULL COLLATE 'utf8mb4_bin',
	`ItemsJson` LONGTEXT NOT NULL COLLATE 'utf8mb4_bin',
	`Level` INT(11) NOT NULL,
	`UpgradeEndTime` BIGINT(20) NOT NULL,
	`LastCollectTime` BIGINT(20) NOT NULL,
	`LastAutoClickTime` BIGINT(20) NOT NULL DEFAULT '0',
	PRIMARY KEY (`RoleId`, `BuildingId`) USING BTREE
)
COLLATE='utf8mb4_bin'
ENGINE=InnoDB
;

During execution, ADO.NET threw an exception with the message: Deadlock found when trying to get lock; try restarting transaction
How can this problem be resolved?