mysql rc_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.00sec)

mysql> create table t (id int primary key, name int, key(name))engine=innodb;

Query OK,0 rows affected (0.24sec)

.......

mysql> select * fromt;+----+------+

| id | name |

+----+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

+----+------+

7 rows in set (0.00sec)

mysql> set autocommit=0;

Query OK,0 rows affected (0.00sec)

mysql> show variables like 'autocommit';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set (0.03sec)

mysql> begin;

Query OK,0 rows affected (0.00sec)

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.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| 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.00sec)

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.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| 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 inshare mode;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_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 inshare mode;

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_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.00sec)

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);

ERROR1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> select * frominformation_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.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> 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.79sec)

Records:0 Duplicates: 0 Warnings: 0

还有一个需要注意的地方是，如果不加for update，则并发插入时，都会插入新数据，client1：

mysql> begin;

Query OK,0 rows affected (0.00sec)

mysql> select * fromt;+----+------+

| id | name |

+----+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

+----+------+

8 rows in set (0.00sec)

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.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| 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.00sec)

mysql> commit;

Query OK,0 rows affected (0.03sec)

mysql> select * fromt;+----+------+

| 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.00sec)

mysql> select * fromt;+----+------+

| id | name |

+----+------+

| 1 | 1 |

| 2 | 2 |

| 3 | 3 |

| 4 | 4 |

| 5 | 5 |

| 6 | 6 |

| 7 | 7 |

| 8 | 8 |

+----+------+

8 rows in set (0.00sec)

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.00sec)

Records:1 Duplicates: 0 Warnings: 0mysql> select * fromt;+----+------+

| 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.00sec)

mysql> commit;

Query OK,0 rows affected (0.04sec)

mysql> select * fromt;+----+------+

| 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)

可见，根本起不到不存在则插入的效果。