目录
1.更新丢失--mysql所有事务隔离级别都可以在数据库层面上均可避免
2.脏读——read-committed事务隔离级别以上可避免
3.不可重复读——repeatable-read事务隔离级别以上可避免
InnoDB在可重复读(repeatable-read)隔离级别下如何避免幻读
事务四大特性(ACID)
1.原子性( Atomicity ):事务是不可分割的最小操作单元,要么全部成功,要么全部失败。
举例说明:假如将我要超市买东西作为一个事务,那么就必须要完成挑选,结账,付款的操作。这些步骤,缺一不可,不可再分。
2.一致性( Consistency ):事务完成时,必须使所有的数据都保持一致状态。
举例说明:假如事务A指的是A给B转账1k以元那么一致性就体现在,转账结束之后,A的账户必须减少1k,B的账户必须增加1k。
3.隔离性( Isolation ):数据库系统提供的隔离机制,保证事务在不受外部并发操作影响的独立环
境下运行。
4.持久性(Durability ):事务一旦提交或回滚,它对数据库中的数据的改变就是永久的。
接下来我们来讨论
事务并发访问引起的问题以及如何避免
1.更新丢失--mysql所有事务隔离级别都可以在数据库层面上均可避免
大致模拟更新丢失的问题过程:
一个事务的更新覆盖另一个事务的更新,
由于现在主流数据库的自动加锁避免了这种问题发发生。
2.脏读——read-committed事务隔离级别以上可避免
一个事务读到另一个事务未更新的数据
模拟:(原本账户上有1k块钱)
先查看数据库的隔离级别,调低:
section1:
select @@tx_isolation;
set session transction isolation level read uncommitted;//将隔离级别设置成最低
start transaction;//开始事务
update account_innodb set balance = 1000 - 100 where id = 1;
select * from account_innodb;//此时查询余额变为900
注:此时并未提交事务
section2:
select @@tx_isolation;
set session transction isolation level read uncommitted;//将隔离级别设置成最低
start transaction;//开始事务
select * from account_innodb where id = 1;//此时查询余额变为900
section1:发生网络问题取款失败,发生回滚
select @@tx_isolation;
set session transction isolation level read uncommitted;//将隔离级别设置成最低
start transaction;//开始事务
update account_innodb set balance = 1000 - 100 where id = 1;
select * from account_innodb where id = 1;//此时查询余额变为900
rollback;//回滚数据变为1k
section2:并不知道section1
select @@tx_isolation;
set session transction isolation level read uncommitted;//将隔离级别设置成最低
start transaction;//开始事务
select * from account_innodb where id =1;//此时查询余额变为900
update account_innodb set balance = 900 +200 where id = 1;
commit;//数据变为1100
此时section2发现问题,而数据库将会如何避免呢:
将数据库隔开级别调回read-commited:
section1:
start transaction;//开始事务
update account_innodb set balance = 1000 - 100 where id = 1;
select * from account_innodb;//此时查询余额变为900
section2:
start transaction;//开始事务
select * from account_innodb where id =1;//此时查询余额为1000,未读取,避免脏读
update account_innodb set balance = 1000 +200 where id = 1;//1200
section1:遇到网络问题回滚
start transaction;//开始事务
update account_innodb set balance = 1000 - 100 where id = 1;
select * from account_innodb where id = 1;//此时查询余额变为900
rollback;//回滚数据变为1k
section2:
start transaction;//开始事务
select * from account_innodb where id =1;//此时查询余额为1000,未读取,避免脏读
update account_innodb set balance = 1000 +200 where id = 1;//1200
commit;//1200
这时候section2读取的数据是正确的。
因此read-committed,顾名思义,只能读取其他事务已经提交的数据
3.不可重复读——repeatable-read事务隔离级别以上可避免
重复读:事务A(section1)多次重复读取同一个数据,在读取过程中,事务B在事务A读取过程中,对数据做了更新和提交,事务A读取数据不同。
section2:(最开始account_innodb=1000)
select @@tx_isolation;//此时是read-commited隔离级别
rollback;
start transaction;//开始事务
update account_innodb set balance = balance +300 ;
select * from account_innodb where id = 1;//此时查询余额为1300
注:此时还未提交事务
section1:(注此时section1事务无法读到section2事务未更新的数据)
select @@tx_isolation;//此时是read-commited隔离级别
rollback;
start transaction;//开始事务
select * from account_innodb where id = 1;//此时查询余额为1000
接着 section2:
select @@tx_isolation;//此时是read-commited隔离级别
rollback;
start transaction;//开始事务
update account_innodb set balance = balance +300 ;
select * from account_innodb where id = 1;//此时查询余额为1300
commit;
section1:
select @@tx_isolation;//此时是read-commited隔离级别
rollback;
start transaction;//开始事务
select * from account_innodb where id = 1;//此时查询余额为1000
select * from account_innodb where id = 1;//此时查询余额为1300
此时section1并不知道section2提交事务,对数据做出了修改,读取了不同的数据,那么数据库将
会如何避免该问题呢:
section1/section2:(将事务隔离级别设置成repeatable read)
select @@tx_isolation;
set session transction isolation level repeatable read;//将隔离级别设置成repeatable read
section2:(最开始account_innodb=1000)
select @@tx_isolation;
set session transction isolation level repeatable read;//将隔离级别设置成repeatable read
select * from account_innodb where id = 1;//此时余额为1000
update account_innodb set balance = balance +400 ;
select * from account_innodb where id = 1;//此时查询余额为1400
注:此时未提交事务
section1:(无法读取section2未提交的数据)
select @@tx_isolation;
set session transction isolation level repeatable read;//将隔离级别设置成repeatable read
select * from account_innodb where id = 1;//此时余额为1000
接着section2提交事务,commit,我们会发现:
section1:
select @@tx_isolation;
set session transction isolation level repeatable read;//将隔离级别设置成repeatable read
select * from account_innodb where id = 1;//此时余额为1000
select * from account_innodb where id = 1;//此时读取余额任然为1000
update account_innodb set balance = balance - 100 where id = 1;//但此时是用1400-100
commite;
我们发现在更新数据时,账户余额是使用的section2未更新的数据1400来进行操作的。于是就是
1400-100=1300;
因此避免了更新数据时的紊乱,这就是repeatable-read
4.幻读——serializable事务级别可以避免
幻读:事务A读取与搜索条件相匹配的若干行,事务B以插入或删除行等方式来修改事务A的结果积,让事务A看起来像幻觉一样。
section1/section2:同时开始事务(此时事务隔离级别为repeatable read)
section1:
select @@tx_isolation;
start transction;
select * from account_innodb lock in share mode;//当前读,读取事务最新更新的数据,即添加了一个贡献读锁。
secton2:
select @@tx_isolation;
start transction;
insert into account_innodb values(4,'newman',500);
section1:
select @@tx_isolation;
start transction;
select * from account_innodb lock in share mode;
update accunt_innodb set balance = 1000;
section1在section2更新之后,紧接着将数据余额全部更新为1000,幻读就是本来应该更新三条记录,但是莫名更新了四条记录。(理论,并未认证)
然而当我们对第四条记录进行插入操作:
发现插入操作被锁,需要section1提交才能插入。此时对于section1来说插入数据并未出现,可mysql的innodb居然在repeatable-read的情况下避免了幻读的情况,理论上是不可避免的,那么MySQl是怎么做到的呢?
首先我们先来模拟幻读的问题情况:
先将之前的数据rollback;将隔离级别调成read commit
section1:(以当前读来打开)
select @@tx_isolation;
set session transaction isolation level read committed;
start transction;
select * from account_innodb lock in share mode;
section2:
select @@tx_isolation;
set session transaction isolation level read committed;
start transction;
insert into account_innodb values(4,'newman',500);
与之前不同的是,此时插入是成功的,接着commit,提交事务。
section1:
select @@tx_isolation;
set session transaction isolation level read committed;
start transction;
select * from account_innodb lock in share mode;
update accunt_innodb set balance = 1000;
此时:
这时候幻读情况出现了,section1不知道section2的操作,本来只是对三条数据做更新,结果更新
了四条数据。
如何彻底避免幻读呢?
把事务隔离级别调成最高的serializable。先将section1,2事务提交,重新进行一遍之前的操作
section1:
select @@tx_isolation;
set session transaction isolation level serializable;
start transaction;
set * from account_innodb;
updata account_innodb set balance = 1000;
注:只有在隔离级别为serializable下,所有的mysql执行都会上锁,即便我们不写lock in share mode。
此时显示的是四条数据:(这是上一次事务提交之后的)
接着section2:
我们运行发现,在隔离级别为serializable下,插入被锁住了,需要等待section1提交事务,或者rollack。
就解决了幻读的情况。
事务并发访问引起的问题以及如何避免-总结
注 :1. 不可重复读侧重对数据的修改,幻读侧重新增和删除。
2.出于性能考虑,事务隔离级别越高,数据约安全,串行化越严重,但是性能越低,所以我们要选择适合的。
InnoDB在可重复读(repeatable-read)隔离级别下如何避免幻读
表象:快照读(非阻塞读)--伪MVCC
内在:next-key锁(行锁+gap锁)
当前读:(加了锁的增删改查)
select...lock inshare mode,select...for update//加共享锁
update,delect,insert//加排它锁
读取记录的最新版本,读取之后需要保证其他并发事务不能修改当前记录,对读取的记录加锁。
快照读:不加锁的非阻塞读,select(以事务隔离级别不为serializable的条件下成立)
基于提升并发性能的考虑,快照读的实现是基于多版本并发性控制即mvcc,可以认为mvcc是行级
锁的一个变种,但在很多情况下避免了加锁操作,因此开销更低。所以它可
能读取的并不是数据的最新更新。
接下来看几个例子:(事务级别在rc和rr以下)
section1/section2:事务隔离级别为rc
section3/4:事务隔离级别为rr
section1、section2:同时开启事务,
section1:
select @@tx_isolation;
start transaction;
select * from account_innodb where id = 2;
section2:
select @@tx_isolation;
start transaction;
select * from account_innodb where id = 2;
update account_innodb set balance = 600 where id = 2;
commit;
在更新完数据时候之后,我们进行提交。接着我们在section1中分别为用快照读和当前读来查看数据:
我们发现在rc隔离级别下,当前读和快照读的更新版本是一样的。
接着在rr隔离级别下演示:
section3:
select @@tx_isolation;
start transaction;
select * from account_innodb where id = 2;
此时账户余额为上次提交的600
section4:
select @@tx_isolation;
start transaction;
update account_innodb set balance = 300 where id = 2;
commit;
更新数据之后提交,接下来我们在section1中分别用当前读和快照读来查看数据:
当前读:(数据最新版本)
快照读:(返回的数据与没修改之前一致)
那么是否存在快照读返回的数据是最新的?
我们先对事务进行提交,接着
我们直接对section4:
select @@tx_isolation;
start transaction;
update account_innodb set balance = 30 where id = 2;
commit;
接着我们在section3中用当前读来查看:
接下来我们用快照读来查看:(此时读取的是最新版本)
那么这一次相对与上一次的区别是什么呢:
1.在上一次中我们先在section1中先使用了一次快照读来读取数据,接着section2更更新,最后section再使用快照读,读取的是旧版本的数据。
2.在这一次中,我们直接在section2中更新数据,在section1中用快照读查看数据,这时候得到的是数据的最新版
重点:创建快照的时机,决定读取数据的版本
RC、RR级别下的InnoDB的非阻塞读如何实现
1.DB_TRX_ID:顾名思义,与事务相关,用来标识最近一次对本行记录的修改。
2.DB_ROLL_PTR:回滚指针,指写入回滚段,rollback segment的undo日志记录,如果一行被记录被更新,则undo log record,包含重该行记录被更新之前内容所必须包含的信息。
3.DB_ROW_ID即行号,包含一个随着新行插入而单调递增的行ID,当由innodb自动自动产生聚集
索引时,聚集索引会包括这个行ID的值,否则这个行ID不会出现在任何索引中。
如果一个innodb的表即没有主键,也没有唯一键,innodb会为我们创建一个自身隐藏主键字段,即这里的DB_ROW_ID。
光有以上三个字段并不能实现我们的快照读,还需要依靠undo日志,当我们对数据进行变更操作时,就会产生undo记录。里面存储的是老版本的数据。为了能够读取老版本的数据,需要顺着undo链找到满足其可见性的记录。
undolog分为两种:
1.insert undo log 表示事务对insert 新记录的undo log 只在事务回滚时需要,提交后删除
2.update undolog 在事务回滚时需要,在快照时也需要,不能随便删除,只有当数据库所使用的快照中,不涉及该日志记录对应的回滚日志,才会被purge线程删除。
日志的工作方式:(事务对行记录的更新过程)
1.首先用排他锁,锁住该行,接着把该行修改前的值拷贝一份到undo log里面;
2.修改当前行的值,填写事务ID,DB_TRX_ID,使用回滚指针指向undo log中修改前的行
假如数据库中还有其他事务在用快照读读取该日志记录,那么对应的undo log还没被清除,此时某个事务又对同一行数据进行修改,这里将fields3改为了45,这时又多了一条undo日志
现在有了行隐藏列,有了undo日志,只差read view.
read view主要用来做可见性判断,当我们执行快照读select的时候,会针对我们查询的数据创建一个read view来决定当前事务能看见的是哪个版本,有可能是当前最新版本的记录,也有可能只允许看undo kog中某一版本的数据,read view遵循一个可见性算法,主要是讲要修改数据的DB_TRX_ID取出与系统其他活跃事务ID做对比,如果>或者=,则通过DB_ROLL_PTR指针去取出undo log,就是取出上一层的DB_TRX_ID,直到undo log上一层的DB_TRX_ID<系统其他活跃事务ID,就保证了我们获取到的数据版本是当前可见的最稳定的版本。
我们来看一下mysql保存活跃事务的源码:
(补充:每当我们的事务start transaction时,我们的事务ID就会去递增,越新开启的事务,它的ID越大。)
就用这两个值去和DB_TRX_ID做对比,就能觉得是不是让他回溯到undo log去取出适应该版本的数据版本来。
总结:正是因为生成时机的不同,造成rc,rr两种隔离级别的不同可加性。
1.在repeatable read级别下,之后的第一条快照读及read view 将当前系统中活跃的其他事务记录起来,此后在调用快照读时,还是用的同一个read view 。而在read committed级别下,事务中,每条select语句也就是每次调用快照读时,都会创建一个新的快照,这就是我们为什么在rc下能用快照读看到别的事务对已提交事务对表记录的增删改。
2.而在rr级别下,如果首次使用快照读,是在别的事务对数据做出增删改,并且提交之前,那么即便后面别的事务对表进行增删改,并提交,还是读不到数据变更。对与rr来讲,首次select的时机非常重要。
正是因为上面三个因子,才使得innodb在rr或rc级别支持非阻塞读,而读取数据时的非阻塞读机制实现了仿造版的MVCC。
MVCC:multi version concurrency control简称,代表多版本并发控制,读不加锁,读写不冲突,在都读多写少的应用中读写不冲突是非常重要的,极大增加了系统的并发性能。
那为什么这里只实现了伪MVCC?因为并没有实现核心的多版本共存。undo log只是串行化的结果,记录了多个事务的过程,不属于多版本过程。
InnoDB可重复读隔离级别下如何避免幻读
表象:快照读(非阻塞读)--伪MVCC
内在:next-key锁(行锁+gap锁)
行锁:对当个行记录上锁
Gap锁:gap指索引记录中插入的空隙,而间隙锁,即锁定一个范围,但不包括记录本身,目的,防止同一个事务的两次当前读出现幻读的情况。
gap锁在rc以及更低的事务隔离级别下是没有的,这就是在rc以及read uncomitted下无法避免幻读的原因,而在rr和serializable级别下,默认支持gap。
我们主要讨论在rr级别下,gap出现的场景,
对主键索引或者唯一索引会用Gap锁吗?
全部命中:精确查询的时候,所有记录都有,如果只有部分,则为部分命中。
表里有两个列,一个是name是该表的主线,另外一个ID是该表的唯一键,如果我们执行。
delete from table where id=9该如何进行加锁呢?
此时,由于ID是unique索引,因此delete语句会选择走ID这一列的索引进行where条件的过滤,再找到ID=9的记录之后呢,首先会将这个unique索引上的ID为9的索引,加上行锁recollect,同时会根据读到的这个name类回主键索引及我们的密集索引,将这个密集索引上的这个name=d对应的主键索引项也加上一个recollect,也就是排它锁。
为什么密集索引上的记录也要加排它锁?
如果并发的一个SQL是通过主键索引来更新的,update tb 1 set id=90,他将这个ID改成90
where id = d;此时如果delete语句,没有将主键索引上的这个记录加锁,那么并发的这个update
就会感知不到delete语句的存在。这里,我们delete也是操作的是同一个记录,update也是这一
name=d的操作记录,这个记录这样就违背了同一记录上的更新或者删除,需要串行执行的约束。
所以呢,如果where条件全部命中,则不会用gap 锁。
例子:
此时两个事务的隔开级别均为rr:
section1:delete走的是唯一索引(前面添加explain可以查看)
section 2:
执行成功,说明当前读如果走的是唯一索引,并且命中数据,是不会添加gap锁。
那么说明时候会添加gap锁:
section1:(先rollback)删除一个不存在的指
section2:(先rollback)插入一个不存在的指
我们会发现:虽然删除的是7,但是7周围的环境也被锁住了 ,即加了gap锁。
以上是全部命中的情况,接下来来看部分事务命中的情况:(先rollback)
section2:
也就是说对5-9的间隙插入了gap锁,我们继续演示:
但是10并未上锁:
因此:如果是部分命中的话,那么也会是部分加上gap锁。我们再来对比全部命中的情况:
先将两个事务回滚
section1:执行当前读,精确命中所有数据,(将7改为6)
section2:接着我们再去插入不存在的数据,执行是成功的
也就是说,全部命中的情况下是不会上gap锁的。因此,经过实验,我们就得出上述的两个结论如
果where条件全部并重,则不会用gap锁,只会加记录锁。如果where条件部分命中或者全都不命
中,则会加gap锁前面。
Gap锁会用在非唯一索引或者不走索引的当前读中
1.当前读走到非唯一索引的情况
大家可以试想一下,如果我们1事务A第一次用当前读选出ID为9的数据。
我们用这个delete from tb1 whereid=9,这个当前读来选出ID为九的数据,如果只锁住选出来的两
行,那么此后另外一个事物b。插入了ID,同样为9的数据并提交事务,再次用当前读选出ID为九的
数据时,会取出三条。这样就会发生幻读,因此这个时候我们需要引入gap锁。
gap所具体能在什么地方添加呢?
从图中我们可以了解到gap的地方,跟我们走的非唯一索引的值分布有很大的关系,都是一个左
开,右闭的区间。
我们查看官方文档:
除了最后一项到正无穷大之外呢,其他都是左开,右闭的区间。在这些区间内,一旦上了gap锁,
该区间就没办法插入数据了,因此gap锁是用来防止插入的。
对于普通非唯一索引来讲,并不是所有的gap都会去上锁,只会对要修改的地方的周边上gap锁。
2.不走索引的情况
当前读不走索引的时候,它会对所有的gap都上锁,这也就类似锁表了,这样也同样能达到防止换读的效果。
相比走非唯一索引的情况呢?不走索引的情况会对所有gap都上锁,大家可以试试别的gap,
发现同样都被锁住。不过相比表锁,这样上锁的代价更大。这种情况通常是需要避免的,
因为会降低数据库的效率。
总结:这里咱们总结一下innodb-rr级别,主要通过引入next key锁来避免幻读问题,而next key由record lock以及gap lock组成。gap lock,会用在非唯一索引或者不走索引的当前读,以及仅命中检索条件的部分结果积。并且呢,用到这个主键索引以及唯一索引的当前途中。
自我总结:(精简)
1.innodb使用行级锁,而不使用表锁的原因,也是为了提高操作效率,如果使用表锁,那么整个表里的数据都会被锁定,这时候就无法操作。
接着,关于当前读和快照读 我们用银行取钱来举例:
2.当前读:有一个ATM挤,当你取ATM机取钱时,其他人必须等待你取完钱,才能进行操作。
那么官方语言来说,我们的数据一直都是最新版本的数据,如果你的事务操作为执行完,那么其他
事务是无法提交事务,也就意味着操作失败,会进行回滚。
3.快照读:相当于这个银行有很多的柜台,那么是可以多个人同时进行操作的,但是我们只有在提交
数据的最后一刻,会去查看你的前面是否还有人在正在进行操作,如果有,那么你会操作失败,会
回滚。
而innodb使用这个mvcc机制,即快照读就是因为它提高了我们操作的效率。
4.吊打面试官的重点:但是这里我们为什么会说他是伪MVCC机制,是因为,我们这里的快照读,
相当于undo_log串行式链式结构由回滚指针项链,而为多版本数据结构,一个指一个,一个指一
个,必须要保证你前面的人提交事务,那么你才能行操作。而真正的MVCC机制,它是相当于我们
可以多个人同时取钱,但是我们看的是操作速度,谁操作更快,谁就能更新,而更新之后,也就是
DB_ROW_ID会进行+1,那么比你速度慢的人就会发现数据已经被修改。相比与伪MVCC的链式结
构,真正的MVCC是将相同的数据拷贝多份,大家都可以看到一个相同的结果,也就是多个人去看
各个副本(每个副本是一样的),当然MVCC机制不止应用于数据库,只是这里innodb为了提高效
率而应用的。
5、MVCC解决的问题
MVCC是一种用来解决读写冲突的无锁并发控制,也就是为事务分配单项增长的时间戳,为每个修
改保存一个版本,版本与事务时间戳关联,读操作只读该事务开始前的数据库的快照。
因此,MVCC可以为数据库解决以下问题∶
1)、在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作,提
高了数据库并发读写的性能。
2)、解决脏读、幻读、不可重复读等事务隔离问题,但是不能解决更新丢失问题。
mvcc主要是解决读写冲突的,如果是写写的操作就要用锁来解决了,因为mvcc无法解决写的并发安全,所以一般都是 rr事务隔离+mvcc+锁 来保证所有情况安全。