Table like that:

CREATE TABLE `test_un_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_test_name` (`name`)
) ENGINE=InnoDB

Transaction1:

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

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

Transaction2:

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

mysql> insert into test_un_index(id) values (2);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

Why there will be a lock?

And if I change the index to not unique, the lock will not happen, why?

Thanks a lot!

I want to use “show engine innoDB status” to see the lock detail, but not found.