[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed that caused the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]
Both tables have unique indexes. When inserting into the first table, there was a unique index conflict but no prompt. The second table was not inserted, and the entire transaction executed successfully without a 1062 prompt.
The operations were performed using GORM. During the transaction, when inserting into the first table, I checked the error. Inserting data that conflicted with the unique index did not result in an error. I also checked the error for the transaction execution result, and there was no 1062 prompt. I’m quite curious, isn’t this a transaction operation? It should prompt an error, right?
TiDB version: Server version: 5.7.25-TiDB-v5.0.6 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible
Table 1
CREATE TABLE rdata ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’, mid bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘ID’, tid bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘tID’, key varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘key’, round int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘round’, data varchar(2048) NOT NULL DEFAULT ‘’ COMMENT ‘data’, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘creation time’, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘update time’,
PRIMARY KEY (id),
UNIQUE KEY uniq_mt_key_round (mid,tid,key,round),
KEY idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=39931000 COMMENT=‘Table 1’
CREATE TABLE m_data ( id bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘id’, mid bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘mID’, tid bigint(20) NOT NULL DEFAULT ‘0’ COMMENT ‘tID’, key varchar(64) NOT NULL DEFAULT ‘’ COMMENT ‘key’, round int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘round’, rdata_id bigint(20) unsigned NOT NULL DEFAULT ‘0’ COMMENT ‘rid’, created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘creation time’, updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘update time’,
PRIMARY KEY (id),
UNIQUE KEY uniq_mt_key_round (mid,tid,key,round),
KEY idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=1 COMMENT=‘Table 2’
Execution process
There is already a test data in Table 1.
Code
`err = c.transactionDB.Transaction(func(tx *gorm.DB) error {
userTaskPO, userTaskRightPO, err = c.ReTX(ctx, tx, doo.DependTaskCompletedCount, memberTaskInfo, taskConfigData)
if err != nil {
log.Errorf(ctx, “ReTX %s err %v”, util.ToJson(us), err)
return err
}
return nil
})
func ReTX( ctx,tx,data)err {
// Insert into Table 1, here the unique index conflicts
db := tx.Table(data.TableName())
if err := tx.Create(data).Error; err != nil {
log.Errorf(ctx, “Insert xxxx err %v”, err)
return err
}
// Here update Table 2, the rule is to update if there is a unique conflict
db := tx.Table(data2.TableName())
if err := db.Clauses(clause.OnConflict{
Columns: clause.Column{{Name: “id”}, {Name: “tid”}, {Name: “key”}, {Name: “round”}},
DoUpdates: clause.AssignmentColumns(string{“r_data_id”}),
}).Create(&data2).Error; err != nil {
return err
}
return nil
}
`
The final result is as I said:
Table 1 has data, Table 2 does not.
In the transaction, the unique index conflict occurred when inserting data into Table 1, err was empty, and the data in Table 2 was not successfully inserted either. The entire transaction was committed normally.
Later, I tried adding db.Clauses(clause.OnConflict{DoNothing: true}) to the insertion of Table 1, and only then did the data in Table 2 get written correctly.
This is code, I can’t understand this language.
If you execute SQL statements directly in the database tool, will the performance be the same as code operations?
First, check in the database client whether there is a unique index structure and whether it matches the SQL statement you executed; second, your unique index is built on four fields (mid, tid, key, round), check if there are two rows of completely identical data in the table; third, since your code is being executed, debug and trace it to see the specific execution process, and you can also copy the executed SQL statement so that everyone can help you analyze it.
First point and second point, I have confirmed with the DBA. To verify this issue, I specifically commented out the writes to the second table and started a transaction to write. With the same original data, the result still shows 1062. As long as the two tables are together, it doesn’t work
Is it possible that there are two identical pieces of data in the table without a unique constraint? Could it be that you mistakenly inserted into a different table when passing the table name?
|
username: TiDBer_JtT4juYQ
|
username: 
|
username: 
This is code, I can’t understand this language.
|
username: 
|
username: 
|
username: 
|
username: