Mysql之事务详解

Mysql之事务详解

事务定义

有时候我们在操作数据库的时候,希望多个数据库操作要不都成功,要不都失败,这个时候就需要用到数据库的事务了。

事务是数据库管理系统(DBMS)执行过程中的一个逻辑单位,由个有限的数据库操作序列构成。

这里面有两个关键点,第一个,所谓的逻辑单位,意味着它是数据库最小的工作单元,是不可以再分的。第二个,它可能包含了一个或者一系列的DML语句,包括insert delete update。

在MySQL所支持的这些存储引擎里面,有哪些是支持事务的呢?

除了做集群的NDB之外,只有InnoDB支持事务,这个也是它成为默认的存储引擎的一个重要原因。

事务的四大特性

事务的四大特性是ACID,A是Atomicity,即原子性,C是Consistent,即一致性,I 是Isolation,即隔离性,D是Durability,即持久性。

原子性

原子性,Atomicity,也就是不可再分,因为原子是化学上(参加化学反应)最小的单位。也就意味着我们对数据库的一系列的操作,要么都是成功,要么都是失败,不可能出现部分成功或者部分失败的情况,以的转账的场景为例,一个账户的余额减少,必然对应着另一个账户余额的增加。

全部成功比较简单,问题是如果前面一个操作已经成功了,后面的操作失败了,怎么让它全部失败呢,这个时候就需要全部回滚。

原子性,在InnoDB里面是通过undo log来实现的,它记录了数据修改之前的值(逻辑日志),一旦发生异常,就可以用undo log来实现回滚操作。

隔离性

隔离性,Isolation,我们有了事务的定义以后,在数据库里面会有很多的事务同时去操作同一张表或者同一行数据,必然会产生一些并发或者干扰的操作。隔离性的定义,就是这些很多个的事务,对表或者行的并发操作,应该是透明的,互相不干扰的。

比如两个人给a转账100,开启两个事务,都拿到了a账户的余额1000,然后各自基于1000加100,最后结果是1100,就出现了数据混乱的问题。

持久性

持久性,Durability,我们对数据库的任意的操作,增删改,只要事务提交成功,那么结果就是永久性的,不可能因为数据库掉电、宕机、意外重启,又变成原来的状态,这个就是事务的持久性。

持久性怎么实现呢?InnoDB崩溃恢复(crash-safe)是通过什么实现的?

持久性是通过redo log和double write buffer(双写缓冲)来实现的,我们操作数据的时候,会先写到内存的buffer pool里面,同时记录redo log,如果在刷盘之前出现异常,在重启后就可以读取redo log的内容,写入到磁盘,保证数据的持久性。当然,恢复成功的前提是数据页本身没有被破坏,是完整的,这个通过双写缓冲保证。

需要注意的是,原子性,隔离性,持久性,最后都是为了实现一致性。

一致性

一致性,Consistent,指的是数据库的完整性约束没有被破坏,事务执行的前后都是合法的数据状态。

数据库自身提供了一些约束:比如主键必须是唯一的,字段长度符合要求,另外还有用户自定义的完整性。

比如说转账的这个场景,A账户余额减少1000,B账户余额只增加了500,两个操作都成功了,它是满足原子性的定义的,但是它不满足用户自定义的一致性,因为它导致了会计科目的不平衡。

还有一种情况,A账户余额为0,如果这个时候转账成功了,A账户的余额会变成-1000,虽然它也满足原子性,但是我们知道,借记卡的余额是不能够小于0的,所以也违反了一致性,用户自定义的完整性通常要在代码中控制。

使用事务

自动开启

InnoDB里面有一个auto commit的参数(分为两个级别,session级别和global 级别),它的默认值是ON。

auto commit 参数的意思是 是否自动提交。

如果它的值是true/on的话,我们在操作数据的时候,会自动提交事务。

1
show variables like 'autocommit';

所以当我们执行一条更新语句时,会自动开启了一个事务,而且自动提交了,最终写入了磁盘。

这个是开启事务的第一种方式,增删改的语句会自动开启事务,当然是一条SQL一个事务。

注意每个事务都是有编号的,这个编号是一个整数,有递增的特性。

如果我们把 auto commit设置成false/off,那么数据库的事务就需要我们手动提交并结束,用rollback或者commit.

手动开启

如果要把多条SQL放在一个事务里面,就要手动开启事务。手动开启事务有两种方式:一种是用begin;一种是用start transaction

结束事务也有两种方式:

第一种是回滚事务rollback,事务结束。

第二种就是提交一个事务,commit,事务结束。

还有一种情况,客户端的连接断开的时候,事务也会结束。

事务并发的问题

脏读

我们有两个事务,一个是事务编号2673,一个是事务编号2674。在第一个事务里,它首先通过一个 where id=1 的条件查询一条数据,返回name=Ada,age=16的这条数据。然后第二个事务,它同样地是去操作id=1的这行数据,它通过一个update的语句,把id=1的这行数据的age改成了18,但是它没有提交。

这个时候,在第一个事务里面,它再次去执行相同的查询操作,发现数据发生了变化,获取到的数据age变成了18。那么,这种在一个事务里面,由于其他的事务修改了数据并且没有提交,而导致了前后两次读取数据不一致的情况,这种事务并发的问题叫做脏读。

不可重复读

同样是两个事务,第一个事务通过id=1查询到了一条数据。然后在第二个事务里面执行了一个update操作,这里注意一下,执行了update以后它通过一个commit提交了修改。然后第一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况。

这种一个事务读取到了其他事务已提交的数据导致前后两次读取数据不一致的情况,叫做不可重复读。

幻读

image-20210823223049650

在第一个事务里面执行了一个范围查询,这个时候满足条件的数据只有一条。在第二个事务里面,它插入了一行数据,并且提交了。重点:插入了一行数据。在第一个事务里面再去查询的时候,它发现多了一行数据,这种情况就好像突然冒出来的一个幻影一样。

一个事务前后两次读取数据数据不一致,是由于其他事务插入数据造成的,这种情况我们把它叫做幻读。

不可重复读和幻读的区别

修改或者删除造成的读不一致叫做不可重复读,插入造成的读不一致叫做幻读。

这里有两点需要注意下

1、一个事务读取到其他事务最新提交的数据,这不是正常的吗?当然是正常的,所以这里讨论的是读一致性。读一致性的意义就是一个事务的select操作跟其他事务没有瓜葛,你不需要修改数据,所以不需要获取最新的数据,这样能够提高并发性能。

2、如果在第一个事务里面,select以后,再执行一个update,就能获取到第二个事务的最新数据,这个怎么解释?同样的,这个也脱离了读一致性的讨论范畴。如果要修改数据,必然会读取到最新的数据,也会影响其他的事务。

总结

脏读,不可重复读,还是幻读,它们都是数据库的读一致性的问题,都是在一个事务里面前后两次读取出现了不一致的情况,读一致性的问题,必须要由数据库提供一定的事务隔离机制来解决。

事务的隔离级别

事务有4种隔离级别

第一个隔离级别叫做:Read Uncommitted(未提交读),一个事务可以读取到其他事务未提交的数据,会出现脏读,所以叫做RU,它没有解决任何的问题。

第二个隔离级别叫做:Read Committed(已提交读),也就是一个事务只能读取到其他事务已提交的数据,不能读取到其他事务未提交的数据,它解决了脏读的问题,但是会出现不可重复读的问题。

第三个隔离级别叫做:Repeatable Read(可重复读),它解决了不可重复读的问题,也就是在同一个事务里面多次读取同样的数据结果是一样的,但是在这个级别下,没有解决幻读的问题。

第四个隔离级别就是:Serializable(串行化),在这个隔离级别里面,所有的事务都是串行执行的,也就是对数据的操作需要排队,已经不存在事务的并发操作了,所以它解决了所有的问题。

InoDB对事务隔离级别的支持情况

lnnoDB支持的四个隔离级别,隔离级别越高,事务的并发度就越低。

唯一的区别就在于,InnoDB在RR的级别就解决了幻读的问题。

也就是说,不需要使用串行化的隔离级别去解决所有问题,既保证了数据的一致性,又支持较高的并发度。

这个就是InnoDB默认使用RR作为事务隔离级别的原因。

实现方案

如果要解决读一致性的问题,保证一个事务中前后两次读取数据结果一致,实现事务隔离,应该怎么做?

总体上来说,有两大类的方案。

LBCC

第一种方案,既然要保证前后两次读取数据一致,那么读取数据的时候,锁定要操作的数据,不允许其他的事务修改就行了。

这种方案叫做基于锁的并发控制Lock Based Concurrency Control (LBCC)。

如果仅仅是基于锁来实现事务隔离,一个事务读取的时候不允许其他事务修改,那就意味着不支持并发的读写操作,而大多数应用都是读多写少的,这样会极大地影响操作数据的效率。

MVCC

所以还有另一种解决方案,如果要让一个事务前后两次读取的数据保持一致,那么可以在修改数据的之前给它建立一个备份或者叫快照,后面再来读取这个快照就行了。

这种方案叫做多版本的并发控制Multi Version Concurrency Control (MVCC)。

MVCC的原则:

一个事务能看到的数据版本:

1、第一次查询之前已经提交的事务的修改

2、本事务的修改

一个事务不能看见的数据版本:

1、在本事务第一次查询之后创建的事务(事务ID比自己的事务ID大)

2、活跃的(未提交的)事务的修改

MVCC的效果:

可以查到在这个事务开始之前已经存在的数据,即使它在后面被修改或者删除了。而在这个事务之后新增的数据,是查不到的。

所以才把这个叫做快照,不管别的事务做任何增删改查的操作,它只能看到第一次查询时看到的数据版本。

这个快照是怎么实现的呢?会不会占用额外的存储空间?

下面来分析一下MVCC的原理。

首先,InnoDB的事务都是有编号的,而且会不断递增。

InnoDB为每行记录都实现了两个隐藏字段:

DBTRXID,6字节:事务ID,数据是在哪个事务插入或者修改为新数据的,就记录为当前事务ID。

DB_ROLL PTR,7字节:回滚指针(可以把它理解为删除版本号,数据被删除或记录为旧数据的时候,记录当前事务ID,没有修改或者删除的时候是空)。

image-20210823225513550

InnoDB中,一条数据的旧版本,是存放在undo log的。因为修改了多次,这些undo log会形成一个链条,叫做undo log链。

上面说的DB_ROLL_PTR,它其实就是指向undolog链的指针。

InnoDB有一个数据结构,会把本事务ID、活跃事务ID、当前系统最大事务ID存起来,实现判断事务相关的判断。这个数据结构就叫Read View(可见性视图),每个事务都维护一个自己的Read View。

image-20210823230521884

mids:表示在生成Read View时当前系统中活跃的读写事务的事务id列表

min trx id:表示在生成Read View时当前系统中活跃的读写事务中最小的事务id,也就是m_ids中的最小值

max_trx_id:表示生成ReadView时系统中应该分配给下一个事务的id值

creator_trx_id:表示生成该ReadView的事务的事务id

有了这个数据结构以后,事务判断可见性的规则是这样的:

0、从数据的最早版本开始判断(undo log)

1,数据版本的trx_id = creator_trx_id 本事务修改,可以访问

2、数据版本的trx_id < min_trx_id(未提交事务的最小ID),说明这个版本在生成ReadView已经提交,可以访问

3、数据版本的trx_id > max_trx_id(下一个事务ID),这个版本是生成ReadView之后才开启的事务建立的,不能访问

4、数据版本的trx_id在min_trx_id和max_trx_id之间,看看是否在m_ids中,如果在,不可以。如果不在,可以

5、如果当前版本不可见,就找undo log链中的下一个版本

注意:RR(可重复读)中Read View是事务第一次查询的时候建立的,RC的Read View是事务每次查询的时候建立的。

在InnoDB中,MVCC和锁是协同使用的,这两种方案并不是互斥的。

MySQL InnoDB锁相关

锁的粒度

MyISAM只支持表锁,用lock table的语法加锁。

而InnoDB同时支持表锁和行锁。

表锁和行锁的区别

锁定粒度:表锁 > 行锁

加锁效率:表锁 > 行锁

冲突概率:表锁 > 行锁

并发性能:表锁 < 行锁

锁的类型

共享锁

第一个行级别的锁就是Shared Locks(共享锁),获取了一行数据的读锁以后,可以用来读取数据,所以它也叫做读锁,注意不要在加上了读锁以后去写数据,不然的话可能会出现死锁的情况,而且多个事务可以共享一把读锁。

共享锁的作用:因为共享锁会阻塞其他事务的修改,所以可以用在不允许其他事务修改数据的情况。

可以用 select ...lock in share mode; 的方式手工加上一把读锁。

释放锁有两种方式,只要事务结束,锁就会自动释放,包括提交事务和结束事务。

排它锁

第二个行级别的锁叫做Exclusive Locks(排它锁),它是用来操作数据的,所以又叫做写锁。只要一个事务获取了一行数据的排它锁,其他的事务就不能再获取这一行数据的共享锁和排它锁。

排它锁的加锁方式有两种,第一种是自动加排他锁,我们在操作数据的时候,包括增删改,都会默认加上一个排它锁。

还有一种是手工加锁,我们用一个FOR UPDATE给一行数据加上一个排它锁,这个无论是在我们的代码里面还是操作数据的工具里面,都比较常用。

意向锁

意向锁是表锁

当我们给一行数据加上共享锁之前,数据库会自动在这张表上面加一个意向共享锁。

当我们给一行数据加上排他锁之前,数据库会自动在这张表上面加一个意向排他锁。

反过来:

如果一张表上面至少有一个意向共享锁,说明有其他的事务给其中的某些数据行加上了共享锁。

如果一张表上面至少有一个意向排他锁,说明有其他的事务给其中的某些数据行加上了排他锁。

意向锁跟意向锁是不冲突的,意向锁跟行锁也不冲突。

那么这两个表级别的锁存在的意义是什么呢?

如果说没有意向锁的话,当准备给一张表加上表锁的时候,首先要做什么?是不是必须先要去判断有没其他的事务锁定了其中了某些行?如果有的话,肯定不能加上表锁。那么这个时候就要去扫描整张表才能确定能不能成功加上一个表锁,如果数据量特别大,比如有上千万的数据的时候,加表锁的效率是不是很低?

但是引入了意向锁之后就不一样了,只要判断这张表上面有没有意向锁,如果有,就直接返回失败,如果没有,就可以加锁成功。所以InnoDB里面的表锁,可以把它理解成一个标志,是用来提高加锁的效率的。

锁的作用是,是为了解决资源竞争的问题,数据库的资源就是数据表或者数据行,所以锁是用来解决事务对数据的并发访问问题的。

在InnoDB中,行锁到底锁住了什么

当一个事务锁住了一行数据的时候,其他的事务不能操作这一行数据,那它到底是锁住了这一行数据,还是锁住了这一个字段,还是锁住了别的什么东西呢?

InnoDB的行锁,就是通过锁住索引来实现的。

在没有索引或者没有用到索引的情况下,会锁住整张表。

锁的算法

示例

插入4行数据,主键id分别是1、4、7、10。

image-20210823233942576

Record 记录锁

第一种情况,当对于唯一性的索引(包括唯一索引和主键索引)使用等值查询,精准匹配到一条记录的时候,这个时候使用的就是记录锁。

image-20210823234158983

比如 whereid=1 4 7 10。使用不同的key去加锁,不会冲突,它只锁住这个record。

图片上红色的字就是使用这种锁的条件。

Grap 间隙锁

第二种情况,当我们查询的记录不存在,没有命中任何一个record,无论是用等值查询还是范围查询的时候,它使用的都是间隙锁。

举个例子,where id > 4 and id <7,where id=6

image-20210823234349010
注意,当查询的记录不存在的时候,使用间隙锁,间隙锁主要是阻塞插入insert。相同的间隙锁之间不冲突。

Next-key临键锁

第三种情况,当使用了范围查询,不仅仅命中了Record记录,还包含了Gap间隙,在这种情况下使用的就是临键锁,它是MySQL里面默认的行锁算法,相当于记录锁加上间隙锁。

唯一性索引,等值查询匹配到一条记录的时候,退化成记录锁。

没有匹配到任何记录的时候,退化成间隙锁。

image-20210823234548718

比如使用 > 5 , < 9,它包含了记录不存在的区间,也包含了一个Record7。

临键锁,锁住最后一个key的下一个左开右闭的区间。

1
2
select * from t2 where id >5 and id <=7 for update;  锁住(4,7]和(7,10]
select * from t2 where id > 8 andid <=l0 for update; 锁住(7,10],(10,+oo)

锁总结

RR隔离级别下,普通的select使用快照读(snapshotread),底层使用MVCC来实现。

加锁的select(select..in share mode / select..for update)以及更新操作update,delete等语句使用当前读(current read),底层使用记录锁、或者间隙锁、

RC隔离级别下,普通的select都是快照读,使用 MVCC实现。 加锁的select都使用记录锁,因为没有Gap Lock。

除了两种特殊情况一外键约束检查(foreign-key constraint checking)以及重复键检查(duplicate-key checking)时会使用间隙锁封锁区间,所以RC会出现幻读的问题。

RC和RR主要有几个区别:

1、RR的间隙锁会导致锁定范围的扩大。

2、条件列未使用到索引,RR锁表,RC锁行。

3、 RC的“半一致性”(semi-consistent)读可以增加update操作的并发性。

打赏

请我喝杯咖啡吧~

支付宝
微信