主要看并发事务中不存在则插入（只有key索引）的阻塞情况。
表定义：
1mysql> desc user; 2+-------------+------------------+------+-----+-------------------+----------------+ 3| Field | Type | Null | Key | Default | Extra | 4+-------------+------------------+------+-----+-------------------+----------------+ 5| id | int(10) unsigned | NO | PRI | NULL | auto_increment | 6| name | varchar(50) | NO | MUL | NULL | | 7| password | char(20) | NO | | NULL | | 8| regist_time | timestamp | NO | | CURRENT_TIMESTAMP | | 9+-------------+------------------+------+-----+-------------------+----------------+ 104 rows in set (0.00 sec)
事务隔离级别：RR
mysql版本：5.7
client1：
1mysql> begin; 2Query OK, 0 rows affected (0.00 sec) 3 4mysql> select * from user; 5+----+------+----------+---------------------+ 6| id | name | password | regist_time | 7+----+------+----------+---------------------+ 8| 1 | a | a | 2018-03-11 16:32:43 | 9| 2 | b | b | 2018-03-11 16:33:09 | 10| 3 | c | c | 2018-03-11 16:33:39 | 11+----+------+----------+---------------------+ 123 rows in set (0.00 sec) 13 14mysql> insert into user(name,password) select 'd','d' from dual where not exist (select name from user where name='d'); 15Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0 16 17mysql> select * from user; 18+----+------+----------+---------------------+ 19| id | name | password | regist_time | 20+----+------+----------+---------------------+ 21| 1 | a | a | 2018-03-11 16:32:43 | 22| 2 | b | b | 2018-03-11 16:33:09 | 23| 3 | c | c | 2018-03-11 16:33:39 | 24| 4 | d | d | 2018-03-11 17:03:35 | 25+----+------+----------+---------------------+ 264 rows in set (0.00 sec)
然后启动client2：
1mysql> begin; 2Query OK, 0 rows affected (0.00 sec) 3 4mysql> select * from user; 5+----+------+----------+---------------------+ 6| id | name | password | regist_time | 7+----+------+----------+---------------------+ 8| 1 | a | a | 2018-03-11 16:32:43 | 9| 2 | b | b | 2018-03-11 16:33:09 | 10| 3 | c | c | 2018-03-11 16:33:39 | 11+----+------+----------+---------------------+ 123 rows in set (0.00 sec) 13 14mysql> select * from user where name='d'; 15Empty set (0.02 sec) 16 17mysql> insert into user (name,password) select 'd','d' from dual where not exists (select name from user where name='d');
client2 执行“ insert into user (name,password) select 'd','d' from dual where not exists (select name from user where name='d'); ”出现阻塞，直到超时或client1 commit。
client2 直接执行插入操作则不会阻塞：
1mysql> insert into user(name, password) values ('d','d'); 2Query OK, 1 row affected (0.00 sec)
client2 执行：
mysql> insert into user (name,password) select 'e','e' from dual where not exists (select name from user where name='e');
也会出现阻塞。但是执行：
1mysql> insert into user (name,password) select '12','12' from dual where not exists (select name from user where name='12'); 2Query OK, 1 row affected (0.02 sec) 3Records: 1 Duplicates: 0 Warnings: 0
并不会阻塞。
另：如果已经存在name='d'的数据，client1执行"insert not exists"后并不会插入也不会加锁，client2执行时也不会阻塞。
查看锁（client2 插入'd'时的情况）：
1mysql> select * from information_schema.innodb_locks; 2+-------------------------+-----------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ 3| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | 4+-------------------------+-----------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ 5| 422016582501824:462:4:8 | 422016582501824 | S | RECORD | `test1`.`user` | name | 462 | 4 | 8 | 'd', 11 | 6| 162094:462:4:8 | 162094 | X | RECORD | `test1`.`user` | name | 462 | 4 | 8 | 'd', 11 | 7+-------------------------+-----------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+ 82 rows in set, 1 warning (0.02 sec)
client2 当插入'z'时也会阻塞，但lock_data还会是：
1mysql> select * from information_schema.innodb_locks; 2+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------------+ 3| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | 4+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------------+ 5| 162131:462:4:1 | 162131 | X | RECORD | `test1`.`user` | name | 462 | 4 | 1 | supremum pseudo-record | 6| 162094:462:4:1 | 162094 | S | RECORD | `test1`.`user` | name | 462 | 4 | 1 | supremum pseudo-record | 7+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------------+ 82 rows in set, 1 warning (0.00 sec)
也就是'z'是加锁的上界，插入'x'和'~'也是这种情况。
之所以'12'不会锁，'d'和其以后的都会锁，是因为mysql为了防止幻读，还锁住了下一行，因为最大的是'd'，所以锁住区域为（'d', +∞），另一个区域是（'c', 'd'）。如果插入的不是这个区域的都不会阻塞。
RC和RR加锁区别请见：RR和RC复合语句加锁
当client2 插入'A'、'B'时居然不阻塞也插入不了：
1mysql> insert into user (name,password) select 'A','A' from dual where not exists (select name from user where name='A'); 2Query OK, 0 rows affected (0.00 sec) 3Records: 0 Duplicates: 0 Warnings: 0 4 5mysql> insert into user (name,password) select 'B','B' from dual where not exists (select name from user where name='B'); 6Query OK, 0 rows affected (0.00 sec) 7Records: 0 Duplicates: 0 Warnings: 0
client1也插入不了'A'，只有直接执行时才可以：
1mysql> insert into user (name,password) select 'A','A' from dual where not exists (select name from user where name='A'); 2Query OK, 0 rows affected (0.00 sec) 3Records: 0 Duplicates: 0 Warnings: 0 4 5mysql> insert into user(name,password) values('A','A'); 6Query OK, 1 row affected (0.00 sec)
之所以出现无法插入'A'、'B'，是因为不区分大小写，测试一下便知：
1mysql> select * from user where name='a'; 2+----+------+----------+---------------------+ 3| id | name | password | regist_time | 4+----+------+----------+---------------------+ 5| 1 | a | a | 2018-03-11 16:32:43 | 6| 44 | A | A | 2018-03-11 20:56:42 | 7+----+------+----------+---------------------+ 82 rows in set (0.00 sec)
要想区分大小写，建表时需要相应设置，也可以在查询时使用：
1mysql> select * from user where binary name='a'; 2+----+------+----------+---------------------+ 3| id | name | password | regist_time | 4+----+------+----------+---------------------+ 5| 1 | a | a | 2018-03-11 16:32:43 | 6+----+------+----------+---------------------+ 71 row in set (0.01 sec)
另：on duplicate key只适用于unique key，如果不是unique，总是会插入
mysql> insert into user(name,password) values('d','d') on duplicate key update password='e';
这时会插入一条name='d',password='d'的记录。