[REPEATABLE READ]
首先设置数据库隔离级别为可重复读(REPEATABLE READ):
- set global transaction isolation level REPEATABLE READ ;
- set session transaction isolation level REPEATABLE READ ;
[REPEATABLE READ]能解决的问题之一
[REPEATABLE READ]隔离级别解决了不可重复读的问题, 一个事务中多次读取不会出现不同的结果, 保证了可重复读. 还是上一篇中模拟不可重复读的例子: 事务 1:
- START TRANSACTION;
- SELECT sleep(5);
- UPDATE users SET state=1 WHERE id=1;
- COMMIT;
事务 2:
- START TRANSACTION;
- SELECT * FROM users WHERE id=1;
- SELECT sleep(10);
- SELECT * FROM users WHERE id=1;
- COMMIT;
事务 1 先于事务 2 执行. 事务 1 的执行信息:
[SQL 1]START TRANSACTION;
受影响的行: 0
时间: 0.000s
- [SQL 2]
- SELECT sleep(5);
受影响的行: 0
时间: 5.001s
- [SQL 3]
- UPDATE users SET state=1 WHERE id=1;
受影响的行: 1
时间: 0.000s
[SQL 4]
COMMIT;
受影响的行: 0
时间: 0.062s
事务 2 的执行信息:
- [SQL 1]
- SELECT * FROM users WHERE id=1;
受影响的行: 0
时间: 0.000s
- [SQL 2]
- SELECT sleep(10);
受影响的行: 0
时间: 10.001s
- [SQL 3]
- SELECT * FROM users WHERE id=1;
受影响的行: 0
时间: 0.001s
- [SQL 4]
- COMMIT;
受影响的行: 0
时间: 0.001s
执行结果:
结论: 可重复读 [REPEATABLE READ] 隔离级别解决了不可重复读的问题.
分析: 可重复读 [REPEATABLE READ] 隔离级别能解决不可重复读根本原因其实就是前文讲过的 read view 的生成机制和 [READ COMMITTED] 不同. [READ COMMITTED]: 只要是当前语句执行前已经提交的数据都是可见的. [REPEATABLE READ]: 只要是当前事务执行前已经提交的数据都是可见的. 在 [REPEATABLE READ] 的隔离级别下, 创建事务的时候, 就生成了当前的 global read view, 一直维持到事务结束. 这样就能实现可重复读.
在模拟不可重复读的事务中, 事务 2 创建时, 会生成一份 read view. 事务 1 的事务 id trx_id1=1, 事务 2 的事务 id trx_id2=2. 假设事务 2 第一次读取数据前的此行数据的事务 trx_id=0. 事务 2 中语句执行前生成的 read view 为 {1},trx_id_min=1,trx_id_max=1. 因为 trx_id(0)<trx_id_min(1), 该行记录的当前值可见, 将该可见行的值 state=0 返回. 因为在[REPEATABLE READ] 隔离级别下, 只有在事务创建时才会重新生成 read view , 事务 2 第二次读取数据之前事务 1 对数据进行了更新操作, 此行数据的事务 trx_id=1.trx_id_min(1)=trx_id(1)=trx_id_max(1), 此时此行数据对事务 2 是不可见的, 从该行记录的 DB_ROLL_PTR 指针所指向的回滚段中取出最新的 undo-log 的版本号的数据, 将该可见行的值 state=0 返回. 所以事务 2 第二次读取数据时的处理和第一次读取时是一致的, 读取的 state=0. 数据是可重复读的.
从事务 1 的执行信息中的 [SQL 3] 我们可以得知,[REPEATABLE READ]隔离级别读操作也是不加锁的. 因为如果读需要加 S 锁的话, 是在事务结束时释放 S 锁的. 那么事务 1[SQL 3]进行更新操作申请 X 锁的时候便会等待事务 2 的 S 锁释放. 现实并不是.
我们知道, MySql 的 InnoDB 引擎是通过 MVCC 的方式在保证数据的安全性的同时, 实现了读的非阻塞. MVCC 模式需要额外的存储空间, 需要做更多的行检查工作; 但是保证了读操作不用加锁, 提升了性能, 是一种典型的牺牲空间换取时间思想的实现. 需要注意的是, MVCC 只在 [READ COMMITTED] 和[REPEATABLE READ]两个隔离级别下工作. 其他两个隔离级别都和 MVCC 不兼容, 因为 [READ UNCOMMITTED] 总是读取最新的数据行, 而不是符合当前事务版本的数据行. 而 [SERIALIZABLE] 则会对所有读取的行都加锁.
通过亲自实践模拟分析 [READ COMMITTED] 和[REPEATABLE READ]两个隔离级别的工作机制, 我们也能深刻的体会到各个数据库引擎实现各种隔离级别的方式并不是和标准 sql 中的封锁协议定义一一对应的.
[REPEATABLE READ]能解决的问题之二
幻读其实是不可重复读的一种特殊情况. 不可重复读是对数据的修改更新产生的; 而幻读是插入或删除数据产生的. 所谓的幻读有 2 种情况, 一个事物之前读的时候, 读到一条记录, 再读发现记录没有了, 被其它事务删了, 另外一种是之前读的时候记录不存在, 再读发现又有这条记录, 其它事物插入了一条记录.
事务 1:
- START TRANSACTION;
- SELECT * FROM users;
- SELECT sleep(10);
- SELECT * FROM users;
- COMMIT;
事务 2:
- START TRANSACTION;
- SELECT sleep(5);
- INSERT INTO users VALUES(2,'song',2);
- COMMIT;
执行结果:
1. 预期结果
2. 实际结果
事务 1 中并没有读取到事务 2 新插入的数据, 并没有发生幻读现象. 这有点出乎我的意料, 难道 Mysql[REPEATABLE READ]隔离级别能解决幻读问题? 按照封锁协议定义, 三级封锁协议是解决不了幻读的问题的. 只有最强封锁协议, 读和写都对整个表加锁, 才能解决幻读的问题. 但是这样做相当于所有的操作串行化, 数据库支持并发的能力会变得极差. 所以 Mysql 的 InnoDB 引擎通过自己的方式在 [REPEATABLE READ] 隔离级别上解决了幻读的问题, 下面我们探究一下 InnoDB 引擎是如何解决幻读问题的.
分析: InnoDB 有三种行锁的算法: 1.Record Lock: 单个行记录上的锁. 2.Gap Lock: 间隙锁, 锁定一个范围, 但不包括记录本身. GAP 锁的目的, 是为了防止同一事务的两次当前读, 出现幻读的情况. 3.Next-Key Lock:1+2, 锁定一个范围, 并且锁定记录本身. 主要目的是解决幻读的问题.
在 [REPEATABLE READ] 级别下, 如果查询条件能使用上唯一索引, 或者是一个唯一的查询条件, 那么仅加行锁(通过唯一的查询条件查询唯一行, 当然不会出现幻读的现象); 如果是一个范围查询, 那么就会给这个范围加上 Gap 锁或者 Next-Key 锁 (行锁 + Gap 锁). 理论上不会发生幻读.
验证一下 Gap Lock 和 Next-Key Lock 的存在
我们可以通过自己操作来验证一下 Gap Lock 和 Next-Key Lock 的存在. 首先我们需要给 state 字段加上索引. 然后准备几条数据, 如下图:
事务 1:
- START TRANSACTION;
- SELECT * FROM users WHERE state=3 for UPDATE;
事务 2:
- [SQL]INSERT INTO users VALUES(5,'song',1);
- [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
- [SQL]INSERT INTO users VALUES(6,'song',2);
- [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
- [SQL]INSERT INTO users VALUES(6,'song',3);
- [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
- [SQL]INSERT INTO users VALUES(6,'song',4);
- [Err] 1205 - Lock wait timeout exceeded; try restarting transaction
- [SQL]INSERT INTO users VALUES(5,'song',0);
受影响的行: 1
时间: 0.120s
[SQL]INSERT INTO users VALUES(6,'song',5);
受影响的行: 1
时间: 0.195s
[SQL]INSERT INTO users VALUES(7,'song',7);
受影响的行: 1
时间: 0.041s
因为 InnoDB 对于行的查询都是采用了 Next-Key Lock 的算法, 锁定的不是单个值, 而是一个范围(GAP). 上面索引值有 1,3,5,8, 其记录的 GAP 的区间如下: (-,1],(1,3],(3,5],(5,8],(8,+). 是一个左开右闭的空间. 需要注意的是, InnoDB 存储引擎还会对辅助索引下一个键值加上 Gap Lock. 事务 1 语句锁定的范围是(1,3], 下个键值范围是(3,5], 所以插入 1~4 之间的值的时候都会被锁定, 要求等待, 等待超过一定时间便会进行超时处理(Mysql 默认的超时时间为 50 秒). 插入非这个范围内的值都正常.
[REPEATABLE READ]读到底加不加锁?
当我理解了 [REPEATABLE READ] 隔离级别是如何解决幻读问题时, 随即产生了另一个疑问.[READ COMMITED]和 [REPEATABLE READ] 通过 MVCC 的方式避免了读操作加锁的问题, 但是 [REPEATABLE READ] 又为了解决幻读的问题加 Gap Lock 或 Next-Key Lock. 那么问题来了,[REPEATABLE READ]读到底加不加锁? 我对这个问题是百思不得其解, 直到读到了这篇文章 http://blog.sina.com.cn/s/blog_499740cb0100ugs7.html 才算理解了一些.
我们可以思考一下如果 InnoDB 对普通的查询也加了锁, 那和序列化 (SERIALIZABLE) 的区别又在哪里呢? 我的理解是 InnoDB 提供了 Next-Key Lock, 但需要应用自己去加锁. 这里又涉及到一致性读 (快照读) 和当前读. 如果我们选择一致性读, 也就是 MVCC 的模式, 读就不需要加锁, 读到的数据是通过 Read View 控制的. 如果我们选择当前读, 读是需要加锁的, 也就是 Next-Key Lock, 其他的写操作需要等待 Next-Key Lock 释放才可写入, 这种方式读取的数据是实时的.
一致性读很好理解, 读不加锁, 不堵塞读. 当前读对读加锁可能比较难理解, 我们可以通过一个例子来理解一下:
事务 1 事务 2
- START TRANSACTION; START TRANSACTION;
- SELECT * FROM users;
- INSERT INTO users VALUES (2, 'swj',2);
- COMMIT;
- SELECT * FROM users;
- SELECT * FROM users LOCK IN SHARE MODE;
- SELECT * FROM users FOR UPDATE;
执行结果:
- mysql> SELECT * FROM users;
- +----+------+-------+
- | id | name | state |
- +----+------+-------+
- | 1 | swj | 1 |
- +----+------+-------+
- 1 row in set (0.04 sec)
- mysql> SELECT * FROM users;
- +----+------+-------+
- | id | name | state |
- +----+------+-------+
- | 1 | swj | 1 |
- +----+------+-------+
- 1 row in set (0.08 sec)
- mysql> SELECT * FROM users LOCK IN SHARE MODE;
- +----+------+-------+
- | id | name | state |
- +----+------+-------+
- | 1 | swj | 1 |
- | 2 | swj | 2 |
- +----+------+-------+
- 2 rows in set (0.00 sec)
- mysql> SELECT * FROM users FOR UPDATE;
- +----+------+-------+
- | id | name | state |
- +----+------+-------+
- | 1 | swj | 1 |
- | 2 | swj | 2 |
- +----+------+-------+
- 2 rows in set (0.00 sec)
结论: MVCC 是实现的是快照读, Next-Key Lock 是对当前读. MySQL InnoDB 的可重复读并不保证避免幻读, 需要应用使用加锁读来保证, 而这个加锁读使用到的机制就是 Next-Key Lock.
作者: 撸码那些事 http://songwenjie.cnblogs.com/
声明: 本文为博主学习感悟总结, 水平有限, 如果不当, 欢迎指正. 如果您认为还不错, 不妨关注一下我的[微信公众号] , 第一时间获取文章更新. 转载与引用请注明出处.
来源: https://juejin.im/post/5b25104ce51d4558cb0ff72f