mysql事务隔离与原理

mysql事务隔离与原理

事务遇到的问题

数据库的隔离级别会遇到下面三个问题,分别是脏读,不可重复读,幻读。

脏读

​ 事务A读取到事务B尚未提交的数据,这是不能接受的,如果事务B后面回滚了,事务A就相当于读取到了不存在的脏数据。

不可重复读

​ 指事务A读取一条数据 “name=张三”,然后事务B修改了该条数据“update name=李四”并提交,事务A再次读取时发现name被修改了。这在有些情况下是能接受的,有些情况下是不能接收的,要根据业务需求来。

幻读

​ 与不可重复读类似,A先读取“where id > 10”查询出2条数据,此时B插入一条数据并提交,然后A再次读取时发现读取到了三条数据,读取到了幻行,和不可重复读类似,只是关注的点不一样,一个关注数据内容的变化,一个关注数据数量的变化。

事务隔离级别

​ 上面描述的问题在有的业务场景下不允许发生,但在有的业务场景下确是允许发生的,所以mysql提供了四种事务隔离级别,能够由程序员自己根据业务需求配置mysql,选择哪种隔离级别。

四种隔离级别分别是,读未提交,读已提交,可重复读,序列化。

读未提交级别Read Uncommitted

​ 允许读取其他事务未提交的数据,也就是允许上面那三种现象的发生。

读已提交Read Committed

​ 允许读取已经提交的数据,未提交的不能读取,这能解决脏读问题,但是无法解决不可重复读和幻读的问题。

可重复读Repeatable Read

​ 不允许读取到其他事务已提交的数据,第一次读取时建立当前数据的快照,下次读取仍读取该快照,这样读取到的内容就不会受其他事务插入或修改的数据影响了。

串行化Serializable

​ 事务的提交串行化,可以解决上面所有的问题。

mvcc快照原理

​ 每行数据保存两个隐藏的列,分别是创建时的版本号和删除时的版本号。

进行查询时只查询创建版本号小于等于当前事务,且删除版本号为空或大于当前事务的数据。

进行更新时将数据的删除版本号设置为当前值,并复制一条数据进行更新,将创建版本号设为当前事务的

删除时将删除版本号设置当前事务

行锁和间隙锁

​ 如果当前数据库数据如下(id添加主键索引,number添加普通索引):

id number
0 0
1 1
2 2
5 5
7 7

如果事务A执行下面语句,就会对id = 2这一条语句加行锁,其他事务无法修改或删除该条数据。

1
select * from table where id = 2 for update

如果事务A执行下面的语句,将会对 id>2 的部分添加行锁,这里分别是id=5和id=7,除此之外还会对(7 - max)添加间隙锁,为什么是间隙锁而不是行锁呢,因为此时(7-max)的数据还不存在,也就不能添加行锁了,只能使用间隙锁,锁住暂时不存在的数据的索引间隙。

1
select * from table where id > 2 for update

上面只是间隙锁对唯一索引上的规则,如果是普通索引会有不一样的行为。

1
select * from table where number = 2 for update

执行这条语句,他与上面不同的是他是查询的number字段而不是id,此时它不但会锁定number = 2这条数据,还会添加间隙锁,锁住number处于 (1-2)(2-5]之间的number字段数据。

那么如果number<=1 或者 number >5就能正常修改吗,当然也不一定。还需要满足id的限制。因为number作为二级索引,其下层是以一对多的形式存储主键id的,这里gap锁的左端记录是(1,1),右边数据是(5,5),不仅要求number满足范围条件,也需要id满足条件。

如果number <=1,虽然number的锁满足了,但需要id在区间(-max , 1]

如果number>5 则需要id在区间 (5 , max)

再看一个间隙锁的例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `ttt` (
`uid` varchar(18) NOT NULL DEFAULT '',
`gap` int NOT NULL ,
`name` VARCHAR(10),
PRIMARY KEY (`uid`),
KEY `gap` (`gap`)
) ENGINE=InnoDB;

insert into `ttt` values ('11111',1,'a1');
insert into `ttt` values ('11113',3,'a3');
insert into `ttt` values ('11115',5,'a5');
insert into `ttt` values ('11117',7,'a7');
insert into `ttt` values ('11119',9,'a9');

##锁定gap = 3 的记录
BEGIN;
select * from `ttt` where `gap`=3 for update;


锁定gap=3的记录后,同时加在gap上的间隙锁是 (1,5],加在id上的间隙锁是(11111,11115],即其他事务插入的记录必须在 (1,11111)左边或 (5,11115)右边,一下是测试结果。

1
2
3
4
5
6
7
8
insert into `ttt` values ('11110',0,'a6');  ##success 因为在(1,11111)左边
insert into `ttt` values ('11110',1,'a6'); ##success 因为在(1,11111)左边
insert into `ttt` values ('11112',1,'a6'); ##block 因为在(1,11111)(5,11115)区间内

insert into `ttt` values ('11110',2,'a6'); ##block 因为在(1,11111)(5,11115)区间内

insert into `ttt` values ('11114',5,'a6'); ##block 因为在(1,11111)(5,11115)区间内
insert into `ttt` values ('11116',5,'a6'); ##success 因为在(5,11115)右边

从这个例子中能清楚的展示,二级索引的next-lock,不是只锁定二级索引对应的字段,而是更加细分到具体id,即使二级索引字段在gap锁范围内,但id在范围外也是可以的。

总结

mysql使用next-lock解决尽量小范围内加锁问题,加锁后被锁定的范围就不能被修改和新增。

使用mvcc解决不可重复读和幻读问题,这里与锁无关,通过事务版本号和删除版本号,这样就不能读取后面事务做的修改了,但是仅限于快照读,如果发生当前读仍然会读取其他事务做的修改。

这种特性有时还蛮坑的,需要注意。

例1,比如事务1查询 age = 10 的记录共10条,事务2修改了其中一条的age,这时快照读读到的仍是10条,当前读读到的就是9条,如果按照age更新就只能更新9条,此时再次当前读还是10条会发现有一条不会被更新。

例2,比如事务1查询age = 10 的记录共10条,事务2新增一条age = 10的记录,这时快照读仍是10条,但当前读读到的是11条,发生过一次当前读后再使用快照读就变成11条了。


mysql事务隔离与原理
https://www.huangchaoyu.com/305128508.html
作者
hcy
发布于
2020年10月21日
更新于
2024年8月17日
许可协议