Will MySQL lock the index row when executing a concurrent insert with same index value?

user_9012649 0
Viewed 31

I have a table like this:

create table `sample` (
  id int auto_increment,
  sid int,
  message varchar(100),
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_sid` (`sid`)
);

sid is not an unique index.

When I executing insert, all record will have the same value of sid. Will these insert statement lock the row by insert intention lock as they all have the same insert index?

locks indexing innodb mysql
1 Answers

Test it!

Window 1:

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

mysql> insert into sample set sid = 42;
Query OK, 1 row affected (0.01 sec)

Window 2:

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

mysql> insert into sample set sid = 42;
Query OK, 1 row affected (0.00 sec)

Both are able to insert. There's no contention.

Bill_Karwin_208600
Related