mysql版本：5.7
目的：在RC下，name列上仅有key索引，并发插入name时不出现重复数据
RC不加gap lock，并且复合select语句是不加锁的快照读，导致两个事务同时进行都可插入，测试如下：
client1：
mysql> set tx_isolation='read-committed'; mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec) mysql> create table t (id int primary key, name int, key(name))engine=innodb;
Query OK, 0 rows affected (0.24 sec) ....... mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
+----+------+
7 rows in set (0.00 sec) mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.03 sec) mysql> begin;
Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
+----+------+
8 rows in set (0.00 sec)
client2设置同client1，设置略，然后起事务插入：
mysql> begin;
Query OK, 0 rows affected (0.00 sec) mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 9 | 8 |
+----+------+
8 rows in set (0.00 sec)
可以看到并未阻塞，这不同于RR，在RR下会阻塞，因为加了gap lock。
难道这时候没有加任何锁吗，其实并不是，client1执行如下，并查看锁：
mysql> select name from t where name=8 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 164163:469:4:10 | 164163 | S | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 9 |
| 164168:469:4:10 | 164168 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 9 |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
看看加锁的数据。client2如下：
mysql> select name from t where name=8 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from information_schema.innodb_locks;
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 164168:469:4:9 | 164168 | S | RECORD | `test1`.`t` | name | 469 | 4 | 9 | 8, 8 |
| 164163:469:4:9 | 164163 | X | RECORD | `test1`.`t` | name | 469 | 4 | 9 | 8, 8 |
+----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
看看加锁的数据，可见client1和2都上了锁，是在insert时上的。
那么为了能达到加锁阻塞的目的，可以使用如下方式，client1：
mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8 for update);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
client2则阻塞：
mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8 for update);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> select * from information_schema.innodb_locks;
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 164170:469:4:10 | 164170 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 8 |
| 164169:469:4:10 | 164169 | X | RECORD | `test1`.`t` | name | 469 | 4 | 10 | 8, 8 |
+-----------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.00 sec)
如果client1在client2阻塞时 commit：
mysql> insert into t select 8,8 from dual where not exists (select name from t where name=8 for update);
Query OK, 1 rows affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql> commit;
Query OK, 0 rows affected (0.04 sec)
client2：
mysql> insert into t select 9,8 from dual where not exists (select name from t where name=8 for update);
Query OK, 0 rows affected (4.79 sec)
Records: 0 Duplicates: 0 Warnings: 0
还有一个需要注意的地方是，如果不加for update，则并发插入时，都会插入新数据，client1：
mysql> begin;
Query OK, 0 rows affected (0.00 sec) mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
+----+------+
8 rows in set (0.00 sec) mysql> insert into t select 9,9 from dual where not exists (select name from t where name=9);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
+----+------+
9 rows in set (0.00 sec) mysql> commit;
Query OK, 0 rows affected (0.03 sec) mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 9 |
+----+------+
10 rows in set (0.00 sec)
与client1并发执行的client2：
mysql> begin;
Query OK, 0 rows affected (0.00 sec) mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
+----+------+
8 rows in set (0.00 sec) mysql> insert into t select 10,9 from dual where not exists (select name from t where name=9);
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 10 | 9 |
+----+------+
9 rows in set (0.00 sec) mysql> commit;
Query OK, 0 rows affected (0.04 sec) mysql> select * from t;
+----+------+
| id | name |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
| 7 | 7 |
| 8 | 8 |
| 9 | 9 |
| 10 | 9 |
+----+------+
10 rows in set (0.00 sec)
可见，根本起不到不存在则插入的效果。