TiDB Transactions: Both Tables Have Unique Indexes, No Warning for Unique Index Conflict on First Table Insert, Second Table Not Inserted, Entire Transaction Succeeds Without 1062 Warning

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

Original topic: tidb事务:两张表都有唯一索引,第一张表插入唯一索引冲突没任何提示,第二张表没插入,整个事务执行成功 没提示1062

| username: TiDBer_JtT4juYQ

[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?

| username: 随缘天空 | Original post link

Please send a screenshot of your operation.

| username: Kongdom | Original post link

Please provide the TiDB version and reproducible script. Let’s verify it together~

| username: TiDBer_JtT4juYQ | Original post link

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.

| username: TiDBer_JtT4juYQ | Original post link

Replied. How did I operate the table and code?

| username: TiDBer_JtT4juYQ | Original post link

Posted it!

| username: Kongdom | Original post link

:joy: 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?

| username: TiDBer_JtT4juYQ | Original post link

This is Go code using the Gorm framework.

| username: 随缘天空 | Original post link

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.

| username: dba远航 | Original post link

Suspect that the current value was inserted instead of the next value.

| username: Kongdom | Original post link

:joy: No need to add, I’m a .Net developer and haven’t used Go language. You can check with others in the community.

| username: zhaokede | Original post link

Are there logs in the database?

| username: TiDBer_JtT4juYQ | Original post link

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 :joy_cat:

| username: TiDBer_JtT4juYQ | Original post link

No, the database has that data. Both our DBA and I have checked.

| username: forever | Original post link

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 | Original post link

They are two identical pieces of data, and the names of my tables are fixed.

| username: forever | Original post link

If there are two identical values, then it’s not a code issue. Can it be reproduced using SQL?

| username: TiDBer_JtT4juYQ | Original post link

You mean that I should not use gorm and directly use TiDB’s driver for Go, manually start a transaction, and execute the corresponding SQL, right?

| username: forever | Original post link

Yes, just use SQL to test it directly to avoid program interference.

| username: oceanzhang | Original post link

The code clearly has issues.