mysqlindexinginnodblocks

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

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?

Solution

  • 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.