UPDATE statement is not blocked by INSERT statement that inserts duplicate row

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* init */ CREATE TABLE t (c1 INT PRIMARY KEY, c2 INT);
/* init */ INSERT INTO t VALUES (1, 1);

/* tx1 */ BEGIN;
/* tx1 */ INSERT INTO t VALUES (1, 5); -- report "ERROR 1062 (23000): Duplicate entry '1' for key 't.PRIMARY'"
/* tx2 */ BEGIN;
/* tx2 */ UPDATE t SET c1 = 2;
/* tx2 */ COMMIT;
/* tx1 */ INSERT INTO t VALUES (1, 10); 
/* tx1 */ COMMIT;
/* tx1 */ SELECT * FROM t; -- [(1, 10), (2, 1)]

2. What did you expect to see? (Required)

In MySQL, the UPDATE statement in transaction 2 is blocked until transaction 1 commits. The final query returns one row [(2, 1)].

3. What did you see instead (Required)

The UPDATE statement is not blocked, and transaction 2 can be committed directly. The final query returns two rows [(1, 10), (2, 1)].

4. What is your TiDB version? (Required)

Release Version: v8.2.0
Edition: Community
Git Commit Hash: 821e491a20fbab36604b36b647b5bae26a2c1418
Git Branch: HEAD
UTC Build Time: 2024-07-05 09:16:25
GoVersion: go1.21.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv

[sayJason]

I think the UPDATE statement in transaction 2 should by blocked by the INSERT statement in transaction 1 due to the primary key, as it happened in MySQL. Could you please explain the design and implementation considerations behind this incompatibility with MySQL in TiDB?

[jebter]

@cfzjywxk PTAL

[cfzjywxk]

@sayJason Unlike MySQL, TiDB will release the relevant lock after the statement execution fails, and MySQL will retain a shared lock on the corresponding record. The behaviour is different.

[sayJason]

@sayJason Unlike MySQL, TiDB will release the relevant lock after the statement execution fails, and MySQL will retain a shared lock on the corresponding record. The behaviour is different.

I got it. Thanks for your reply.