mysql RC下不存在则插入
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)
可见，根本起不到不存在则插入的效果。
