mysql RC下不存在则插入
2024-09-04 14:21:06
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)
可见，根本起不到不存在则插入的效果。
最新文章
- iOS 整理笔记 获取手机信息（UIDevice、NSBundle、NSLocale）
- Mastering Web Application Development with AngularJS 读书笔记(二)
- Spring-MongoDB简单操作
- 1452: [JSOI2009]Count - BZOJ
- Netty多线程处理机制
- [转] Webpack 入门指迷
- JS 拼凑字符串
- 代码对齐 分类： C#小技巧 2014-04-17 14:45 166人阅读 评论(0) 收藏
- 转 Android - 文件操作
- Delphi 全面控制Windows任务栏
- .NET平台和C#语言
- java.lang.NoSuchMethodError: javax.persistence.OneToMany.orphanRemoval()Z
- 关于C++异常机制的笔记(SEH, try-catch)
- CodeForces 735D Taxes
- UVa 10986 - Sending email
- 在QComboBox的基础上实现复选功能
- nginx中支持.htaccess并禁止php在特定目录无法运行
- 【Android 应用开发】 FastJson 使用详解
- PyCharm创建自定义代码段（JetBrains系列通用）
- Excel复制粘贴假死