Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: Tidb insert on duplicate key 或 replace into 报主键冲突异常
|
username: juecong
[TiDB Usage Environment] Production Environment
[TiDB Version]
7.1.1
[Reproduction Path]
When the program writes in multiple threads, it reports Duplicate entry ‘81432804’ for key ‘change_records.PRIMARY’, but the program controls that the same primary key will only be updated once within a few seconds.
[Encountered Problem: Problem Phenomenon and Impact]
The table that reports the primary key conflict does not have a primary key set, it is a unique key. Copying the SQL and executing it manually can insert and execute normally.
Table structure:
CREATE TABLE change_records (
id bigint(20) NOT NULL COMMENT ‘Serial number’,
u_id char(32) NOT NULL DEFAULT ‘’ COMMENT ‘Unique key’,
eid char(36) NOT NULL COMMENT ‘eid’,
seq_no int(11) NOT NULL COMMENT ‘Serial number’,
after_content mediumtext DEFAULT NULL COMMENT ‘Content after change’,
before_content mediumtext DEFAULT NULL COMMENT ‘Content before change’,
change_date varchar(255) NOT NULL DEFAULT ‘’ COMMENT ‘Change date’,
change_item varchar(2000) NOT NULL DEFAULT ‘’ COMMENT ‘Change name’,
update_time datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT ‘Update time’,
UNIQUE KEY eid_u_id (eid,u_id),
KEY key_row_update_time (row_update_time),
KEY eid_u_tags (eid,u_tags),
KEY key_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=8 */ ;
Insert statement:
INSERT INTO change_records (seq_no, special_type, eid, update_time, change_date, source, type, u_id, id, before_content, after_content, change_item)
VALUES (-1, 0, ‘0c6c80b6-dce3-422b-9c1c’, ‘2024-04-16 01:54:51’, ‘2015-05-15’, ‘1’, ‘Personnel change’, ‘d1d75004c7261a85c09c66375082d990’, 7085492, ‘Zhang San’, ‘Li Si’, ‘Personnel’)
ON DUPLICATE KEY UPDATE seq_no = VALUES(seq_no), special_type = VALUES(special_type), eid = VALUES(eid), update_time = VALUES(update_time), change_date = VALUES(change_date), source = VALUES(source), type = VALUES(type), u_id = VALUES(u_id), id = VALUES(id), before_content = VALUES(before_content), after_content = VALUES(after_content), change_item = VALUES(change_item);
Previously using REPLACE INTO also reported the same primary key conflict.
|
username: 
|
username: 
|
username: 
|
username: