Insert statement need to lock primary key or unique index

[winkyao]

If we have a table:

CREATE TABLE `test` (
      `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
      `test1` int UNIQUE,
      PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

if we exec the below transactions in mysql in two clients:

client 1:

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

mysql> insert into test(id, test1) values(1, 1);
Query OK, 1 row affected (0.00 sec)

client 2:

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

mysql> insert into test(id, test1) values(1, 2);
blocking

the client2 will block till client 1 commit; and client 2 will failed with a duplicate key error.

But in tiDB, the client1 and client 2 will success both, because tidb don't lock any key in insert statement exec func. I have tried in tidb, and I got the result:

mysql> select * from test;
+----+-------+
| id | test1 |
+----+-------+
|  1 |     1 |
|  1 |     2 |
+----+-------+
2 rows in set (0.00 sec)

[ngaut]

Confirmed, thank you @winkyao