什么是事务 事务(Transaction)是访问和更新数据的执行单元。事务中包含有个或者多个sql语句,要么都执行,要么都不执行。
sql语句的执行顺序 fromwhere limit MySQL的逻辑架构 MySQL数据库主要分两个层级:服务层和存储引擎层
服务层:包含连接器,查询缓存,分析器,优化器,执行器。大多数核心功能和所有的跨存储引擎的功能也在这一层实现,包括存储过程,触发器,视图。 存储引擎层:包含了MySQL常见的存储引擎,包括MyISAM,InnoDB和Memory等,最常用的是InnoDB,也是MySQL默认的存储引擎。存储引擎在建表的时候也可以指定。 > create table rumenz(id int primary key auto_increment)engine=innodb;SQL语句的执行过程 连接器:从MySQL客户端登录,需要连接器来连接用户和MySQL数据库,mysql -uroot -p123456进行MySQL登录,在完成TCP握手之后,连接器会根据输入的用户名和密码验证身份,若错误会提示:Access denied for user,若账号密码正确,MySQL会根据权限表中的记录来判定权限。 查询缓存:MySQL在收到一个请求后,会先去缓存中查找,是否执行过这条SQL语句,之前执行过的SQL语句结果会以key-value的形式直接存储到缓存中,key是查询的语句,value是查询结果集,如果能通过SQL语句直接查询到缓存中的结果集,则直接返回。优点是效率高,但是不建议使用,原因是在MySQL中对表进行了更新操作,缓存就失效了,在频繁更新的系统中,缓存的命中率并不高。在MySQL8.0中查询缓存功能就被删除了,不存在查询缓存的功能了。
词法分析:MySQL会解析sql语句,分词器会先做词法分析,SQL语句一般由字符串和空格组成,MySQL要识别出字符串代表什么。 语法分析:根据词法分析的结果,按照语法规则看看SQL语句是否正确。如果SQL语句语法不正确,就提示:You have an error in your SQL syntax 优化器:SQL语句语法正确后,优化器会判断你使用了哪种索引,哪种链接,优化器的作用就是确定效率最高的执行方案。 执行器:在执行阶段,MySQL会判断有没有执行语句的权限,若没有权限,会返回没有权限的错误,若有权限,则打开表继续执行。打开表时,执行器会根据你使用的存储引擎,去使用该引擎对接的接口。 MySQL执行流程 提交和回滚 MySQL事务是如下操作的
begin; -- 或者start transactionbegin或start transaction都是手动开启一个事务。commit是手动提交一个事务,将执行结果写入数据库,如果这个过程中出现错误会调用rollback,回滚所有已经执行成功的sql。当然也可以在事务中直接使用rollback语句进行回滚。
自动提交 MySQL默认采用的是自动提交
> show variables like '%autocommit%' ;在自动提交的模式下,如果没有显示的start transaction
begin开启一个事务,那么每个sql语句都会被当做一个事务执行提交操作。通过如下方式,可以关闭autocommit,需要注意的是,autocommit是针对连接的,在一个连接中修改了此参数,不会影响其他的连接。
> set autocommit=0;'%autocommit%' ;in set (0.00 sec)如果关闭了autocommit,所有的sql语句都在一个事务中,直到执行了commit或rollback,该事务结束,并且开启了下一个事务。
特殊操作 在MySQL中,存在一些特殊的命令,如果在事务中执行了这些命令,会马上强制执行commit提交事务;如DDL语句(create table/drop table/alter table)、lock tables语句等等。
DML语句(insert,delete,update,select)等都不会强制提交事务。
ACID特性 ACID是衡量事务的四个特性
按照严格标准,只有同时满足ACID
ACID的少之又少,例如MySQL的NDB Cluster事务不满足持久性和隔离性；InnoDB默认事务隔离级别是可重复读，不满足隔离性；Oracle默认的事务隔离级别为READ COMMITTED，不满足隔离性……因此与其说ACID是事务必须满足的条件，不如说它们是衡量事务的四个维度。
原子性 原子性是指一个事务中一个不可分割的工作单位,其中的操作要么都做,要么都不做,如果事务中的一个sql语句执行失败,则已执行的sql语句也必须都要回滚,数据库回到事务前的状态。
实现原理undo log MySQL的日志分很多种,如二进制日志,错误日志,慢查询日志,查询日志,另外InnoDB存储引擎还有两种事务日志,redo log(重做日志),undo log(回滚日志),其中redo log用于保证事务持久性,undo log则是事务原子性和隔离性的基础。
undo log 是原子性的关键,当事务回滚时,能撤销所有已经成功执行的sql语句。InnoDB能实现回滚就是靠undo log:当事务对数据库进行修改时,InnoDB会生成对应的undo log;如果事务执行失败或者进行了回滚(rollback),导致事务回滚,便可以利用undo log中的信息将数据恢复到回滚前的样子。
undo log属于逻辑日志,它记录的是sql执行相关的信息。当发生回滚时,InnoDB会根据undo log做相反的事情,对于每个insert,回滚做delete;对于每个delete,回滚做insert;对于update,回滚会执行一个相反的update,把数据改回去。
以update为例:当事务执行update时,其中生成的undo log会包含被修改数据的主键(以便知道修改了哪些行),修改了哪些列,这些列在修改前后的值信息,回滚时便可以利用这些信息将数据恢复到update之前的状态。
redo log redo log和undo log都是InnoDB的事务日志. InnoDB作为MySQL的存储引擎,数据是永久存放在磁盘中的,但是每次读写数据都要磁盘IO,效率会很低。为此,InnoDB提供了缓存(Buffer Pool),Buffer Pool中包含了磁盘中部分数据的映射,作为数据库访问的缓冲,当需要从数据库读取数据时,会先从Buffer Pool中读取,如果Buffer Pool中没有,则会从磁盘中读上来并放到Buffer Pool,当向数据库写入数据时,会首先写入Buffer Pool,Buffer Pool中修改的数据会被定期刷新到磁盘中去(这一过程被称为脏读) Buffer Pool极大的提高了数据的读写效率,但是也带来了新的问题,如果MySQL宕机,而此时Buffer Pool中被修改的数据还没有被刷新到磁盘,就会导致数据丢失,就无法保证数据的持久性。
为了解决上面的问题,redo log就出现了,当数据被修改时,除了修改Buffer Pool中的数据,还会在redo log日志中记录这次操作,当事务提交时,会调用fsync接口对redo log进行刷盘。如果MySQL宕机,重启时可以读取redo log对数据库数据进行恢复。redo log采用的是预写入模式(WAL),所以修改先写入redo log,然后更新到Buffer Pool,保证数据不会因为MySQL宕机而丢失,从而满足持久性的要求。
redo log 也需要在事务提交的时候将日志写入磁盘,为什么比直接将Buffer Pool中修改的数据写入磁盘(刷脏)要快？
刷脏是随机IO,每次更新数据位置是随机的,但写redo log是追加操作,属于顺序IO。 刷脏是以数据页为单位的,MySQL默认页的大小是16KB,一个Page上修改很少的数据也要整页写入,而redo log中只包含真正需要写入的数据,无效IO大大减小。 SQL语句的日志记录 日志记录用到的WAL技术,全称Write-Ahead-loggingredo log
redo log日志文件是固定大小的,比如可以配置为一组4个文件，每个文件大小是1GB，那么这块日志就可以记录4GB的内容，可以理解为一个环形结构，有一个write pos标识当前记录的位置，一边写入一边后移，有一个check point记录当前要擦除的位置（当然擦除之前要写入数据文件中），也是往后推移，并且循环的。当write pos追上 check point的时候表示日志已经满了， 当前需要停下来先擦除一些记录，存到数据文件中，为需要写入的日志腾出空间。
有了redo log,InnoDB就能保证数据库发生异常重启,之前提交的记录也不会丢失,这个能力叫做Crash-safe
redo log和binlog的区别 在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据恢复,但是二者有根本的不同.
作用不同,redo log是用于crash recovery,保证MySQL宕机也不会影响持久性;binlog是用于point-in-time recovery,保证服务器可以基于时间点恢复数据,此外binlog还用于主从复制. 层次不同,redo log是InnoDB存储引擎实现的,而binlog是MySQL的服务器层实现的,同时支持InnoDB和其它存储引擎. 内容不同,redo log是物理日志,内容基于磁盘的Page,binlog是二进制的,根据binlog_format参数的不同,可以是基于sql语句,基于数据本身或者二者结合. 写入时机不同,binlog日志提交时写入,redo log写入时机相对比较多. 当事务提交时会调用fsync对redo log进行刷盘,这是默认情况下的策略,修改innodb_flush_log_at_trx_commit参数可以修改此策略,但事物的持久性无法得到保证 除了事务提交还有其它的刷盘时机,如master thread 每秒刷盘一次redo log,好处是不一定要等到commit才刷盘,commit速度会大大加快.
隔离性 原子性和持久性研究的是一个事务本身,而隔离性研究的是不同事务之间的影响。隔离性是指事务内部的操作和其它事务是隔离的,并发执行的各个事务不能相互影响,严格的隔离性,对应的事务隔离级别为Serializable (可串行化),但是实际应用中出于性能方面的考虑很少会使用可串行化。
隔离性追求的是并发情况下事务之间不相互影响,我们这里只考虑读操作和写操作。
一个事务写对另一个事务写操作的影响,锁机制保证隔离性 一个事务写对另外一个事务读操作的影响,MVCC保证隔离性 锁机制
锁机制的基本原理:事务在修改数据之前,首先要获得相应的锁;或者锁之后,事务可以修改数据;该事务操作期间,这部分数据是被锁定的,其它事务如果想修改数据,只有等当前事务提交或者回滚后释放锁。
行锁和表锁 按照锁的颗粒度可以划分为:行锁和表锁。表锁在操作数据时会锁定整张表,并发性较差,行锁只锁定需要操作的数据,并发性好。但是加锁本身要消耗资源(获得锁,检查锁,释放锁都要消耗系统资源),因此在锁定数据较多的情况下可以适用表锁可以节省大量的资源。MySQL中不同存储引擎支持的锁是不一样的,例如:MyISAM只支持表锁,而InnoDB即支持表锁也支持行锁,处于性能考虑,绝大多数情况下使用的都是行锁。
查看InnoDB中锁的情况
> select * from information_schema.innodb_locks; #锁的概况 #InnoDB整体状态，其中包括锁的情况 session1 session2 session3 set autocommit=0; set autocommit=0; select * from information_schema.innodb_locks;
session3查询结果
image-20210712151333584 lock_type为RECORD，代表锁为行锁(记录锁) 脏读,不可重复读,幻读 脏读 当前事务可以读到其它事务未提交的数据(脏数据),这种现象是脏读。
时间 事务A 事务B T1 开始事务 开始事务 T2 select sex from user where id=1 T3 update user set sex=15 where id=1; T4 select sex from user where id=1 T5 提交事务
不可重复读:在同一个事务中两次读取同一个数据不一样,这种现象被称为不可重复读。脏读可不可重复读的区别在于,脏读读到的是其它事务未提价的数据,而不可重复读读到的是其它事务已经提价的数据
时间 事务A 事务B T1 开始事务 开始事务 T2 select sex from user where id=1 T3 update user set sex=15 where id=1; T4 提交事务 T5 select sex from user where id=1
幻读 在一个事务中两次查询查到的数据条数不同,这种现象被称为幻读。不可重复读和幻读的区别在于,不可重复度是数据变了,幻读是数据行数变了。
时间 事务A 事务B T1 开始事务
开始事务 T2 select sex from user where id> 1 and id <5 T3 insert into user(id,sex)values(2,18); T4 提交事务 T5 select sex from user where id> 1 and id <5
事务隔离级别 SQL标准中定义了4种隔离级别,并规定每种隔离级别的特点。一般来说隔离级别越低,系统开销就越低,可支持并发越高,但隔离性也越差。
隔离级别 脏读 不可重复读 幻读 Read Uncommitted读未提交 可能 可能 可能 Read committed读已提交 不可能 可能 可能 Repeatable Read可重复读 不可能 不可能 可能 Serializable可串行化 不可能 不可能 不可能
在实际使用过程中,读未提交并发会导致很多的问题,而且性能相对其他隔离级别提高有限,因此很少使用。可串行化,并发效率很低,只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。
在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）,InnoDB默认的隔离级别是RR。
查看MySQL当前的提交级别 mysql> show variables like '%isolation%' ;in set (0.00 sec)MVCC RR解决脏读, 不可重复读,幻读等问题,使用的是MVCC,MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。
时间 事务A 事务B 事务C
T1 开始事务 开始事务 开始事务 T2 查询rumenz的余额为100 T3 修改rumenz的余额是200 T4 提交事务 T5 查询rumenz的余额是100 查询rumenz的余额是200
MVCC最大的优点就是读不加锁,因此读写不冲突,并发性好。InnoDB实现MVCC,多个版本的数据就可以共存。用到的技术和数据结构。
隐藏列:InnoDB每行都有隐藏列,隐藏列中包含了本行数据的事务ID, 指向undo log的指针等。 基于undo log的版本链,每条数据都会包含指向undo log的指针,而每条undo log也会指向更早版本的undo log,从而形成一个版本链。 ReadView通过隐藏列和版本链,MySQL可以将数据恢复到指定版本;但是具体要恢复到哪个版本,则需要根据ReadView来确定。所谓ReadView,是指事务(事务A)在某一时刻给整个事务系统(trx_sys)打快照,之后进行读操作的时候,会将读到数据中的事务ID与(trx_sys)快照比较,从而判断数据对该ReadView可见,即事务A是否可见。 trx_sys主要内容low_limit_id表示生成ReadView系统应该分给下一个事务的ID。如果事务的ID大于等于low_limit_id,则对该ReadVIew不可见。up_limit_id表示生成ReadView时当前系统中活跃的读写事务中最小的事务ID,如果数据的事务ID小于up_limit_id,则对该ReadView可见。rw_trx_ids表示生成ReadView时当前系统中活跃读写事务的事务ID列表,如果数据的事务ID在low_limit_id和up_limit_id之间,则需要判断事务ID在不在rw_trx_ids中,如果在则说明生成ReadView时事务仍然在活跃中,因此数据对ReadView不可见,如果不在说明生成ReadView时,事务已经提交了。脏读 时间
事务A 事务B T1 开始事务 开始事务 T2 修改rumenz的余额由100到200 T3 查询rumenz的余额为100元 T4 提交事务
当事务A在T3时刻读取rumenz余额时,会生成ReadView由于此时事务B没有提交仍然活跃,因此其事务ID一定在ReadView的rw_trx_ids中,根据上面的规则,事务B的修改对ReadView不可见。接下来,事务A根据指针指向undo log查询上一版本的数据,得到rumenz的余额为100,这样事务A就避免了脏读。
不可重复读 时间 事务A 事务B
T1 开始事务 开始事务 T2 查询rumenz的余额为100元 T3 修改rumenz的余额由100到200 T4 提交事务 T5 查询rumenz的余额为100元
当事务A在T2时刻读取rumenz的余额前会生成一个ReadView,此时事务B分两种情况讨论，一种是如图中所示，事务已经开始已经提交，此时其事务id在ReadView的rw_trx_ids中；一种是事务B还没有开始，此时其事务id大于等于ReadView的low_limit_id。无论是哪种情况，根据前面介绍的规则，事务B的修改对ReadView都不可见。
当事务A在T5时刻再次读取rumenz的余额时，会根据T2时刻生成的ReadView对数据的可见性进行判断，从而判断出事务B的修改不可见；因此事务A根据指针指向的undo log查询上一版本的数据，得到rumenz的余额为100，从而避免了不可重复读。
脏读 时间 事务A 事务B
T1 开始事务 开始事务 T2 查询0的所有用户rumenz,balance=100 T3 插入id=2,name=lisi,balance=200 T4 提交事务 T5 查询0的所有用户rumenz,balance=100
MVCC避免幻读的机制与避免不可重复读非常类似。
当事务A在T2时刻读取0
当事务A在T5时刻再次读取0
RC 与 RR的区别 RC与RR一样,都使用了MVCC
RR在事务开始的时候后第一次执行select前就创建ReadView,直到事物提交都不会再创建。RR可以解决脏度,不可重复读,幻读 RC每次执行select都会创建一个ReadView,因此如果事物A第一次select之后,事务B对数据进行了修改并提交,那么事务A第二次select时会重新建立ReadView,因此事务B的修改对事务A可见的。因此RC隔离级别可以避免脏读,但是无法避免不可重复读和幻读。 加锁读和非加锁读
按照是否加锁，MySQL的读可以分为两种,一种是非加锁读，也称作快照读、一致性读，使用普通的select语句，这种情况下使用MVCC避免了脏读、不可重复读、幻读，保证了隔离性。
#共享锁读取 in share mode#排它锁读取 加锁读在查询时会对查询的数据加锁（共享锁或排它锁）。由于锁的特性，当某事务对数据进行加锁读后，其他事务无法对数据进行写操作，因此可以避免脏读和不可重复读。而避免幻读，则需要通过next-key lock。next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。因此，加锁读同样可以避免脏读、不可重复读和幻读，保证隔离性。
总结 InnoDB实现的RR,通过锁机制(包含netxt-key lock),MVCC(包含隐藏数据列,基于undo log的版本链,ReadView)等,实现了一定程度的隔离性,可以满足大多数场景的需要。
RR虽然避免了幻读问题，但是毕竟不是Serializable，不能保证完全的隔离
如果在事务中第一次读取采用非加锁读，第二次读取采用加锁读，则如果在两次读取之间数据发生了变化，两次读取到的结果不一样，因为加锁读时不会采用MVCC。
时间 事务A 事务B T1 begin; begin T2 select * from user where id>0 and id <5; T3 insert into user(id,balance) values(2,200); T4 commit; T5 update user set balance=300 where id>0 and id<5; T6 commit;
出现了幻读问题
RR模式下 日志中记录的是：先commit的记录在前面，后commit的记录在后面，与执行时间点无关；就单个 session 来说，好理解，执行顺序就是记录顺序；多个 session 之间的话，先 commit 的先记录主库对数据库的更改是按执行时间的先后顺序进行的，而 binlog 却是按 commit 的先后顺序记录的。
