搞懂MySQL事务隔离级别请参考《上个厕所的功夫,搞懂MySQL事务隔离级别》
Mysql 隔离级别有以下四种(级别由低到高):
| 隔离级别 | 效果 |
| :-- | :-- |
| 读未提交(RU) | 一个事务还没提交时,它做的变更就能被别的事务看到。(别的事务指同一时间进行的增删改查操作) |
| 读提交(RC) | 一个事务提交(commit)之后,它做的变更才会被其他事务看到。 |
| 可重复读(RR) | 一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。
当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。 |
| 串行(xíng)化(S) | 正如物理书上写的,串行是单线路,顾名思义在MySQL中同一时刻只允许单个事务执行,“写”会加“写锁”,“读”会加“读锁”。
当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。 |
搞懂了隔离级别以及实现原理其实就可以理解ACID里的隔离性了。前面说过原子性,隔离性,持久性的目的都是为了要做到一致性,但隔离型跟其他两个有所区别,原子性和持久性是为了要实现数据的正确、可用,比如要做到宕机后的恢复、事务的回滚等,保证数据是正确可用的!
那么隔离性是要做到什么呢?
隔离性要管理的是:多个并发读写请求(事务)过来时的执行顺序。像交警在马路口儿指挥交通一样,当并发处理多个DML更新操作时,如何让事务操作他该看到的数据,出现多个事务处理同一条数据时,让事务该排队的排队,别插队捣乱,保证数据和事务的相对隔离,这就是隔离性要干的事儿。
所以,从隔离性的实现原理上,我们可以看出这是一场数据的可靠性与性能之间的权衡。
4、一致性原理
一致性,我们要保障的是数据一致性
,数据库中的增删改操作,使数据库不断从一个一致性的状态转移到另一个一致性的状态
。
事务该回滚的回滚,该提交的提交,提交后该持久化磁盘的持久化磁盘,该写缓冲池的写缓冲池+写日志;对于数据可见性,通过四种隔离级别进行控制,使得库表中的有效数据范围可控,保证业务数据的正确性的前提下,进而提高并发程度,支撑服务高QPS的稳定运行,保证数据的一致性
,这就是咱们叨叨叨说的清楚想不明白的数据库ACID四大特性。
课间休息,欣赏一下来自咱们SQL大腿群
同学的搬砖工地,坐标:杭州。
=====================================================================================
并发场景下MySQL事务可能会出现脏读、幻读、不可重复读问题;
-
脏读(Drity Read):某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,则后一个事务所读取的数据就会是不正确的。
-
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致,这可能是两次查询过程中间插入了一个事务更新了原有的数据。
-
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致,例如有一个事务查询了几列(Row)数据,而另一个事务却在此时插入了新的几列数据,先前的事务在接下来的查询中,就会发现有几列数据是它先前所没有的。
追问1:那Innodb是如何解决幻读问题的呢?
先说结论,MySQL 存储引擎 InnoDB 在可重复读(RR)隔离级别下是解决了幻读问题的。
方法是通过next-key lock在当前读事务开启时,1.给涉及到的行加写锁(行锁)防止写操作;2.给涉及到的行两端加间隙锁(Gap Lock)防止新增行写入;从而解决了幻读问题。
幻读出现的场景:
- 幻读出现在可重复读(RR)隔离级别下,普通的SELECT查询就是快照读,是不会看到别的事务插入的数据的。因此,幻读在“当前读”下才会出现。(当前读会生成行锁,但行锁只能锁定存在的行,针对新插入的操作没有限定)
- 上面 session B 的修改结果,被 session A 之后的 select 语句用“当前读”看到,不能称为幻读。幻读仅专指“新插入的行”。
- 因为这三个查询都是加了 for update,都是当前读。而当前读的规则,就是要能读到所有已经提交的记录的最新值。并且,session B 和 sessionC 的两条语句,执行后就会提交,所以 Q2 和 Q3 就是应该看到这两个事务的操作效果,而且也看到了,这跟事务的可见性规则并不矛盾。
幻读场景实例:
测试表数据如下:
mysql> select * from LOL;
±—±-------------±-------------±------+
| id | hero_title | hero_name | price |
±—±-------------±-------------±------+
| 1 | 刀锋之影 | 泰隆 | 6300 |
| 2 | 迅捷斥候 | 提莫 | 6300 |
| 3 | 光辉女郎 | 拉克丝 | 1350 |
| 4 | 发条魔灵 | 奥莉安娜 | 6300 |
| 5 | 至高之拳 | 李青 | 6300 |
| 6 | 无极剑圣 | 易 | 450 |
| 7 | 疾风剑豪 | 亚索 | 6300 |
±—±-------------±-------------±------+
7 rows in set (0.00 sec)
下面是一个出现幻读情况的示例流程:
| 时刻T | Session A | Session B | Session C |
| — | :-- | — | — |
| T1 | begin;
– Query1
select * from LOL where price=450 for update;
Result:(6,‘无极剑圣’,450) | | |
| T2 | | update LOL set price=450 where hero_title = ‘疾风剑豪’; | |
| T3 | – Query2
select * from LOL where price=450 for update;
Result:(6,‘无极剑圣’,450),(7,‘疾风剑豪’,450) | | |
| T4 | | | insert into LOL values(10,‘雪人骑士’,‘努努’,‘450’); |
| T5 | – Query3
select * from LOL where price=450 for update;
Result:(6,‘无极剑圣’,450),(7,‘疾风剑豪’,450),(10,‘雪人骑士’,450) | | |
| T6 | commit; | | |
可以看到,session A 里执行了三次查询,分别是 Q1、Q2 和 Q3。它们的 SQL 语句相同,都是 select * from LOL where price=450 for update。这个语句的意思你应该很清楚了,查所有 price=450 的行,而且使用的是当前读,并且加上写锁。现在,我们来看一下这三条 SQL 语句,分别会返回什么结果。
-
Q1 只返回 “无极剑圣” 这一行;
-
在 T2 时刻,session B 把 “疾风剑豪” 这一行的 price 值改成了 450,因此 T3 时刻 Q2 查出来的是 “无极剑圣” 和 “疾风剑豪” 这两行;
-
在 T4 时刻,session C 又插入一行 (10,‘雪人骑士’,‘努努’,‘450’),因此 T5 时刻 Q3 查出来 price = 450 的是"无极剑圣" 、“疾风剑豪” 和 “雪人骑士” 这三行。
其中,Q3 读到 (10,‘雪人骑士’,450) 这一行的现象,被称为“幻读”。也就是说,幻读指的是一个事务在前后两次查询同一个范围的时候,后一次查询看到了前一次查询没有看到的行。
解决幻读原理分析
如果你看到了这里,那么我会默认你了解了脏读 、不可重复读与可重复读。如果还不清楚可以先参阅《上个厕所的功夫,搞懂MySQL事务隔离级别》
场景如上,场景隔离级别为RR,当前读。
一、原理解读
那么幻读能仅通过行锁解决么?答案是否定的,如上面示例,首先说明一下,select xx for update(当前读)
是将所有条件涉及到的(符合where条件)行加上行锁。但是,就算我在select xx for update 事务开启时将所有的行都加上行锁。那么也锁不住Session C新增的行,因为在我给数据加锁的时刻,压根就还没有新增的那行,自然也不会给新增行加上锁。
所以要解决幻读,就必须得解决新增行的问题。
现在你应该明白了,产生幻读的原因是:行锁只能锁住行,但是新插入记录这个动作,要更新的是记录之间的“间隙”。因此,为了解决幻读问题,InnoDB 只好引入新的锁,也就是间隙锁 (Gap Lock)。顾名思义,间隙锁,锁的就是两个值之间的空隙。比如文章开头的表 LOL,初始化插入了 7 个记录,这就产生了 8 个间隙。
二、next-key lock
这样,当你执行 select * from LOL where hero_title = ‘疾风剑豪’ for update 的时候,就不止是给数据库中已有的 7 个记录加上了行锁,还同时加了 8 个间隙锁。这样就确保了无法再插入新的记录,也就是Session C在T4新增(10,‘雪人骑士’,‘努努’,‘450’) 行时,由于ID大于7,被间隙锁(7,+∞)锁住。
在一行行扫描的过程中,不仅将给行加上了行锁,还给行两边的空隙,也加上了间隙锁。MySQL将行锁 + 间隙锁组合统称为 next-key lock,通过 next-key lock 解决了幻读问题。
注意:
next-key lock的确是解决了幻读问题,但是next-key lock在并发情况下也经常会造成死锁。死锁检测和处理也会花费时间,一定程度上影响到并发量。
=====================================================================================
该问题取自“MySQL江湖路”专栏中的博文:《面试让HR都能听懂的MySQL锁机制,欢声笑语中搞懂MySQL锁》
-
按锁粒度从大到小分类:
表锁
,页锁
和行锁
;以及特殊场景下使用的全局锁
-
如果按锁级别分类则有:
共享(读)锁
、排他(写)锁
、意向共享(读)锁
、意向排他(写)锁
; -
以及Innodb引擎为解决幻读等并发场景下事务存在的数据问题,引入的
Record Lock(行记录锁)
、Gap Lock(间隙锁)
、Next-key Lock(Record Lock + Gap Lock结合)
等; -
还有就是我们面向编程的两种锁思想:悲观锁、乐观锁。
追问1:那你来谈一谈你对表锁、行锁的理解吧。
表锁
表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单,带来的系统负面影响最小
。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定,所以可以很好的避免困扰我们的死锁问题。
当然,锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高,大大降低并发度。
使用表级锁定的主要是MyISAM,MEMORY,CSV等一些非事务性存储引擎。
行锁
与表锁正相反,行锁最大的特点就是锁定对象的颗粒度很小,也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小,所以发生锁定资源争用的概率也最小,能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。
虽然能够在并发处理能力上面有较大的优势,但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小,所以每次获取锁和释放锁需要做的事情也更多,带来的消耗自然也就更大了。此外,行级锁定也最容易发生死锁
。
使用行级锁定的主要是InnoDB存储引擎。
适用场景
:从锁的角度来说,表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新数据的情况,同时又有并发查询的应用场景。
页锁
除了表锁、行锁外,MySQL还有一种相对偏中性的页级
锁,页锁是MySQL中比较独特的一种锁定级别,在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间,所以获取锁定所需要的资源开销,以及所能提供的并发处理能力也同样是介于上面二者之间。另外,页级锁定和行级锁定一样,会发生死锁。
使用页级锁定的主要是BerkeleyDB存储引擎。
追问2:那全局锁是什么时候用的呢?
首先全局锁,是对整个数据库实例加锁。使用场景一般在全库逻辑备份
时。
MySQL提供加全局读锁的命令:Flush tables with read lock
(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后,数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。
风险:
-
如果在主库备份,在备份期间不能更新,业务停摆
-
如果在从库备份,备份期间不能执行主库同步的binlog,导致主从延迟同步
还有一种锁全局的方式:set global readonly=true
,相当于将整个库设置成只读状态,但这种修改global配置量级较重,和全局锁不同的是:如果执行Flush tables with read lock
命令后,如果客户端发生异常断开,那么MySQL会自动释放这个全局锁,整个库回到可以正常更新的状态。但将库设置为readonly后,客户端发生异常断开,数据库依旧会保持readonly状态,会导致整个库长时间处于不可写状态,试想一下微信只能看,不能打字~~
追问2:那你再说一下按锁级别划分的那几种锁的使用场景和理解吧?
MySQL基于锁级别又分为:共享(读)锁
、排他(写)锁
、意向共享(读)锁
、意向排他(写)锁
对于共享(读)锁
、排他(写)锁
,比如咱们住酒店,入住前顾客都是有权看房的,只看不住想白嫖都是可以的,前台小姐姐会把门给你打开。当然,也允许不同的顾客一起看(共享 读
),比如和这
《一线大厂Java面试题解析+后端开发学习笔记+最新架构讲解视频+实战项目源码讲义》
【docs.qq.com/doc/DSmxTbFJ1cmN1R2dB】 完整内容开源分享
位杀马特小伙子。
看房时房间相当于公共场所,小姐姐嘱咐不能乱涂乱画,也不能偷喝免费的矿泉水。。如果你觉得不错,偷偷跑到前台要定这间房,交钱后会给你这个房间的钥匙并将房间状态改为已入住,不再允许其他人看房(排他 写)。
对了,当办理入住时前台小姐姐也会通知看房的杀马特小伙子说这间房已经有人定了!!等看房的杀马特小伙儿骂骂咧咧出门后,看到满头大汗的你,鄙夷着咽了一口口水,咳tui!然后你锁上门哼着歌儿,开始干那些见不得人的事儿~~直到你退房前,其他人无法在看你的房
。
可见,读锁是可以并发获取的(共享的),而写锁只能给一个事务处理(排他的)。当你想获取写锁时,需要等待之前的读锁都释放后方可加写锁;而当你想获取读锁时,只要数据没有被写锁锁住,你都可以获取到读锁,然后去看房。
另外还有意向读\写锁
,严格来说他们并不是一种锁,而是存放表中所有行锁的信息。就像我们在酒店,当我们预定一个房间时,就对该行(房间)添加 意向写锁
,但是同时会在酒店的前台对该行(房间)做一个信息登记(旅客姓名、男女、住多长时间、家里几头牛等)。大家可以把意向锁当成这个酒店前台,它并不是真正意义上的锁(钥匙),它维护表中每行的加锁信息,是共用的。后续的旅客通过酒店前台来看哪个房间是可选的,那么,如果没有意图锁,会出现什么情况呢?假设我要住房间,那么我每次都要到每一个房间看看这个房间有没有住人,显然这样做的效率是很低下的。杀马特小伙儿表示支持!
读写锁、意向锁的兼容性如下所示;
| 锁类型
| 读锁 | 写锁 | 意向读锁 | 意向写锁 |
| — | — | — | — | — |
| 读锁 | 兼容 | 冲突
| 兼容 | 冲突
|
| 写锁 | 冲突
| 冲突
| 冲突
| 冲突
|
| 意向读锁 | 兼容 | 冲突
| 兼容 | 兼容 |
| 意向写锁 | 冲突
| 冲突
| 兼容 | 兼容 |
我们再回到MySQL原理上讲
1、共享(读)锁(Share Lock)
共享锁,又叫读锁,是读取操作(SELECT)时创建的锁。其他用户可以并发读取数据,但在读锁未释放前,也就是查询事务结束前,任何事务都不能对数据进行修改(获取数据上的写锁),直到已释放所有读锁。
如果事务A
对数据B
(1024房)加上读锁后,则其他事务只能对数据B
上加读锁,不能加写锁。获得读锁的事务只能读数据,不能修改数据。
SQL显示加锁写法:
SELECT … LOCK IN SHARE MODE;
在查询语句后面增加LOCK IN SHARE MODE,MySQL就会对查询结果中的每行都加读锁,当没有其他线程对查询结果集中的任何一行使用写锁时,可以成功申请读锁,否则会被阻塞。其他线程也可以读取使用了读锁的表,而且这些线程读取的是同一个版本的数据。
2、排他(写)锁(Exclusive Lock)