MySQL-事务与锁

288 阅读14分钟

【ACID】

①原子性:保证一组操作要么全部成功,要么全部失败。如果在执行操作的过程中发生了错误,数据库需要把数据变回未执行操作之前的状态。(结合undolog)

②隔离性:对应的数据库操作的执行顺序有一定规律,保证事务之间彼此互不影响(RE:结合mvcc)

③一致性:在一个事务中,数据状态在前后要保持一致性,也就是说其他客户端访问数据时,要么就读到事务之前的数据,要么就读到事务之后的数据。原子性和隔离性都是保证一致性的一种手段。

④持久性:对数据库的状态转换都应该在磁盘上保留下来。(RE:结合redolog、双写缓存)

我们把满足ACID四大特性的一个或多个数据库操作称为一个事务,事务是在引擎层实现的。而我们一般情况下并不能全部满足ACID,所以就有了事务隔离级别。而分布式事务必须兼顾性能和高可用,更加不能完全满足ACID。


【redo log-存储引擎层-Innodb特有】

我们在访问页时,需要把磁盘中的页缓存到内存中的buffer pool中进行操作(mysql用bufferpoll管理内存),也就是说事务首先操作的是内存中的buffer pool,在之后才会被刷入磁盘。但是如果事务提交后突然发生故障导致数据丢失,此时事务操作并没有被写入磁盘,也就无法保证持久性。所以redo log用来记录在事务中 “对数据库所做的修改 和 change buffer的修改记录”,即使buffer pool中的数据丢失,mysql也可以通过redo log来恢复事务操作。(InnoDB对buffer poll的LRU算法做了改进,第一次进入内存的数据页先放入old区,若1秒内没有再访问,则不会把它移到LRU链表头部)

redo log写入流程:当插入或更新一条记录时,InnoDB会先把记录先写入redo log buffer中,事务commit或经过一段时间后,InnoDB会把buffer中的命令循环写入redo log文件中。redo log是循环写的,它通过写入指针和擦除指针来记录写范围。若两个指针重合则表示redo log空间用完,则InnoDB必须停下来然后刷入一些数据到磁盘中以空出redo log,然后再开始接收数据。

bufferpool中有脏页和干净页,那当内存不够用时,mysql就会把页数据刷入磁盘中(宕机、空闲、redolog满都会刷页)。刷页:设置innodb_io_capatity告诉mysql磁盘io能力

【binlog-server层】 redolog是物理日志,记录在页面上做了什么改动(也在磁盘上) binlog是逻辑日志,记录sql语句的原始逻辑


【undo log】

为了实现事务的原子性,在事务执行失败时我们需要把数据恢复成之前的样子,所以就需要undo log。对每条记录做一次改动就会产生一条undo log,用于回滚事务。(RE:太多了...)

【事务隔离级别】

MySQL是一个客户端服务器架构的软件,一个服务器同一时刻会有若干个客户端与之相连。所以服务器可能会在同一时刻处理的多个事务对某一个数据进行并发访问。而事务有隔离性嘛,也就是说这多个事务要串行访问。这样对性能影响太大了,我们既想保持事务的隔离性,又想提高服务器处理事务的性能,那我们要在两者之间找一个平衡点,这就是事务隔离级别。

未提交读:一个事务还没提交,它的变更能被其他事务看到。

发生脏读:一个事务读到了另一个事务未提交的修改了的数据。

已提交读:一个事务提交后,它的变更才能被其他事务看到。

发生不可重复读:一个事务在执行过程中对某个数据多次读取的结果不一致,即读到了其他事务修改后的值。
可重复读(MySQL默认):一个事务在执行过程中看到的数据,总是跟这个事务在启动时看到的数据一致,即事务在执行过程中看到的数据一致。 当前读下发生幻读(普通查询是快照读,有MVCC所以不会发生幻读,而select for update[写锁]是当前读,会发生幻读):一个事务在执行过程中对某个表多次读取的记录数不一致,即读到了其他事务新插入的记录(读到了之前没有读到过的,记录数变少了不算幻读。)

如何解决幻读:幻读是指在事务中的新记录影响了事务的执行,也就是说无法给这些记录加记录锁,因为事务在执行第一次读取操作时这些记录还不存在,所以就需要使用间隙锁。某条记录的间隙锁会锁住这条记录到前一条记录直接的间隙,阻塞插入操作。

MySQL为什么是可重复读:在MySQL5.0之前,binlog只记录语句而不记录行号,所以在Master先执行delete后执行insert时,binlog可能会记成先insert再delete,造成主从数据错乱。所以MySQL默认使用了可重复读。那现在binlog是基于row行格式记录的,不会出现执行顺序的问题。

为什么互联网项目要使用已提交读?

当条件列未命中索引时,已提交读只会锁上不满足条件的列,而可重复读会锁上整个表。

当update/delete没有命中记录时,可重复读会上间隙锁,增加死锁的概率。 已提交读有半一致性读,可以提高update的并发性:select读取update锁上的数据行时,InnoDB会判断上个版本的数据和update的条件语句是否一致。若一致则MySQL会直接发起读操作,读取修改后的新版本。而可重复读只会等待update。

串行化:有读锁和写锁,事务之间完全串行化。

【MVCC】

MVVC实际上指的是在使用read committed和repeatable read这两种隔离级别下的事务,执行普通查询操作时访问当前记录版本链的过程。也就是事务在不同隔离级别下拍快照的时机问题。

在InnoDB中,每行记录也有一个版本链。每次事务更新某行的数据时,都会在此行的版本链最上面生成一个新的数据版本,并把当前事务ID赋给这个新版本,记为row trx_id。【但实际上,这个版本链是虚拟的。在生成新版本时都会生成对应的undo log。我们在需要旧版本时,实际上是根据当前版本和undo log计算出来的】。

那对于不同隔离级别,能够读取到的数据范围不同就相当于能够读到此记录版本链中的哪个版本。所以InnoDB提供了一个ReadView概念。InnoDB在查询某条记录时,顺着此记录版本链依次与ReadView比较,若匹配成功则找到正确读取的记录。所以对于read committed和repeatable read来说,生成ReadView的时机便成为了决定他们区别的主要因素。

【RE~!!08,没看懂!!!!】对于read committed的读操作来说,在一次事务中,它的每个语句之前都生成一个新的ReadView,新的ReadView会匹配到版本链中的隶属于此次事务的最新记录,即解决了不可重复问题。但是新ReadView的生成也就意味着匹配到了版本链中更新的当前事务生成的记录,那么也就会多扫描了其他事务在此次ReadView之前生成的记录,即无法解决幻读。

而对于repeatable read的读操作来说,在整个事务执行期间只在第一次读取数据时生成一个ReadView,那在此事务里的其他查询都共用这一个ReadView,也就是复用它其中的事务ID,也就防止了读到其他事务提交的新数据,[解决了幻读!所以我们想要实现幻读,那么就要用共享读锁!]

但是,对于update或加锁的select,它们会为当前版本的数据行加锁,所以它必须要读到当前值,称为“当前读”。而且其他事务会被锁住,无法读取此数据行直至提交。

分布式事务解决方案

2PC: 本地消息表

本地消息表:

【锁】

锁的实现原理:

MySQL的锁也是为了解决多用户共享资源的并发访问问题。数据库需要合理的控制资源的访问规则,而锁就是用来实现这些访问规则的重要数据结构。锁其实是一个内存结构,当事务向对记录执行改动时,首先会看看内存中有没有与此记录关联的锁结构。若没有直接执行操作;则判断锁的is_waiting属性。若为true则表示锁已被占用,其他事务操作此记录需要等待,此事务执行完成后会把is_waiting属性置为false并唤醒正在等待的其他事务。若为false则加锁成功,置为true并执行操作。

全局锁

对整个数据库实例加全局读锁,会阻塞增删改、修改表结构、提交事务等操作。若不加锁,则全局备份时可能会得到逻辑不一致的视图。但可重复读隔离级别下开启事务,MVCC可以保证一致性视图呀。所以全局锁可以用于MyISAM等不支持事务的引擎。

表级锁

MDL锁是隐式加上的,在事务结束后才被释放。所以如果我们在事务中先查询,再alter改表,再查询,就会发生死锁。所以在长事务中,我们就没法给表加字段了。那么我们可以用SQL语法WAIT设定等待时间。

InnoDB中的行锁: (InndDB行锁锁住的是记录对应的聚簇索引中的叶子节点哦,但为了好描述我在下面直接写记录了)

记录锁:锁住单个记录。 在两个事务中update/insert同一条记录,会自动添加记录锁阻塞。所以在某张表会有update并发操作时,我们尽量把并发行为放到事务的最后执行,减少这个事务占用锁的时间。

间隙锁:该记录到它的前一个记录之间的键值间隙(即当前叶子节点在链表中的前驱节点对应的记录,他们的键值区间即为键值间隙)。对于右开区间,每个数据页中有两条伪记录,分别对应该页面的无穷小记录和无穷大记录,那我们锁住无穷大记录即为锁住了右开区间。

临键锁:记录锁和间隙锁的结合体,会锁住当前记录的左开右闭区间,[解决了幻读!]

插入意向锁、隐式锁:(RE:太多了...)

MySQL死锁 两个事务对某些行做循环引用,就会导致死锁。我们可以设置MySQL锁等待的超时时间,但这样会误杀。所以一般的解决方案是使用MySQL默认的死锁检测,发现死锁后主动回滚某一事务。但死锁检测是每当一个事务(线程)占用锁时,如果这个行上有锁,就要去检测他依赖的线程有没有被其他人锁住,非常浪费CPU。所以我们的解决方案是控制并发量,而且需要在MySQL服务端控制。我们可以修改MySQL源码,对于同行的更新,要在进入引擎前提前排队,减少并发操作。或者我们可以把一行拆分成逻辑上的多行(总和拆多行,更新其中一行),也是减少锁等待的个数。 死锁一般是由锁本身引起的。若为行锁,则是行锁互斥引起的;若为间隙锁,则为锁与插入操作互斥引起的。

① Show engine innodb status查看死锁日志

② 在RR隔离级别下,update/delete若没有命中记录则会在左右区间加上间隙锁,且每个事务中的单条语句执行完成后不立即释放锁,而是继续往下执行所以就导致A事务需要的插入锁会被B事务的临键锁占有,B同理。

③ 锁超时机制、开启死锁检测并回滚事务(构建以事务为定点、锁为边的有向图,判断有向图中是否存在环)

多节点事务(RE极客)

【间隙锁的实现原理,比如age=20,锁的是id还是age?】: 答锁的原理 + 间隙锁 + B+树取得记录、索引条件下推、server层判断大小(RE:公众号-我们都是小青蛙) 【设计全局锁?】:直接对聚簇索引加锁、启动事务保存一致性视图,使用MVCC恢复加锁前的状态。

分析慢查询

查询结果长时间不返还:show processlist命令查看Query语句的状态,若为Waiting for MDL...,则说明有其他线程在表上请求/持有MDL锁,阻塞了select。我们可以找到持有锁的线程,然后kill掉即可。

在开启A事务后,再开启B事务,执行10000次update。执行select * from 时是一致性读,需要从10001开始依次执行undo log,回滚10000次,最终得到最开始的结果。而select * from lock in share mode是当前读,无需回滚。 image.png

在可重复读隔离级别下(遵守两阶段锁协议)

for update,仅仅是锁住加锁时满足条件的行。后续出现新的满足条件的行并没有锁住,最后提交事务时就会对新行有影响,即幻读会产生数据不一致性。 for update是行锁,指的是行锁与行锁之间的互斥。而间隙锁,指的是间隙锁与新插入动作的互斥。 而select * from x for update是加的临键锁,锁住所有行,解决了幻读。 而默认加的间隙锁会使得并发度大大降低,而且可能会出现两个客户端同时加间隙锁而导致后续的死锁。所以现在一般用已提交读 + binlog row格式。

lock in share mode读锁,只会锁住覆盖索引,不会锁主键索引。 for update写锁,会锁住覆盖索引和主键索引。(主键索引中满足条件的行) select * from x where id>=10 and id<11 for update:先等值查询id=10,再往后遍历至id=11,所以若id是唯一索引,则锁住 id=10的行锁和(10,xx]的临键锁;否则,则锁住(x,10],(10,x]

在一个事务中进行增删改,都会默认加临建锁。 加锁的基本单位是next-key lock临键锁。但若碰到的是有关索引的等值查询会有退化现象(树内部搜索时也是等值查询,所以范围边界也是等值哦): 给唯一索引加锁时,临键锁退化为行锁;普通索引要继续遍历并加锁到第一个不满足条件的记录 向右遍历的第一个值不满足等值条件时,临键锁退化为间隙锁(也就是不要不满足条件的值)。 查找过程中,只有访问到的列会加锁,而且锁的是索引啊(无索引锁主键),所以走覆盖索引时不会被普通索引上的锁影响。而limit就可以减少锁的范围,避免它遍历到第一个不满足条件的,所以delete时尽量加limit。