什么是事务

事务(Transaction)是访问和更新数据的执行单元。事务中包含有个或者多个sql语句,要么都执行,要么都不执行。

sql语句的执行顺序

from
join
on
where
group by(开始使用select中的别名,后面的语句都可以使用)
avg,sum
having
select
distinct
order by
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事务是如下操作的

begin; -- 或者start transaction
-- 1条或者n条sql语句
commit;

begin或start transaction都是手动开启一个事务。commit是手动提交一个事务,将执行结果写入数据库,如果这个过程中出现错误会调用rollback,回滚所有已经执行成功的sql。当然也可以在事务中直接使用rollback语句进行回滚。

自动提交

MySQL默认采用的是自动提交

> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

在自动提交的模式下,如果没有显示的start transaction或begin开启一个事务,那么每个sql语句都会被当做一个事务执行提交操作。通过如下方式,可以关闭autocommit,需要注意的是,autocommit是针对连接的,在一个连接中修改了此参数,不会影响其他的连接。

> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row 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是衡量事务的四个特性

• 原子性(Atomicity,或称为不可分割性)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

按照严格标准,只有同时满足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是InnoDB引擎中的日志模块,只有InnoDB有,它是物理日志,记录这个数据页做了什么改动。

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保证隔离性

锁机制

两个事务写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据库进行写操作。InnoDB通过锁机制来保证这一点。

锁机制的基本原理:事务在修改数据之前,首先要获得相应的锁;或者锁之后,事务可以修改数据;该事务操作期间,这部分数据是被锁定的,其它事务如果想修改数据,只有等当前事务提交或者回滚后释放锁。

行锁和表锁

按照锁的颗粒度可以划分为:行锁和表锁。表锁在操作数据时会锁定整张表,并发性较差,行锁只锁定需要操作的数据,并发性好。但是加锁本身要消耗资源(获得锁,检查锁,释放锁都要消耗系统资源),因此在锁定数据较多的情况下可以适用表锁可以节省大量的资源。MySQL中不同存储引擎支持的锁是不一样的,例如:MyISAM只支持表锁,而InnoDB即支持表锁也支持行锁,处于性能考虑,绝大多数情况下使用的都是行锁。

查看InnoDB中锁的情况

> select * from information_schema.innodb_locks; #锁的概况
> show engine innodb status; #InnoDB整体状态，其中包括锁的情况

session3查询结果

• lock_type为RECORD，代表锁为行锁(记录锁)
• lock_mode为X，代表排它锁(写锁)

脏读,不可重复读,幻读

脏读

当前事务可以读到其它事务未提交的数据(脏数据),这种现象是脏读。

不可重复读:在同一个事务中两次读取同一个数据不一样,这种现象被称为不可重复读。脏读可不可重复读的区别在于,脏读读到的是其它事务未提价的数据,而不可重复读读到的是其它事务已经提价的数据

幻读

在一个事务中两次查询查到的数据条数不同,这种现象被称为幻读。不可重复读和幻读的区别在于,不可重复度是数据变了,幻读是数据行数变了。

事务隔离级别

SQL标准中定义了4种隔离级别,并规定每种隔离级别的特点。一般来说隔离级别越低,系统开销就越低,可支持并发越高,但隔离性也越差。

在实际使用过程中,读未提交并发会导致很多的问题,而且性能相对其他隔离级别提高有限,因此很少使用。可串行化,并发效率很低,只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。

在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）,InnoDB默认的隔离级别是RR。

查看MySQL当前的提交级别

mysql> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MVCC

RR解决脏读, 不可重复读,幻读等问题,使用的是MVCC,MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。

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在T3时刻读取rumenz余额时,会生成ReadView由于此时事务B没有提交仍然活跃,因此其事务ID一定在ReadView的rw_trx_ids中,根据上面的规则,事务B的修改对ReadView不可见。接下来,事务A根据指针指向undo log查询上一版本的数据,得到rumenz的余额为100,这样事务A就避免了脏读。

不可重复读

当事务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，从而避免了不可重复读。

脏读

MVCC避免幻读的机制与避免不可重复读非常类似。

当事务A在T2时刻读取0<id<5的用户余额前，会生成ReadView。此时事务B分两种情况讨论，一种是如图中所示，事务已经开始但没有提交，此时其事务id在ReadView的rw_trx_ids中；一种是事务B还没有开始，此时其事务id大于等于ReadView的low_limit_id。无论是哪种情况，根据前面介绍的规则，事务B的修改对ReadView都不可见

当事务A在T5时刻再次读取0<id<5的用户余额时，会根据T2时刻生成的ReadView对数据的可见性进行判断，从而判断出事务B的修改不可见。因此对于新插入的数据lisi(id=2)，事务A根据其指针指向的undo log查询上一版本的数据，发现该数据并不存在，从而避免了幻读。

RC 与 RR的区别

RC与RR一样,都使用了MVCC

• RR在事务开始的时候后第一次执行select前就创建ReadView,直到事物提交都不会再创建。RR可以解决脏度,不可重复读,幻读
• RC每次执行select都会创建一个ReadView,因此如果事物A第一次select之后,事务B对数据进行了修改并提交,那么事务A第二次select时会重新建立ReadView,因此事务B的修改对事务A可见的。因此RC隔离级别可以避免脏读,但是无法避免不可重复读和幻读。

加锁读和非加锁读

按照是否加锁，MySQL的读可以分为两种,一种是非加锁读，也称作快照读、一致性读，使用普通的select语句，这种情况下使用MVCC避免了脏读、不可重复读、幻读，保证了隔离性。

#共享锁读取
select...lock in share mode
#排它锁读取
select...for update

加锁读在查询时会对查询的数据加锁（共享锁或排它锁）。由于锁的特性，当某事务对数据进行加锁读后，其他事务无法对数据进行写操作，因此可以避免脏读和不可重复读。而避免幻读，则需要通过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。

出现了幻读问题

RR模式下 日志中记录的是：先commit的记录在前面，后commit的记录在后面，与执行时间点无关；就单个 session 来说，好理解，执行顺序就是记录顺序；多个 session 之间的话，先 commit 的先记录主库对数据库的更改是按执行时间的先后顺序进行的，而 binlog 却是按 commit 的先后顺序记录的。