2022-12-27 23:44阅读: 298评论: 0推荐: 0

数据库并发控制原理

参考资料:

事务

ACID

老生常谈的话题,

A:atomic, 原子性,一个事务可能由多条sql语句构成,这些语句要么全部完成要么都不完成

I:Isolation,隔离性。即使每个事务都保证了原子性,但是事务可能会交叉执行,导致不一致状态。我们要保证事务在并发执行时不受其他事务干扰的特性称为隔离性。

D:Durability, 持久性,一个事务提交后,它对数据库系统的影响是持久的,不会受到系统故障之类的影响而丢失

C:Consistency,一致性,是我理解起来最费力的一个性质。这里概括一下,CMU15-445、《数据库系统概念》中对Consistency的解释:

  • 数据库是对现实世界的模拟

  • 如果一个事务修改了数据库,数据库呈现了一种现实世界不可能存在的状态(logically incorrect),那么就称事务违反了一致性

  • 比如 : 数据库有一张 students 表,存放学生姓名、年龄、班级等属性。如果一条事务完成后,将学生的年龄改成了 小于 0 的值, 这在现实世界是不可能的情况,因此违反了一致性

  • 更经典的例子:银行账户转账操作,无论进行几次转账操作,所有账户的全部资金和始终都应该一样。如果某次转账后,资金总和多了或者少了,说明某个事务违反了一致性。

  • 确保一致性,一方面,业务程序员有一定的责任。另一方面,数据库如果不能保证上面三个性质,那么一致性也很难保证。

    • DBMS提供 check语法、触发器等自动约束检查措施,为一致性的遵守提供方便
  • 其余三个性质的违反都会导致一致性的破坏!

如何实现它们?

  • A : (undo)日志 \shadow page
  • I : 并发控制,分为悲观和乐观两种控制模型。常见悲观模型就是基于锁的并发控制,乐观模型典型的为基于时间戳的并发控制协议(包括MVCC)。
  • D : (redo)日志
  • C : A + I + D + 业务程序员

Schedule(调度)

schedule 是指事务指令在系统中执行的时间顺序。

比如下图的两种调度,表示了两种事务指令的执行顺序

image-20220920120428707

serial(串行的)

上图两个事务的调度是串行的,因为来自各事务的指令紧挨在一起,没有穿插

serial schedule 的事务一定满足隔离性要求,它们互不影响,但是为了增加吞吐量和减少等待时间,数据库并不要求所有的调度都是串行的。

serializable(可串行化的) schedule

为了获得并发度,不可能所有事务串行执行,数据库管理系统允许事务语句交替执行的同时还要保证事务的隔离性, 这种schedule 等效(equivalent to)于 serial schedule的schedule 被称为 serializable schedule

conflict serializable(冲突可串行化)

两个操作“冲突”如果:

  • 它们分别属于不同的事务
  • 它们操作相同的object,并且至少一个是写操作

如果可以把Schedule S的 非冲突 操作交换次序,得到的新schedule是串行schedule的话,就称S是conflict serializable

下图所示,左边的调度可以交换非冲突操作转换到右边的串行调度:

image-20220920150816422

但是下图的例子就不能了,因为T1的W(A) 和 T2的W(A)是冲突操作,不能交换次序,不能得到串行调度

image-20220920150931668

更快地检测一个调度是否为冲突可串行化的方法 : 构造优先图,然后检测是否存在环,如果没有环则说明这是一个冲突可串行化的调度

image-20220926214157981

view serializable

就看调度的最后结果,不管冲突操作,如果最后结果于串行调度相同,那么称这个调度为view serializable。

比如下图,左边的调度并不是冲突可串行化的(写操作冲突,不能调换顺序),但是仅仅看最后结果,左边的调度于串行调度的结果相同。

image-20220920151523074

因此可以知道视图可串行化是比冲突可串行化更加宽松的一种串行化定义。但是没有DBMS支持视图可串行化,因为判断视图可串行化所需要的计算太过复杂,几乎所有的DBMS支持冲突可串行化。

image-20220920151904669

事务的隔离级别与各种不一致现象

数据库提供不止可串行化这一个隔离级别,为了能够提升数据库的并发度,DBMS往往提供多种隔离级别。一个应用程序设计者可能会为了提高系统的性能而接受较弱的隔离性级别。

SQL标准规定的隔离性级别如下:

image-20220920154753437

如上图所示,在各种隔离级别下,可能会发生不同的不一致问题,包括脏写、脏读、不可重复度和幻读。其中脏写是最严重的不一致行为,数据库管理系统不允许在任何隔离级别下发生脏写现象。下面看一下其余三种不一致行为的产生原因:

  1. 脏读:事务1修改数据,被事务2读到,之后事务1abort,事务2读到的是脏数据。
  2. 不可重复度:事务1读数据,事务2修改数据,事务1再读数据,事务1两次读到的数据不一致。
  3. 幻读:事务1统计行数,事务2插入数据,事务1再统计行数,事务1两次统计的行数不一致。

可串行化是最高的隔离级别,它的含义是事务的执行结果与串行化调度一致。

在具体实现上,我们可以有多种方式实现不同的隔离级别

  • 基于锁的并发控制协议
  • 基于时间戳并发控制协议
  • 基于多版本和快照隔离并发控制协议

基于锁的并发控制方法属于悲观模型,适合多写少读的场景,而后两者属于乐观模型,适合多读少些的场景。

CMU15-445的lab则使用了锁实现了RU、RC、RR三种隔离级别。

并发控制

基于锁的并发控制协议

属于并发控制的悲观模型

关于数据库中的锁

数据库并发控制这里所说的锁,并不与我们在OS中的同步原语“锁”一致,CMU15445中把OS中的锁称为“latch”,而数据库的锁称为“lock”,如果下图所示

image-20220923151937373

数据库中的锁可能由 不止一种同步原语实现,除了latch,还有condition variable,至少在cmu的lab中,我们使用mutex和condition variable 实现了lock manager类。

OS中的锁有两种:读锁和写锁

而数据库中的锁有:共享锁、排他锁、(共享\排他)意向锁、升级锁;它们的相容性矩阵如下:

image-20220923153608995

其中,意向锁是一个我们定义多粒度锁机制的产物,意向锁一般是表级锁。

二阶段锁与严格二阶段锁

为了解决不可重复读问题,可以使用二阶段锁协议(2PL),2PL将加锁和解锁分成两个阶段

  • growing阶段: 只能获取锁
  • shrinking阶段,只能释放锁

2pl为什么能解决不可重复读问题?先来想一下不可重复读产生的场景:

  • 事务T1获取A的读锁,读A,然后释放A的读锁
  • 事务T2被调度,获取A的写锁,修改A,释放A的写锁
  • 重新调度T1,获取A的读锁,读A,发现两次读取结果不同,产生不可重复读现象。

为了解决T1不可重复读的问题,我们应用2PL协议,可能有两种情况:

情况一:

  • 事务T1获取A的读锁,读A,然后选择释放A的读锁,这时已经到的shrinking阶段
  • 事务T2被调度,获取A的写锁,修改A,释放A的写锁
  • 事务T1由于使用了2PL,此时已经是shrik阶段,因此T1不能再获取锁,也不能读取A,也就没有了不可重复读现象

情况二:

  • 事务T1获取A的读锁,读A,选择不释放A的读锁,还是再growing阶段
  • 事务T2想要修改A,但是T1获取了A 的读锁,因此T2没能对A完成修改
  • 事务T1,再读A,第二次的结果与第一次相同,没有产生不可重复度现象

可见在两种情况中,T1不能在2pl协议下发生不可重复读现象。

2pl的问题

  • 脏读现象依然存在
  • 级联回滚

使得2pl并没有解决脏读,一开始我觉得很诧异,因为2pl能够解决不可重复度问题,但是却不能够解决脏读问题,这与直觉不符啊。通过后面的实践与回顾,我明白了确实是我的直觉错了。

如果有这样的疑惑,你可能和我一样把隔离级别标准的那套特性记得特别清楚,但是在这个问题上却是先入为主了。隔离级别的标准说,只要你的隔离级别是可重复读, 那么就得杜绝脏读和不可重复读现象,但是没说解决了不可重复读问题你就是可重复读隔离级别了。要实现可重复读隔离这个级别,不仅需要2pl,而且还需要加入其他特性。

下图的这个例子,展示了2pl的级联回滚问题:是的,T2真的产生了脏读,而且正因为产生了脏读,才会产生 cascading rollback

image-20220923160337672

T1回滚T2也要回滚,因为T2读取了T1中未提交的内容 A, 这是脏读!

为了解决级联回滚,彻底解决脏读和不可重复读现象,我们使用Strong Strick 2PL,它对锁的释放有更高的要求: 只能在事务结束时(commit 或者 abort)才能释放锁

image-20220923160632462

如何使用锁来实现各种隔离级别

  • isolation = READ_UNCOMMITTED : 不需要读锁, 只需要写锁
  • isolation = READ_COMMITED : 读锁,写锁都需要,但是读锁 在读完就释放, 写时上写锁,在commit时才释放写锁 (不需要2pl)
  • isolation = REPEATABLE_READ : 使用强二阶段锁,在growing阶段只能获取锁,在shring阶段只能释放锁,而且所有的锁都在commit时才释放
  • isolation =SERIALIZABLE : 本lab不要求,理论上需要 上一级别的所有要求 + index locks。因为幻读的出现的原因是我们不可能对不存在的记录加锁,所以我们只能对索引加锁了。sql语句运行过程中,如果插入一条新数据,之后势必会更新索引,如果新加入的索引在 index lock 保护的范围内,那么就回滚对记录所作的插入操作,因此index lock 起到了消除幻读的作用。

CMU15-445的lab要求使用读写锁实现RU、RC、RR这三个隔离级别。

上面所说的所有操作,上锁是否成功由lock_manager管理,而锁的释放时间(是在commit才释放,还是读\写完就立刻释放)由各executor选择,最终事务commit时,不管是哪个隔离级别,transaction_manager释放所有还未释放的锁。

死锁检测\死锁预防

只要使用基于锁的协议就有可能产生死锁现象。例子?

  • 假设T1的执行需要获取A的读锁,然后再获取B的读锁。

  • 假设T2的执行需要获取B的写锁,然后获取A的写锁

  • 假设CPU这样调度T1和T2; T1获取A的读锁 --> T2获取B的写锁 --> T1尝试获取B的读锁,由于读写互斥,因此等待。 --> T2尝试获取A的写锁,由于读写互斥,因此等待。

  • 可以看到T1和T2都陷入了等待,陷入死锁

有两种方法处理死锁:

  • DeadLock Detection and Recovery(死锁检测: 乐观,适合死锁概率不高的场景

    • DBMS创建一个waits-for graph 来跟踪事务在等待哪些锁,节点是事务T,从Ti指向Tj的边表示,Ti正在等待Tj释放锁
    • 系统每隔一段实践就运行死锁检测算法,检测图中是否有环,如果有则表示发生了死锁
    • image-20220923161439573
    • 根据一些指标(事务的age、事务被rollback的次数、事务已经获得的锁数量。。。)选择要abort或者restart的事务
  • DeadLock Prevention(死锁预防:悲观,适合死锁概率较高的场景)

    • 当一个事务尝试想要获取另一个事务已经获取的锁(当然这两锁是互斥的)时,由lock manager杀死其中的一条事务。视杀死事务的不同,有两种锁预防策略
    • Wait-Die ("Old Waits for Young")策略,当请求锁的事务比已经获得的锁的事务“老”时,则等待。否则请求事务abort
    • Wound-Wait("Young Waits for Old"),当请求事务比已经已经获取的锁的事务“年轻”时则等待,否则请求事务abort
    • 可以使用时间戳来判断事务的“老”或“年轻”。CMU-15445的lab中,使用txnid代替时间戳,越晚开始的事务其txnid越大,也就代表它越年轻。

基于时间戳的并发控制协议

属于并发控制的乐观模型

这个就不那么仔细整理了,因为没有具体实践过,没有什么特别的想法,看ppt、notes和教科书就好了

Basic Timestamp Ordering (BASIC T/O)

BASIC T/O + Thomas rules 能够产生 非.冲突可串行化(但是视图可串行化的调度),能够提高数据库的并发度。

可能的缺点:

  • 复制和更新时间戳的开销大
  • 长事务可能被饿死:长事务以为者它很容易成为系统中最“老”的事务,很容易和新事务产生冲突而被迫“自杀”

Optimistic Concurrency Control (OCC)

同样也是基于时间戳的一个协议,但由于历史问题被命名为Optimistic Concurrency Control(乐观并发控制),但其实BASIC T/O以及下节的MVCC也属于乐观控制模型。

"事实上可以基于 Basic T/O 的规则进行改进,没必要在每一步操作的时候都判断是否需要 abort 并 restart。完全可以等到事务准备 commit 时统一判断。"

三个阶段:

  • read

  • validation

  • write

可能的问题:

  • 同样由于要拷贝保存本地副本和更新时间戳,所以开销比较大
  • 如果真的在validation阶段检测到冲突并abort,那么OCC可能比其他协议浪费的cpu资源更多,因为OCC到最后临近commit时才abort,导致先前做的计算都是无用功。

MVCC

应该也算是一种基于时间戳的协议吧

image-20220925162402239

也没有实践过,但是现代DBMS几乎没有不体现MVCC思想的的,比如mysql的并发控制模型同时使用2pl和mvcc

但是看着有点晕,个人感觉cmu15445ppt上的讲mvc c对应的应该是教科书15.6.2的内容,是 mvcc + 2pl ,并不是单纯的mvcc,单纯的mvcc不涉及锁,所有读请求都不会失败。

课本上的协议名称 “多版本两阶段封锁” --- 结合了MVCC和2pl的优点,即mvcc的reader不会被阻塞能够得到更好的并发 + 2pl保证产生无级联调度(是的,单单mvcc并不能保证无级联调度,也就是说存在脏读现象)。

多版本两阶段封锁区分只读事务和更新事务:

  • 只读事务:read操作读取最新版本
  • 更新事务执行2pl,更新事务在读取一个数据项时,需要获得S锁;在修改一个数据项时,需要获取X锁,然后为数据项创建拷贝,写操作在新版本上进行。且所有的锁都在commit时才提交。(诶,这样看这个协议也不像时cmu课堂上讲的协议,属实搞不太懂。。。)

之后的内容会对MySql的MVCC机制做一些介绍和实验。

幻读 / read skew

mysql的reapeatable read级别下能够防止大部分幻读/read skew(只读事务下),但在非只读事务中幻读是有可能产生的,下面是复现幻读的方法:

来自 :https://medium.com/analytics-vidhya/understanding-mysql-transaction-isolation-levels-by-example-1d56fce66b3d

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
CREATE TABLE `users` ( `id` int(2) NOT NULL AUTO_INCREMENT, `username` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL, `password` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT 'password01', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
copy
  • 1
  • 2
  • 3
# 开启事务1 begin; select * from users;
copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
+----+----------+------------+ | id | username | password | +----+----------+------------+ | 1 | jerry | LoveCats01 | | 2 | tom | wysiwyg77 | | 3 | herry | password01 | | 4 | lily | password02 | +----+----------+------------+
copy
  • 1
  • 2
  • 3
  • 4
# 开启事务2 begin; insert into users(username) value("mac"); commit; #提交事务
copy
  • 1
  • 2
  • 3
  • 4
# 回到事务1 select * from users; # 再执行一遍原来的指令,得到相同的集合 update users set password = "known" where id >= 1; #但是如果修改了事务2对应的记录,那么就会产生幻读现象 select * from users;
copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
+----+----------+----------+ | id | username | password | +----+----------+----------+ | 1 | jerry | known | | 2 | tom | known | | 3 | herry | known | | 4 | lily | known | | 8 | mac | known | # <--- 多了一条幻影记录 +----+----------+----------+

一个简单的防止幻读的方式是,事务1 使用锁定的select语句

copy
  • 1
  • 2
#事务1 select * from users where id >= 1 for update;
copy
  • 1
  • 2
#事务2 insert into users(username) value("pol");
copy
  • 1
  • 2
# 事务2会因锁等待而abort ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

于是事务1不再出现幻读

所以结论是,mysql在reapeatable read隔离级别下,光靠mvcc不能防止非只读事务的幻读,还需要锁的帮助。但是对于只读事务,mvcc就够了,不需要锁就能避免幻读

snapshot isolation && write skew

https://vladmihalcea.com/a-beginners-guide-to-read-and-write-skew-phenomena/

snapshot isolation是一种特殊的并发控制机制,在商业和开源系统中被广泛接受。

Wikipedia:“In practice snapshot isolation is implemented within multiversion concurrency control (MVCC), ”

oracle的最高隔离级别声称是 serializable ,但是实际上它只是snapshot isolation隔离级别。snapshot isolation并不能保证串行化,因此可能会导致write skew问题。

write skew 的问题在以mvcc为基础的数据库中泛滥, oracle数据库即使在 serializable隔离级别下也不能防止这个问题,因为实际上oracle的最高隔离级别是Snapshot Isolation level(它还是能防止read skew的,也就是说防止幻读)。mysql虽然也有mvcc机制,但是在serializable隔离级别下会引入 读锁 而避免write skew。

write-skew的例子,教科书P389 - P390的前两个例子。

第一个例子在这个博客有提到,我再抄一遍 :)

T1:读取A,B,用B的值更新A,

T2: 读取A,B,用A的值更新B

根据快照隔离的定义允许产生如下schedule:

T1 T2
read(A)
read(B)
read(A)
read(B)
A = B
B = A
write(A)
write(B)

假设原先A = 1, B = 2,那么按照上面的调度最后的结果是 A = 2, B = 1, 结果互换;

但是如果我们串行执行两个事务(无论是T1先还是T2先),最后的结果是A值等于B值。也就是说上面这个schedule不与serial schedule等价,因此并不是serializable schedule。

这就是 write skew,发生的根本原因是,仅仅在快照隔离的限制下,由于两个事务跟新不同的数据项,它们之间不存在跟新冲突,因此快照隔离下两个事务都可以正常提交。

mysql 中的并发控制

我想再整理一下mysql中的并发控制机制。

mysql的并发控制将2PL 和 MVCC结合起来运用,具体运行机制视隔离级别不同而不容。

参考书籍: 《mysql是怎样运行的》

实验:各种隔离级别下的行为

在readcommited 和 readrepeatable 两个隔离级别下,因为使用了MVCC机制,只读事务是不会有锁的存在的(一致性读,即只涉及普通select语句的情况)。

read uncommited隔离级别下,由于容许脏读,所以既没有mvcc又没有读锁的存在(但是有写锁,因为任何隔离级别都不容许脏写)

serializable 隔离级别下,读锁的存在, 可以很轻松地解决幻读问题

read uncommited

下面是进行实验的表的具体数据

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
mysql> select * from test; +----+-------+ | id | value | +----+-------+ | 1 | 80 | | 2 | 20 | | 3 | 34 | +----+-------+
事务1 事务2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
select * from test where id = 1; #value = 80
update test set value = 90 where id = 1;
select * from test where id = 1; # value = 90脏读现象
... 在这里查询锁的使用情况

看到出现了脏读现象,事务2使用如下语句查询锁的状态:

copy
  • 1
  • 2
select * from performance_schema.data_locks; # mysql 8.0语法 SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; # mysql 5.7语法

显示为test加了IX锁,并为记录加了X锁(行锁),REC_NOT_GAP 表示这是一个常规的行锁(不是gap、next-key、insert-intention锁)

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | INNODB | 2258674859136:1367:2258640161784 | 41751 | 49 | 55 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IX | GRANTED | NULL | | INNODB | 2258674859136:306:4:2:2258640159000 | 41751 | 49 | 55 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+

可以看到,由于在此隔离级别下,只是事务2加了写锁(事务1没有加读锁),也没有运用mvcc机制(这个其实看不出来:)),因此发生了脏读现象。

read commited

事务1 事务2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
begin;
select * from test where id = 1; # value = 90
begin;
update test set value = 99 where id = 1; # 更新这条记录
select * from test where id = 1; # value = 90,`无脏读现象,没有读到事务2未提交的值数据
在这里查看锁的使用情况
commit;
select * from test where id = 1; # value = 99,读到事务2已提交的数据,出现不可重复度现象
commit;

预料之中,不是吗,在此隔离级别下没有脏读但存在不可重复读现象,事务2在更新后,commit前,查看了一次锁的使用情况:

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | INNODB | 2258674859912:1367:2258640166760 | 41757 | 48 | 68 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640166760 | TABLE | IX | GRANTED | NULL | | INNODB | 2258674859912:306:4:2:2258640163976 | 41757 | 48 | 68 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640163976 | RECORD | X,REC_NOT_GAP | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+

与上一个隔离级别一样,都有写锁,没有读锁。写锁是第二个事务加的,第一个事务没有加读锁,只是用mvcc机制,消除了脏读的可能性。

read repeatable

在此隔离级别,不可重复读现象不存在:

事务1 事务2
begin;
select * from test where id = 1; #value = 99
begin;
update test set value = 100 where id = 1;
commit;
select * from test where id = 1; # value = 99, 不存在可重复读现象
commit;

read repeatable隔离级别与read commited隔离级别下,上述行为的不同归因与 readview产生的时机不同。详见下面章节的原理部分。

在此隔离级别下,只读事务不会有幻读情况的发生

事务1 事务2
begin;
begin;
select * from test where value = 100; # 查询出一条记录
insert into test value(4,100);
在这里查看锁的状态
commit;
select * from test where value = 100; # 查询出一条记录,没有出现幻读现象
commit;

事务2,查看锁的状态:只有表被加了IX锁

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
mysql> select * from performance_schema.data_locks; +--------+----------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+----------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+ | INNODB | 2258674859912:1367:2258640166760 | 41761 | 48 | 78 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640166760 | TABLE | IX | GRANTED | NULL | +--------+----------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+-----------+

在此隔离级别下,非只读事务可能会存在幻读现象:(在此之前将test的那一条id = 4的记录删除)

begin;
begin;
select * from test where value = 100;# 查询出一条记录
insert into test value(4,100);
第一次查看锁的状态
commit;
select * from test where value = 100;# 查询出一条记录
update test set value = 90 where value = 100; #显示两行数据被改动
第二次查看锁的状态
select * from test where value = 90;# 、期望只有一条记录,但显示实两条记录!出现幻读
commit;

第一次锁的状态与上一个实验相同,即只有表加了IX锁,但是第二次锁的状态就有意思了:update语句竟然为4条记录加了next-key锁,包括事务2中新插入的那条!因为新的记录在插入完成后,没有任何锁与之关联,因此事务1可以轻易地取得它的next-key锁并修改它,这一修改就将新纪录的lsn改成了事务1的trx_id,这便使新插入的记录对事务1可见了。MVCC的对于读的要求是:事务读取到本事务以前的最近版本,但很明显这里事务1读取到了来自未来的数据(事务2比事务1晚开始),因而产生了幻读。具体原因见最后章节的原理部分。

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
mysql> select * from performance_schema.data_locks; +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 2258674859136:1367:2258640161784 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IX | GRANTED | NULL | | INNODB | 2258674859136:306:4:1:2258640159000 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X | GRANTED | supremum pseudo-record | | INNODB | 2258674859136:306:4:2:2258640159000 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X | GRANTED | 1 | | INNODB | 2258674859136:306:4:3:2258640159000 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X | GRANTED | 2 | | INNODB | 2258674859136:306:4:4:2258640159000 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X | GRANTED | 3 | | INNODB | 2258674859136:306:4:5:2258640159000 | 41780 | 49 | 114 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | X | GRANTED | 4 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+

在此隔离级别下,非只读事务可能会存在幻读现象,为什么说可能?因为事务1的非读操作必须是update或者delete,且用这两条语句修改了事务2新插入的数据,这才有可能在事务1中看到事务2的插入数据。这中的原理与msql快照读的readview生成机制有关

serializable

事务1 事务2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;
select * from test where id = 1;
begin;
update test set value = 101 where id = 1; 阻塞
查看锁的使用状况
select * from test where id = 1; 没有发生脏读现象

即时是只读事务,在可串行化隔离级别下,也已经对表加了IS锁,以及对记录加了S锁,因此当事务2修改记录时会被阻塞,而防止脏读。

在serializable隔离级别下,即使是最普通的查询语句都会对记录加读锁,隔离性强,但是并发性弱。

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+ | INNODB | 2258674859136:1367:2258640161784 | 283733651569792 | 51 | 10 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IS | GRANTED | NULL | | INNODB | 2258674859136:306:4:5:2258640159000 | 283733651569792 | 51 | 10 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S,REC_NOT_GAP | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+

再试试重现幻读,由于锁的存在,事务2的插入语句会被阻塞

事务1 事务2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
begin;
select * from test where value = 100;
查看锁的使用状况① begin;
insert into test value(6, 100); 阻塞一段时间后失败!
查看锁的使用状况②
commit; commit;

第一次锁的状态:对所有记录加了S锁,这里应该是S型next-key锁

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+ | INNODB | 2258674859136:1367:2258640161784 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IS | GRANTED | NULL | | INNODB | 2258674859136:306:4:1:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 2258674859136:306:4:3:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 2 | | INNODB | 2258674859136:306:4:4:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 3 | | INNODB | 2258674859136:306:4:5:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+-----------+-------------+------------------------+

第二次查询锁的状态:事务2 insert 语句会查看要插入的地方是否有next-key锁,如果有则生成一个插入意向锁,并被阻塞

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+ | INNODB | 2258674859912:1367:2258640166760 | 41997 | 50 | 35 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640166760 | TABLE | IX | GRANTED | NULL | | INNODB | 2258674859912:306:4:1:2258640163976 | 41997 | 50 | 35 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640163976 | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record | | INNODB | 2258674859136:1367:2258640161784 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IS | GRANTED | NULL | | INNODB | 2258674859136:306:4:1:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 2258674859136:306:4:3:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 2 | | INNODB | 2258674859136:306:4:4:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 3 | | INNODB | 2258674859136:306:4:5:2258640159000 | 283733651569792 | 51 | 47 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+

实验:一致性读(快照读)和锁定读

在read committed 和 repeatable read 隔离级别下,所有的普通select语句都是一致性读或者快照读(它们都不会加锁)

快照读相对应的是锁定读语句,我们可以有4中方式产生锁定读:

  • select ... lock in share mode
  • select ... for update
  • update
  • delete

在repeatable read下解决幻读

通过实验一,知道在repeatable read隔离下,我们不能通过快照读防止幻读现象,但是我们可以用锁定读防止幻读:

首先展示一下实验表的数据:其中id为主键列

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
mysql> select * from test; +----+-------+ | id | value | +----+-------+ | 1 | 100 | | 2 | 20 | | 3 | 34 | +----+-------+
事务1 事务2
begin;
begin;
select * from test where value = 100 lock in share mode;# 查询出一条记录
insert into test value(4,100);阻塞一段时间后失败
第一次查看锁的状态
select * from test where value = 100 lock in share mode; #不会有幻读

事务2试图插入时被阻塞,因此事务1中不会有幻读现象了,我们查看锁的状态:

copy
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
+--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+ | ENGINE | ENGINE_LOCK_ID | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+ | INNODB | 2258674859912:1367:2258640166760 | 42005 | 50 | 50 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640166760 | TABLE | IX | GRANTED | NULL | | INNODB | 2258674859912:306:4:1:2258640163976 | 42005 | 50 | 50 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640163976 | RECORD | X,INSERT_INTENTION | WAITING | supremum pseudo-record | | INNODB | 2258674859136:1367:2258640161784 | 283733651569792 | 51 | 89 | dbms_fundamentals | test | NULL | NULL | NULL | 2258640161784 | TABLE | IS | GRANTED | NULL | | INNODB | 2258674859136:306:4:1:2258640159000 | 283733651569792 | 51 | 89 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | supremum pseudo-record | | INNODB | 2258674859136:306:4:3:2258640159000 | 283733651569792 | 51 | 89 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 2 | | INNODB | 2258674859136:306:4:4:2258640159000 | 283733651569792 | 51 | 89 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 3 | | INNODB | 2258674859136:306:4:5:2258640159000 | 283733651569792 | 51 | 89 | dbms_fundamentals | test | NULL | NULL | PRIMARY | 2258640159000 | RECORD | S | GRANTED | 1 | +--------+-------------------------------------+-----------------------+-----------+----------+-------------------+-------------+----------------+-------------------+------------+-----------------------+-----------+--------------------+-------------+------------------------+

由于查询语句是非精准匹配模式(扫描区间为 id = (-∞,+∞))、非唯一性搜索,且隔离级别为reapeatable read,因此所有的记录都加上了锁(准确来说是next-key型锁),包括innodb为我们伪造的最大值记录也被加锁了。

innodb中的insert语句一般不会有锁结构,除非遇到了gap锁(next-key锁也算是一种gap锁,它是gap和record的结合),则会生成一个插入意向锁,见上第二个条目,显然X型的插入意向锁与S型别的next-key锁互斥。

mysql-mvcc原理

一些概念

首先介绍mysql中的事务id,以及两个隐藏列。事务id越大表示这个事务越晚开启,越“年轻”。

MySql的记录有两个隐藏列,如果记录被修改了,则在一个隐藏列中记录做出修改动作的事务id(trx_id)。另外一个隐藏列是rollpointer,它在逻辑上指向记录的上一个版本。

当一个事务修改某条记录时,就会将事务id存放于记录的隐藏列中,然后将rollpointer指向上一个版本的记录。

比如,张飞考了59分,老师想捞他一把,将分数改为60,而修改这条记录的trx_id为50:

image-20230308225249673

rollpointer连成的链表被称为版本链

为避免混淆,我将使用"trx_id"表示隐藏列的值,使用"cur_trx_id"表示执行该修改操作的事务id。

那么innodb怎样在不加锁的情况下,使用readview保证事务的隔离性?

首先,readview由下面几个部分组成

  • 生成readview时,系统中活跃的、未提交的读写事务的事务id列表(m_ids),并为了执行效率和一些其他功能,单独维护了活跃列表的最小值与最大值(这里的最大值是,下一个事务应该分配的事务id)。
  • 本事务的事务id(cur_trx_id)

当前事务使用快照读访问某条记录时,比较记录隐藏列中的trx_id与readview中的事务id集合(记作m_ids)判断可见性,分三种情况:

  • 如果trx_id = cur_trx_id,表示当前事务正在修改该版本的记录,因此该版本可见。

  • trx_id < m_ids列表中最小的事务id,表示这条记录比readveiw所有的事务都“老”,包括当前事务cur_trx_id
    因此,该版本可以被当前事务访问。

  • trx_id > m_ids列表中最大的事务id,表示这条记录比readveiw所有的事务都“年轻”,包括当前事务cur_trx_id
    因此,该版本不可以被当前事务访问。

  • m_ids列表中最小的事务id < trx_id < m_ids列表中最大的事务id

    • 若trx_id在m_ids中,说明创建 ReadView 时生成该版本的事务还是活跃的,该版本不可以被访问。
    • 若trx_id不在m_ids中,说明创建 ReadView 时生成该版本的事务已经被提交,该版本可以被访问。

如果某版本不可见,那么就沿着rollpointer组成的版本链往前查找更旧的版本。

一句话概括:read_view中显示包含了当前所有的活跃的、未提交的事务id,也隐式说明了“未来”事务的事务id范围;在判断一条记录的可见性时,该记录隐藏列trx_id如果既不在read_view中,也不“来自未来”,则该条记录对本事务可见,否则沿着版本链依次寻找。

值得注意的是,innodb的事务只有在对记录进行改动时才会被分配真正的事务id,如果是一个只读事务,那么它的trx_id等于0

ReadCommit与RepeatableRead隔离级别

我们主要聚焦于这个问题:为什么repeatable read隔离级别下没有不可重复读现象,而read committed隔离级别下有,明明都是使用的MVCC机制?

主要原因是readveiw的生成时机不同

  • read committed隔离级别下,每一次快照读都会更新readview
  • repeatable read 隔离级别下,只有在事务一开始的那个快照读会产生readview以外,其他快照读不会更新readview

现在来重新想一想,read committed 隔离级别下,为什么出现不可重复读现象?因为事务1在两次读操作中分别使用了两个readview,导致版本的可见性判断会有所不同。但是,在repeatable read条件下就不同了,事务1至始至终都只有一开那一个readview,之后也不会更新它了,因此对记录可见性的判断不会改变。

举个例子,假设先后开启两个事务,事务1查询两次张飞的成绩,事务2则更新张飞的数据。假设两个事务的调度为:

事务1 trx_id = 0 事务2 trx_id = 50
begin
select
begin
update
commit
select
commit

假设事务2被赋予的事务id为50,且注意事务1的事务id一直是0,因为它不修改数据(没有update、insert、delete等修改数据的语句)。

假设在两个事务开始前,张飞这条记录被trx_id为30的事务插入,那么:

image-20230309172313313

  1. 首先讨论在ReadCommited隔离级别下的行为记录

    事务1开启,并执行第一次select时生成readview,虽然不能知晓整个系统的所有事务id,但是至少可以知道readview的m_ids的值的范围一定是:大于30且小于50。

    image-20230309173205202

    然后切换至事务2,事务2修改了这条记录,并提交,那么版本链改为:

    image-20230309173245283

    最后切换回事务1,再次执行select此时生成一个新的readview。虽然不能知晓整个系统的所有事务id,但是至少可以知道readview的的m_id中一定不包括事务2的事务id,因为它已经提交了,而且m_id的最大值一定大于50。那么新版本此时对事务1时可见的,那么就发生了不可重复读现象

    image-20230309174546876

  2. 接着是ReadRepeatable隔离级别。记住ReadRepeatable隔离级别下的ReadView只会在第一次快照读时生成,之后无论执行多少次快照读都不会生成新的readview。

    在相同的调度下,事务1与2的行为如下:

    image-20230309175230813

    前两步时没有任何区别的,但是当事务2进行第二次Select时,ReadView不会更新,它会认为trx_id = 50这个事务修改的记录来自“未来”,是不可见的:

    image-20230309175607852

    因此事务1在RepeatableRead隔离级别下两次读到的成绩都是59,没有发生不可重复读情况。

RepeatableRead隔离级别下的幻读

粗浅地认为在Mysql中,RepeatableRead级别下,只读事务(都是快照读)是不会出现幻读现象的。

还是举例子:假设初始状态如下,只有一条数据

image-20230309172313313

假设事务1,使用两次快照读;事务2使用insert插入新的记录。其调度如下:

事务1 trx_id = 0 事务2 trx_id = 50
begin
select *
begin
insert(2,'关羽',70)
commit
select *
commit

事务1第二次快照读时的示意图如下,由于在RepeatableRead隔离级别下,因此readview不会更新:

image-20230309192803212

可以看到,事务1是一个只读事务,它不会有幻读现象的发生。

但如果事务1不是只读事务,而且更巧的是,事务1修改了事务2新插入的记录

事务1 trx_id = 51 事务2 trx_id = 50
begin
select *
begin
insert(2,'关羽',70)
commit
update set sore = 60 where name = '关羽'
select * # 这是会多出来一条记录,幻读发生
commit

注意此时事务1修改了记录,那么系统会分配事务1一个事务id而不再是0,假设被分配到的事务id为51。那么此时该记录的隐藏列将被改写,且rollpointer指向上个版本。

此后事务1再次,快照读时,虽然readview没有改变,但是新版本的trx_id == cur_trx_id, 因此新版本就视为可见,此时发生幻读:

image-20230309193637438

发生幻读的本质原因应该是,update、delete、insert这类更新操作使用的是基于锁的并发控制协议,它们不用管啥版本链,直接获取最新版本。

另外,虽然没看过源码,但是我猜测当前读语句(select ... for update),也是直接读取的最新版本,你可以看看在RepeatableRead隔离级别下,使用select ... for update也能产生幻读的一个例子

本文作者:别杀那头猪

本文链接:https://www.cnblogs.com/HeyLUMouMou/p/17009250.html

版权声明:本作品采用知识共享署名-非商业性使用-禁止演绎 2.5 中国大陆许可协议进行许可。

posted @   别杀那头猪  阅读(298)  评论(0编辑  收藏  举报
点击右上角即可分享
微信分享提示
💬
评论
📌
收藏
💗
关注
👍
推荐
🚀
回顶
收起