mysql RR下不存在则插入

Wesley13
• 阅读 673

主要看并发事务中不存在则插入(只有key索引)的阻塞情况。

表定义:

mysql> desc user;
+-------------+------------------+------+-----+-------------------+----------------+
| Field       | Type             | Null | Key | Default           | Extra          |
+-------------+------------------+------+-----+-------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL              | auto_increment |
| name        | varchar(50)      | NO   | MUL | NULL              |                |
| password    | char(20)         | NO   |     | NULL              |                |
| regist_time | timestamp        | NO   |     | CURRENT_TIMESTAMP |                |
+-------------+------------------+------+-----+-------------------+----------------+
4 rows in set (0.00 sec)

事务隔离级别:RR

mysql版本:5.7

client1:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+----------+---------------------+
| id | name | password | regist_time         |
+----+------+----------+---------------------+
|  1 | a    | a        | 2018-03-11 16:32:43 |
|  2 | b    | b        | 2018-03-11 16:33:09 |
|  3 | c    | c        | 2018-03-11 16:33:39 |
+----+------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> insert into user(name,password) select 'd','d' from dual where not exist (select name from user where name='d');
Query OK, 1 row affected (0.00 sec)Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from user;
+----+------+----------+---------------------+
| id | name | password | regist_time         |
+----+------+----------+---------------------+
|  1 | a    | a        | 2018-03-11 16:32:43 |
|  2 | b    | b        | 2018-03-11 16:33:09 |
|  3 | c    | c        | 2018-03-11 16:33:39 |
|  4 | d    | d        | 2018-03-11 17:03:35 |
+----+------+----------+---------------------+
4 rows in set (0.00 sec)

然后启动client2:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+----------+---------------------+
| id | name | password | regist_time         |
+----+------+----------+---------------------+
|  1 | a    | a        | 2018-03-11 16:32:43 |
|  2 | b    | b        | 2018-03-11 16:33:09 |
|  3 | c    | c        | 2018-03-11 16:33:39 |
+----+------+----------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from user where name='d';
Empty set (0.02 sec)

mysql> 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 直接执行插入操作则不会阻塞:

mysql> insert into user(name, password) values ('d','d');
Query 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');

也会出现阻塞。但是执行:

mysql> insert into user (name,password) select '12','12' from dual where not exists (select name from user where name='12');
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

并不会阻塞。

另:如果已经存在name='d'的数据,client1执行"insert not exists"后并不会插入也不会加锁,client2执行时也不会阻塞。

查看锁(client2 插入'd'时的情况):

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 |
+-------------------------+-----------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
| 422016582501824:462:4:8 | 422016582501824 | S         | RECORD    | `test1`.`user` | name       |        462 |         4 |        8 | 'd', 11   |
| 162094:462:4:8          | 162094          | X         | RECORD    | `test1`.`user` | name       |        462 |         4 |        8 | 'd', 11   |
+-------------------------+-----------------+-----------+-----------+----------------+------------+------------+-----------+----------+-----------+
2 rows in set, 1 warning (0.02 sec)

client2 当插入'z'时也会阻塞,但lock_data还会是:

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              |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------------+
| 162131:462:4:1 | 162131      | X         | RECORD    | `test1`.`user` | name       |        462 |         4 |        1 | supremum pseudo-record |
| 162094:462:4:1 | 162094      | S         | RECORD    | `test1`.`user` | name       |        462 |         4 |        1 | supremum pseudo-record |
+----------------+-------------+-----------+-----------+----------------+------------+------------+-----------+----------+------------------------+
2 rows in set, 1 warning (0.00 sec)

也就是'z'是加锁的上界,插入'x'和'~'也是这种情况。

之所以'12'不会锁,'d'和其以后的都会锁,是因为mysql为了防止幻读,还锁住了下一行,因为最大的是'd',所以锁住区域为('d', +∞),另一个区域是('c', 'd')。如果插入的不是这个区域的都不会阻塞。

RC和RR加锁区别请见:RR和RC复合语句加锁

当client2 插入'A'、'B'时居然不阻塞也插入不了:

mysql> insert into user (name,password) select 'A','A' from dual where not exists (select name from user where name='A');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into user (name,password) select 'B','B' from dual where not exists (select name from user where name='B');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

client1也插入不了'A',只有直接执行时才可以:

mysql> insert into user (name,password) select 'A','A' from dual where not exists (select name from user where name='A');
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into user(name,password) values('A','A');
Query OK, 1 row affected (0.00 sec)

之所以出现无法插入'A'、'B',是因为不区分大小写,测试一下便知:

mysql> select * from user where name='a';
+----+------+----------+---------------------+
| id | name | password | regist_time         |
+----+------+----------+---------------------+
|  1 | a    | a        | 2018-03-11 16:32:43 |
| 44 | A    | A        | 2018-03-11 20:56:42 |
+----+------+----------+---------------------+
2 rows in set (0.00 sec)

要想区分大小写,建表时需要相应设置,也可以在查询时使用:

mysql> select * from user where binary name='a';
+----+------+----------+---------------------+
| id | name | password | regist_time         |
+----+------+----------+---------------------+
|  1 | a    | a        | 2018-03-11 16:32:43 |
+----+------+----------+---------------------+
1 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'的记录。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
待兔 待兔
3个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这