| MySQL, 锁

MySQL加锁过程详解-01-关于幻读理解

首先，mysql 幻读并非是”一个事务内进行两次相同操作居然得到了不一样的结果”，因为它根本不可能发生在使用了 read view / MVCC 的 RR 隔离级别下，这种幻读的定义更适合给 Oracle，Oracle 的事务隔离只有两级，RC 和 Serializable。然后还有很多人辩解说不可重复读是针对某条记录的，幻读是针对记录集合的，这是在自我安慰么？

这里给出 mysql 幻读的比较形象的场景：

users： id 主键

1
2
3
4
5
1、T1：select * from users where id = 1;

2、T2：insert into `users`(`id`, `name`) values (1, 'big cat');

3、T1：insert into `users`(`id`, `name`) values (1, 'big cat');

T1 ：主事务，检测表中是否有 id 为 1 的记录，没有则插入，这是我们期望的正常业务逻辑。

T2 ：干扰事务，目的在于扰乱 T1 的正常的事务执行。

在 RR 隔离级别下，1、2 是会正常执行的，3 则会报错主键冲突，对于 T1 的业务来说是执行失败的，这里 T1 就是发生了幻读，因为T1读取的数据状态并不能支持他的下一步的业务，见鬼了一样。

在 Serializable 隔离级别下，1 执行时是会隐式的添加 gap 共享锁的，从而 2 会被阻塞，3 会正常执行，对于 T1 来说业务是正确的，成功的扼杀了扰乱业务的T2，对于T1来说他读取的状态是可以拿来支持业务的。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

下面例子版本

1
SELECT VERSION();

img

例子1，读提交

a b
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET AUTOCOMMIT=0;
1.不可重复读
begin begin
INSERT test VALUES(1,1);
SELECT * FROM test; img SELECT * FROM test; img
commit
SELECT * FROM test;
img
COMMIT
B在一个事务的查询的结果变了，不可重复读
2.锁
begin begin
INSERT test VALUES(2,2);
SELECT * FROM test; img
INSERT test VALUES(2,2);
Lock wait timeout exceeded; try restarting transaction
COMMIT COMMIT
begin
INSERT test VALUES(3,3);
INSERT test VALUES(4,4);
COMMIT
BEGIN BEGIN
SELECT COUNT(*) FROM test WHERE a>2;img SELECT COUNT(*) FROM test WHERE a>2;img
INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;img SELECT COUNT(*) FROM test WHERE a>2;img
COMMIT
SELECT COUNT(*) FROM test WHERE a>2; SELECT COUNT(*) FROM test WHERE a>2;
img img

例子2：重复读

a b
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET AUTOCOMMIT=0;
1．可重复读
begin begin
INSERT test VALUES(1,1);
SELECT * FROM test; img SELECT * FROM test; img
commit
SELECT * FROM test;
img
COMMIT
BEGIN
SELECT * FROM test;
img
COMMIT
B在一个事务的查询的没变
2锁
begin begin
INSERT test VALUES(2,2);
SELECT * FROM test; img
INSERT test VALUES(2,2);
Lock wait timeout exceeded; try restarting transaction
COMMIT COMMIT
3(幻读)
BEGIN BEGIN
INSERT test VALUES(3,3);
SELECT * FROM test; img SELECT * FROM test; img
COMMIT
SELECT * FROM test; img
INSERT test VALUES(3,3);
Duplicate entry ‘3’ for key ‘PRIMARY’
COMMIT
BEGIN
SELECT * FROM test; img
COMMIT
幻读，b明明查到没有，插入时候提示主键冲突，刚刚查询没有，出现幻觉？
begin
INSERT test VALUES(4,4);
COMMIT
4．可重复读
BEGIN BEGIN
SELECT COUNT(*) FROM test WHERE a>2;img SELECT COUNT(*) FROM test WHERE a>2;img
INSERT test VALUES(5,5);
SELECT COUNT(*) FROM test WHERE a>2;img SELECT COUNT(*) FROM test WHERE a>2;img
COMMIT
BEGIN
SELECT COUNT(*) FROM test WHERE a>2; SELECT COUNT(*) FROM test WHERE a>2;
img img
COMMIT COMMIT

网上很多说范围啊，count等等都是不对的，不用于幻读

注：转载自 博客园 crazyYong：https://www.cnblogs.com/crazylqy/p/7612230.html

ESC