Tidb: An insert statement which violate key constraint can be committed successfully

[zyguan]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists t;
-- init
create table t (
    c_int       int auto_increment,
    c_double    double,
    c_decimal   decimal(12, 6),
    c_string    varchar(40) collate utf8_general_ci,
    c_datetime  datetime,
    c_timestamp timestamp,
    c_enum      enum ('a', 'b', 'c', 'd', 'e'),
    c_set       set ('1', '2', '3', '4', '5'),
    c_json      json,
    primary key (c_int),
    unique key (c_string),
    key (c_enum),
    key (c_set),
    key (c_timestamp),
    key (c_datetime),
    key (c_decimal)
);
insert into t (`c_int`, `c_double`, `c_decimal`, `c_string`, `c_datetime`, `c_timestamp`, `c_enum`, `c_set`, `c_json`) values
  (1, 0.718657, 91.150000, 'red curtain', '2020-01-02 02:00:00', '2020-01-03 21:11:41', 'd', '1', '{\"datetime\": \"2019-12-27 05:00:00\", \"enum\": \"a\", \"int\": 20, \"set\": \"1,2,3\", \"str\": \"ivory crest\"}')
, (2, 0.070684, 74.820000, 'gold sloth', '2019-12-26 03:00:00', '2019-12-26 18:02:54', 'e', '2', '{\"datetime\": \"2020-01-04 02:00:00\", \"enum\": \"a\", \"int\": 16, \"set\": \"5\", \"str\": \"cream spider\"}')
, (3, 0.490952, 85.290000, 'blue roach', '2020-01-03 14:00:00', '2019-12-25 03:47:00', 'd', '1,4', '{\"datetime\": \"2020-01-04 12:00:00\", \"enum\": \"c\", \"int\": 18, \"set\": \"2\", \"str\": \"light seer\"}')
, (4, 0.427215, 55.340000, 'cerulean ant', '2020-01-03 18:00:00', '2019-12-29 17:11:56', 'a', '1,2,5', '{\"datetime\": \"2019-12-25 21:00:00\", \"enum\": \"d\", \"int\": 11, \"set\": \"4,5\", \"str\": \"orange cat\"}')
, (5, 0.88423, 80.510000, 'black seer', '2020-01-07 06:00:00', '2020-01-06 09:41:17', 'e', '1,3', '{\"datetime\": \"2020-01-04 04:00:00\", \"enum\": \"c\", \"int\": 8, \"set\": \"4,5\", \"str\": \"light bard\"}')
, (6, 0.263039, 6.790000, 'orange crest', '2020-01-04 12:00:00', '2019-12-27 05:27:06', 'e', '1,4', '{\"datetime\": \"2020-01-03 20:00:00\", \"enum\": \"b\", \"int\": 3, \"set\": \"2,5\", \"str\": \"gray warrior\"}')
, (7, 0.444086, 47.430000, 'cyan bard', '2020-01-06 06:00:00', '2020-01-04 04:39:49', 'd', '5', '{\"datetime\": \"2020-01-03 06:00:00\", \"enum\": \"e\", \"int\": 16, \"set\": \"1,4\", \"str\": \"light fly\"}')
, (8, 0.709668, 94.560000, 'yellow speaker', '2020-01-04 04:00:00', '2020-01-02 19:24:03', 'd', '2,3,4,5', '{\"datetime\": \"2019-12-30 13:00:00\", \"enum\": \"e\", \"int\": 4, \"set\": \"1,2,3\", \"str\": \"green vulture\"}')
, (9, 0.899852, 80.660000, 'pink warrior', '2020-01-04 16:00:00', '2020-01-02 15:02:03', 'd', '1,3,5', '{\"datetime\": \"2019-12-27 13:00:00\", \"enum\": \"e\", \"int\": 14, \"set\": \"1,4,5\", \"str\": \"dark kangaroo\"}')
, (10, 0.277686, 24.880000, 'black cat', '2020-01-06 14:00:00', '2020-01-06 03:57:43', 'd', '1,3', '{\"datetime\": \"2020-01-06 10:00:00\", \"enum\": \"c\", \"int\": 6, \"set\": \"2\", \"str\": \"gray carpet\"}')
, (11, 0.365868, 97.450000, 'azure bard', '2019-12-29 03:00:00', '2019-12-31 09:48:56', 'a', '4,5', '{\"datetime\": \"2019-12-25 19:00:00\", \"enum\": \"c\", \"int\": 3, \"set\": \"1,2,4,5\", \"str\": \"white singer\"}')
, (12, 0.136982, 14.990000, 'green seer', '2019-12-29 07:00:00', '2019-12-25 03:56:42', 'c', '1,3,4', '{\"datetime\": \"2019-12-27 05:00:00\", \"enum\": \"d\", \"int\": 6, \"set\": \"4\", \"str\": \"navy witch\"}')
, (13, 0.716163, 85.410000, 'gray scourge', '2020-01-03 06:00:00', '2019-12-29 17:30:24', 'e', '3,5', '{\"datetime\": \"2019-12-29 21:00:00\", \"enum\": \"b\", \"int\": 18, \"set\": \"1,2,3,4\", \"str\": \"silver sloth\"}')
, (14, 0.319057, 73.440000, 'dark fly', '2020-01-06 04:00:00', '2020-01-05 02:16:03', 'b', '3,4,5', '{\"datetime\": \"2019-12-26 05:00:00\", \"enum\": \"e\", \"int\": 2, \"set\": \"1,2,3,4\", \"str\": \"ivory fly\"}')
, (15, 0.45908, 77.550000, 'silver vulture', '2019-12-29 09:00:00', '2019-12-30 16:06:20', 'd', '3', '{\"datetime\": \"2019-12-31 07:00:00\", \"enum\": \"a\", \"int\": 20, \"set\": \"2,3,4\", \"str\": \"dark sloth\"}')
, (16, 0.846275, 6.020000, 'red kangaroo', '2019-12-30 03:00:00', '2020-01-02 15:00:37', 'a', '3,5', '{\"datetime\": \"2020-01-05 20:00:00\", \"enum\": \"b\", \"int\": 6, \"set\": \"1,5\", \"str\": \"white vulture\"}')
, (20, 0.939371, 47.980000, 'gray mistress', '2020-01-07 02:00:00', '2019-12-28 12:00:28', 'd', '1', '{\"datetime\": \"2020-01-05 02:00:00\", \"enum\": \"b\", \"int\": 6, \"set\": \"2,4\", \"str\": \"cerulean curtain\"}')
;
-- txn
begin;
update t set c_string = 'gray mistress' where c_int = 10;
insert into t values (10, 0.133544, 0.39, 'cyan fly', '2020-01-05 20:00:00', '2020-01-02 14:21:55', 'c', '1,2,5', '{"int":18,"str":"cyan trader","datetime":"2020-01-07 08:00:00","enum":"b","set":"2,3"}');
commit;
select * from t where c_int = 10;

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

The last insert statement of the transaction should failed with ERROR 1062 (23000): Duplicate entry '10' for key 'PRIMARY'.

3. What did you see instead (Required)

mysql> begin;
Query OK, 0 rows affected (0.05 sec)

mysql> update t set c_string = 'gray mistress' where c_int = 10;
ERROR 1062 (23000): Duplicate entry 'gray mistress' for key 'c_string'

mysql> insert into t values (10, 0.133544, 0.39, 'cyan fly', '2020-01-05 20:00:00', '2020-01-02 14:21:55', 'c', '1,2,5', '{"int":18,"str":"cyan trader","datetime":"2020-01-07 08:00:00","enum":"b","set":"2,3"}');
Query OK, 1 row affected (0.05 sec)

mysql> commit;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from t where c_int = 10;
+-------+----------+-----------+----------+---------------------+---------------------+--------+-------+-------------------------------------------------------------------------------------------------+
| c_int | c_double | c_decimal | c_string | c_datetime          | c_timestamp         | c_enum | c_set | c_json                                                                                          |
+-------+----------+-----------+----------+---------------------+---------------------+--------+-------+-------------------------------------------------------------------------------------------------+
|    10 | 0.133544 |  0.390000 | cyan fly | 2020-01-05 20:00:00 | 2020-01-02 14:21:55 | c      | 1,2,5 | {"datetime": "2020-01-07 08:00:00", "enum": "b", "int": 18, "set": "2,3", "str": "cyan trader"} |
+-------+----------+-----------+----------+---------------------+---------------------+--------+-------+-------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

Note that the row where c_int = 10 has been updated by the transaction unexpectly.

4. Affected version (Required)

master, v4.0.4

5. Root Cause Analysis

[zyguan]

/label component/transaction

[cfzjywxk]

A minimal case to reproduce this problem

drop table if exists t1;
create table t1(c1 int primary key, c2 int, c3 int, unique key uk(c2));
insert into t1 values(1, 2, 3);
insert into t1 values(10, 20, 30);



TiDB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set c2 = 20 where c1 = 1;
ERROR 1062 (23000): Duplicate entry '20' for key 'uk'
mysql> insert into t1 values(1, 15, 300); 
Query OK, 1 row affected (0.00 sec)

mysql> commit; 

mysql> select * from t1;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 |   15 |  300 |
| 10 |   20 |   30 |
+----+------+------+
2 rows in set (0.00 sec)


MySQL 
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set c2 = 20 where c1 = 1;
ERROR 1062 (23000): Duplicate entry '20' for key 'uk'
mysql> insert into t1 values(1, 15, 300);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 |    2 |    3 |
| 10 |   20 |   30 |
+----+------+------+
2 rows in set (0.00 sec)

[jackysp]

introduced by https://github.com/pingcap/tidb/pull/15257 for the range scan executor

drop table if exists t1;
create table t1(c1 int primary key, c2 int, c3 int, unique key uk(c2));
insert into t1 values(1, 2, 3);
insert into t1 values(10, 20, 30);



TiDB
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set c2 = 20 where c1 >= 1 and c1 < 5;
ERROR 1062 (23000): Duplicate entry '20' for key 'uk'
mysql> insert into t1 values(1, 15, 300); 
Query OK, 1 row affected (0.00 sec)