什么是事务
事务(Transaction)是访问和更新数据的执行单元。事务中包含有个或者多个sql语句,要么都执行,要么都不执行。
sql语句的执行顺序
代码语言：javascript复制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执行流程
提交和回滚
代码语言：javascript复制MySQL事务是如下操作的
begin; -- 或者start transaction
-- 1条或者n条sql语句
commit;
begin或start transaction都是手动开启一个事务。commit是手动提交一个事务,将执行结果写入数据库,如果这个过程中出现错误会调用rollback,回滚所有已经执行成功的sql。当然也可以在事务中直接使用rollback语句进行回滚。
自动提交
代码语言：javascript复制MySQL默认采用的是自动提交
> show variables like '%autocommit%';
--------------- -------
| Variable_name | Value |
--------------- -------
| autocommit | ON |
--------------- -------
代码语言：javascript复制在自动提交的模式下,如果没有显示的
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-logging
redo 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通过锁机制来保证这一点。
锁机制的基本原理:事务在修改数据之前,首先要获得相应的锁;或者锁之后,事务可以修改数据;该事务操作期间,这部分数据是被锁定的,其它事务如果想修改数据,只有等当前事务提交或者回滚后释放锁。
行锁和表锁
代码语言：javascript复制按照锁的颗粒度可以划分为:行锁和表锁。表锁在操作数据时会锁定整张表,并发性较差,行锁只锁定需要操作的数据,并发性好。但是加锁本身要消耗资源(获得锁,检查锁,释放锁都要消耗系统资源),因此在锁定数据较多的情况下可以适用表锁可以节省大量的资源。MySQL中不同存储引擎支持的锁是不一样的,例如:MyISAM只支持表锁,而InnoDB即支持表锁也支持行锁,处于性能考虑,绝大多数情况下使用的都是行锁。
查看InnoDB中锁的情况
> select * from information_schema.innodb_locks; #锁的概况
> show engine innodb status; #InnoDB整体状态，其中包括锁的情况
session1 | session2 | session3 |
|---|---|---|
set autocommit=0;begin;update user set sex=12 where id=1; | ||
set autocommit=0;begin;update user set sex=15 where id=1; | ||
select * from information_schema.innodb_locks; |
session3查询结果
image-20210712151333584
- lock_type为RECORD，代表锁为行锁(记录锁)
- lock_mode为X，代表排它锁(写锁)
脏读,不可重复读,幻读
脏读
当前事务可以读到其它事务未提交的数据(脏数据),这种现象是脏读。
时间 | 事务A | 事务B |
|---|---|---|
T1 | 开始事务 | 开始事务 |
T2 | select sex from user where id=112 | |
T3 | update user set sex=15 where id=1; | |
T4 | select sex from user where id=115(脏数据) | |
T5 | 提交事务 |
不可重复读:在同一个事务中两次读取同一个数据不一样,这种现象被称为不可重复读。脏读可不可重复读的区别在于,脏读读到的是其它事务未提价的数据,而不可重复读读到的是其它事务已经提价的数据
时间 | 事务A | 事务B |
|---|---|---|
T1 | 开始事务 | 开始事务 |
T2 | select sex from user where id=112 | |
T3 | update user set sex=15 where id=1; | |
T4 | 提交事务 | |
T5 | select sex from user where id=115(不可重复读) |
幻读
在一个事务中两次查询查到的数据条数不同,这种现象被称为幻读。不可重复读和幻读的区别在于,不可重复度是数据变了,幻读是数据行数变了。
时间 | 事务A | 事务B |
|---|---|---|
T1 | 开始事务 | 开始事务 |
T2 | select sex from user where id> 1 and id <5 id=1,sex=12 | |
T3 | insert into user(id,sex)values(2,18); | |
T4 | 提交事务 | |
T5 | select sex from user where id> 1 and id <5 id=1,sex=12id=2,sex=18幻读 |
事务隔离级别
SQL标准中定义了4种隔离级别,并规定每种隔离级别的特点。一般来说隔离级别越低,系统开销就越低,可支持并发越高,但隔离性也越差。
隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
Read Uncommitted读未提交 | 可能 | 可能 | 可能 |
Read committed读已提交 | 不可能 | 可能 | 可能 |
Repeatable Read可重复读 | 不可能 | 不可能 | 可能 |
Serializable可串行化 | 不可能 | 不可能 | 不可能 |
在实际使用过程中,读未提交并发会导致很多的问题,而且性能相对其他隔离级别提高有限,因此很少使用。可串行化,并发效率很低,只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。
在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）,InnoDB默认的隔离级别是RR。
查看MySQL当前的提交级别
代码语言：javascript复制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可以读取到不同版本的数据。
时间 | 事务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<id<5的所有用户id=1,name=rumenz,balance=100 | |
T3 | 插入id=2,name=lisi,balance=200 | |
T4 | 提交事务 | |
T5 | 查询0<id<5的所有用户id=1,name=rumenz,balance=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隔离级别可以避免脏读,但是无法避免不可重复读和幻读。
加锁读和非加锁读
代码语言：javascript复制按照是否加锁，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。
时间 | 事务A | 事务B |
|---|---|---|
T1 | begin; | begin |
T2 | select * from user where id>0 and id <5;id=1,balance=100 | |
T3 | insert into user(id,balance) values(2,200);id=2,blance=200 | |
T4 | commit; | |
T5 | update user set balance=300 where id>0 and id<5;id=1,balance=300id=2,balance=300 | |
T6 | commit; |
出现了幻读问题
RR模式下 日志中记录的是：先commit的记录在前面，后commit的记录在后面，与执行时间点无关；就单个 session 来说，好理解，执行顺序就是记录顺序；多个 session 之间的话，先 commit 的先记录主库对数据库的更改是按执行时间的先后顺序进行的，而 binlog 却是按 commit 的先后顺序记录的。
相关文章
Mysql中的索引
Mysql通过binlog恢复数据
Mysql之binlog三种模式
Mysql中的binlog入门介绍
