【 TiDB 使用环境】
测试环境
【 TiDB 版本】
v6.5.8 docker版本
【复现路径】
不好复现, 内网只出现过一次(人数比较少)
【遇到的问题：问题现象及影响】
SQL语句没有成功的执行
【资源配置】进入到 TiDB Dashboard -集群信息 (Cluster Info) -主机(Hosts) 截图此页面
【附件：截图/日志/监控】
SQL语句的写法, 大概是
begin;
insert into on duplicate key update ;
insert into on duplicate key update ;
insert into on duplicate key update ;
commit;
真正的insert SQL语句是:
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` )
通过查询最近的死锁select * from information_schema.deadlocks; 得知:
DEADLOCKS
| DEADLOCK_ID | OCCUR_TIME | RETRYABLE | 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 | 0 | 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 | 0 | 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 | 0 | 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 | 0 | 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 |
表格的创建语句:
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
;
执行的时候, ADO.NET抛出异常, 异常信息是: Deadlock found when trying to get lock; try restarting transaction
请问这个问题该如何解决?
估计是开发不规范，建议让开发排查一下逻辑