RR与RC隔离级别下MySQL不同的加锁解锁方式

|  RC与RR隔离级别下MySQL不同的加锁解锁方式


  • MySQL5.7.21
  • 数据准备
root@localhost : pxs 05:26:27> show create table dots\G
*************************** 1. row ***************************
  Table: dots
Create Table: CREATE TABLE `dots` (
`id` int(11) NOT NULL,
`color` varchar(20) COLLATE utf8_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
root@localhost : pxs 05:27:34> select * from dots;
+----+-------+
| id | color |
+----+-------+
|  1 | black |
|  2 | white |
|  3 | black |
|  4 | white |
+----+-------+
4 rows in set (0.00 sec)
root@localhost : pxs 01:57:02> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF  |
+--------------------------------+-------+
1 row in set (0.00 sec)

1.RC隔离级别

  • 确认隔离级别
root@localhost : pxs 05:27:35> show variables like '%iso%';
+-----------------------+----------------+
| Variable_name        | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
| tx_isolation          | READ-COMMITTED |
+-----------------------+----------------+
2 rows in set (0.01 sec)
  • 同时开启两个会话,按下图的流程开始操作。

2.RR隔离级别

  • 确认隔离级别
root@localhost : pxs 05:24:41> show variables like '%iso%';
+-----------------------+-----------------+
| Variable_name        | Value          |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)
  • 同时开启两个会话,按下图的流程开始操作。

3.半一致读semi-consistent read

3.1 半一致读发生条件

  • RC隔离级别
  • RR隔离级别,且innodb_locks_unsafe_for_binlog=true

3.2 innodb_locks_unsafe_for_binlog

  • innodb_locks_unsafe_for_binlog默认为off。 
  • 如果设置为1,会禁用gap锁,但对于外键冲突检测(foreign-key constraint checking)或者重复键检测(duplicate-key checking)还是会用到gap锁。  
  • 启用innodb_locks_unsafe_for_binlog产生的影响等同于将隔离级别设置为RC,不同之处是:

1)innodb_locks_unsafe_for_binlog是全局参数,影响所有session;但隔离级别可以是全局也可以是会话级别。

2)innodb_locks_unsafe_for_binlog只能在数据库启动的时候设置;但隔离级别可以随时更改。    基于上述原因,RC相比于innodb_locks_unsafe_for_binlog会更好更灵活。

启用innodb_locks_unsafe_for_binlog还有以下作用:

  • 对于update或者delete语句,InnoDB只会持有匹配条件的记录的锁。在MySQL Server过滤where条件,发现不满足后,会把不满足条件的记录释放锁。这可以大幅降低死锁发生的概率。 
  • 简单来说,semi-consistent read是read committed与consistent read两者的结合。一个update语句,如果读到一行已经加锁的记录,此时InnoDB返回记录最近提交的版本,由MySQL上层判断此版本是否满足update的where条件。若满足(需要更新),则MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。

来看下面这个例子:

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB; 
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2); 
COMMIT;

这个例子中,表上没有索引,所以对于记录锁会用到隐藏主键。

假设某个client开启了一个update:

SET autocommit = 0; 
UPDATE t SET b = 5 WHERE b = 3;

假设另一个client紧接着也开启一个update:

SET autocommit = 0; 
UPDATE t SET b = 4 WHERE b = 2;

每当InnoDB发起update,会先对每一行记录加上排它锁,然后再决定记录是否满足条件。如果不匹配,则innodb_locks_unsafe_for_binlog开启,InnoDB就会把记录上的锁释放掉。否则,InnoDB会一直持有锁直到事务结束。具体如下:

如果innodb_locks_unsafe_for_binlog没有开启,第一个update会一直持有x锁

x-lock(1,2); retain x-lock 
x-lock(2,3); update(2,3) to (2,5); retain x-lock 
x-lock(3,2); retain x-lock 
x-lock(4,3); update(4,3) to (4,5); retain x-lock 
x-lock(5,2); retain x-lock

第二个update会阻塞住直到第一个update提交或者回滚

x-lock(1,2); block and wait for first UPDATE to commit or roll back

如果innodb_locks_unsafe_for_binlog开启,第一个update先持有x锁,然后会释放不匹配的记录上面的x锁

x-lock(1,2); unlock(1,2) 
x-lock(2,3); update(2,3) to (2,5); retain x-lock 
x-lock(3,2); unlock(3,2) 
x-lock(4,3); update(4,3) to (4,5); retain x-lock 
x-lock(5,2); unlock(5,2)

对于第二个update,InnoDB会开启半一致读,此时InnoDB返回记录最近提交的版本,由MySQL上层判断此版本是否满足update的where条件。

x-lock(1,2); update(1,2) to (1,4); retain x-lock 
x-lock(2,3); unlock(2,3) 
x-lock(3,2); update(3,2) to (3,4); retain x-lock 
x-lock(4,3); unlock(4,3) 
x-lock(5,2); update(5,2) to (5,4); retain x-lock

4.一开始的例子

4.1 RC隔离级别

session 1

session 1执行:

update dots set color = 'black' where color = 'white'; 

由于color列无索引,因此只能走聚簇索引,进行全部扫描。加锁如下: 

注:如果一个条件无法通过索引快速过滤,那么存储引擎层面就会将所有记录加锁后返回,然后由MySQL Server层进行过滤。因此也就把所有的记录,都锁上了。

但在实际中,MySQL做了优化,如同前面作用1所提到的。在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录放锁 (违背了2PL的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。 

实际加锁如下: 

session 2

session 2执行:

update dots set color = 'white' where color = 'black'; 

session 2尝试加锁的时候,发现行上已经存在锁,InnoDB会开启semi-consistent read,返回最新的committed版本(1,black),(2,white),(3,black),(4,white)。MySQL会重新发起一次读操作,此时会读取行的最新版本(并加锁)。如同前面作用2所提到的。 

加锁如下: 

MySQL优化后实际加锁如下: 

4.2 RR隔离级别

session 1

session 1执行:

update dots set color = 'black' where color = 'white'; 

由于color列无索引,因此只能走聚簇索引,进行全部扫描。加锁如下: 

session 2

session 2执行:

update dots set color = 'white' where color = 'black';

更新被阻塞。  等session 1提交commit之后,session 2update才会成功。

引申:RR隔离级别,且开启innodb_locks_unsafe_for_binlog=ON

  • 环境准备
root@localhost : (none) 04:57:46> show  variables like '%iso%';
+-----------------------+-----------------+
| Variable_name        | Value          |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation          | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.01 sec)
root@localhost : (none) 04:55:25> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | ON    |
+--------------------------------+-------+
1 row in set (0.00 sec)
root@localhost : pxs 05:00:54> select * from dots;
+----+-------+
| id | color |
+----+-------+
|  1 | black |
|  2 | white |
|  3 | black |
|  4 | white |
+----+-------+
4 rows in set (0.00 sec)
  • 开始操作

注:过程现象满足RR隔离级别,也符合设置innodb_locks_unsafe_for_binlog=ON的情况。因为前面所讲的启用innodb_locks_unsafe_for_binlog会产生作用1与作用2,所以整个加锁与解锁情况与RC隔离级别类似。

参考:

《数据库事务处理的艺术:事务管理与并发控制》  https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_locks_unsafe_for_binlog http://hedengcheng.com/?p=771 http://hedengcheng.com/?p=220

|  作者简介

韩杰  沃趣科技MySQL数据库工程师

熟悉mysql体系架构、主从复制,熟悉问题定位与解决。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏微信公众号:Java团长

深入理解Mysql——锁、事务与并发控制

本文对锁、事务、并发控制做一个总结,看了网上很多文章,描述非常不准确。如有与您观点不一致,欢迎有理有据的拍砖!

2023
来自专栏Rgc

mysql数据库优化(四)-项目实战

在flask项目中,防止随着时间的流逝,数据库数据越来越多,导致接口访问数据库速度变慢。所以自己填充数据进行测试及 mysql优化

1253
来自专栏python读书笔记

python 数据分析基础 day10-sqlite3一、使用逻辑二、创建数据库及表三、插入记录四、更新记录五、获取记录

今天是读《python数据分析基础》的第10天,今天的笔记内容是利用sqlite3模块对数据库文件进行操作。这个模块的笔记主要分为五个板块:sqlite3的使用...

2856
来自专栏抠抠空间

MySQL之pymysql模块

PyMySQL介绍 PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库,Python2中则使用mysqldb。 Django中...

3317
来自专栏梅海峰的专栏

可重复读事务隔离级别之 django 解读

本文尝试结合 django 解释应用开发中并发访问数据库可能会遇到的可重复读引起的问题,希望能帮助大家在开发过程中有效避免类似问题。

2890
来自专栏我的博客

centos搭建svn使用mysql管理认证

1、安装 yum install subversion 安装ssl,mysql认证模块等(如果使用http或者svn访问就不用ssl了) yum install...

2915
来自专栏解Bug之路

MySql之自动同步表结构

在开发过程中,由于频繁的修改数据库的字段,导致rd和qa环境的数据库表经常不一致。 而由于这些修改数据库的操作可能由多个rd操作,很难一次性收集全。人手工去和...

651
来自专栏吴生的专栏

MySQL 慢查询日志

MySQL有一种日志,叫做慢查询日志,主要就是用来记录一些耗时的查询操 作。通过这个日志我们就可以分析出哪些的操作是影响性能的,我们需要对其 进行一些优化措施。

2751
来自专栏蓝天

高性能高可用的分布式唯一ID服务——mooon-uniq-id

源码位置:https://github.com/eyjian/mooon/tree/master/application/uniq_id。

682
来自专栏自由而无用的灵魂的碎碎念

Oracle:创建db_link

global_name也就是数据库的全局数据库名,可已使用select * from global_name;查询:

842

扫码关注云+社区