MySql事务原理

最新推荐文章于 2024-06-07 10:00:00 发布

晓哥哥zZ

最新推荐文章于 2024-06-07 10:00:00 发布

阅读量214

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45912029/article/details/103193585

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySql存储引擎

先简单说一下MySql的存储引擎~

ISAM
不支持事务，硬盘崩溃时数据无法恢复，不支持外键

MyISAM

ISAM的扩展，不支持事务、外键，但查询速度快，支持全文索引，表级锁

InnoDB

MySql5.5版本后默认引擎(唯一支持事务的存储引擎)
1.支持事务、外键
2.数据多版本读取
3.不支持全文索引
4.行级锁

Memory

全表锁，存储在内存中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，但不适用于精确查找，主要用于那些内容变化不频繁的代码表

查看当前数据库引擎

show engines; #查看所有
show variables like '%storage_engine%';
show variables like 'default_storage_engine'; #查看当前默认使用

查看/修改表的存储引擎

show table status from 数据库 where name='表'
alter table 表 engine='INNODB'

一张表，ID自增，先insert 17条记录，再del15，16，17条记录，重启MySql，问新插入ID是多少？
若存储引擎是InnoDB，重启后ID是15，不重启则是18.因为最大ID保存在内存中。
若存储引擎是MyISAM，则ID是18，不论重启与否，因为最大ID保存在数据文件中。
InnoDB和MyISAM执行select count(*)哪个更快？
MyISAM更快，因为内部维护了一个计数器，可以直接调用。

MySQL事务

在MySQL中只有使用了 Innodb 数据库引擎的数据库或表才支持事务，事务可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

实现

1、用start transaction，rollback，commit来实现：

mysql> start transaction;  # 开始事务
Query OK, 0 rows affected (0.00 sec)

mysql> insert into 表 value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)

2、用set来改变MySql的自动提交模式(默认开启自动提交)：

show variables like 'autocommit';
set autocommit=0; #禁止自动提交
set autocommit=1; #开启自动提交

事务是必须满足4个条件（ACID）：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

原子性

定义

一个transaction中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被Rollback到事务开始前的状态，就像这个事务从来没有执行过一样。

原理 undo log

在说明原子性原理之前，首先介绍一下MySQL的事务日志。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的事务日志。下面先聊一下redo log存在的背景。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（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：
(1)刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
(2)刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

redo log与binlog

在MySQL中还存在binlog(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：
（1）作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
（2）层次不同：redo log是InnoDB存储引擎实现的，而binlog是MySQL的服务器层(可以参考文章前面对MySQL逻辑架构的介绍)实现的，同时支持InnoDB和其他存储引擎。（3）内容不同：redo log是物理日志，内容基于磁盘的Page；binlog的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
（4）写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

隔离性

定义

事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

查看/设置全局/会话的隔离级别

select @@global.tx_isolation; #全局
select @@tx_isolation; #会话
set global transaction isolation level READ UNCOMMITTED; #全局
set transaction isolation level READ UNCOMMITTED; #会话
# 四种隔离级别：
# READ UNCOMMITTED
# READ COMMITTED
# REPEATABLE READ
# SERIALIZABLE

隔离性追求的是并发情形下事务之间互不干扰。简单起见，我们仅考虑最简单的读操作和写操作(暂时不考虑带锁读等特殊操作)，那么隔离性的探讨，主要可以分为两个方面：

1.一个事务写操作对另一个事务写操作的影响：锁机制保证隔离性
2.一个事务写操作对另一个事务读操作的影响：MVCC保证隔离性

写对写：锁机制

首先来看两个事务的写操作之间的相互影响。隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁与表锁

按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

查看锁信息

select * from information_schema.innodb_locks; #锁的概况

下面来看一个例子：

#事务A：
start transaction;
update account SET balance = 1000 where id = 1;

#事务B：
start transaction;
update account SET balance = 2000 where id = 1;

此时查看锁的情况：
在这里插入图片描述
通过上述命令可以查看事务24052和24053占用锁的情况：其中
lock_type为RECORD，代表锁为行锁(记录锁)；
lock_mode为X，代表排它锁(写锁)。

介绍完写操作之间的相互影响，下面讨论写操作对读操作的影响，首先来看并发情况下，读操作可能存在的三类问题：

脏读

事务A中可以读到事务B未提交的数据（脏数据），这种现象是脏读。举例：
在这里插入图片描述

不可重复读

在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：脏读读到的是其他事务未提交的数据，不可重复读读到的是其他事务已提交的数据。举例：
在这里插入图片描述

幻读

在事务A中先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：不可重复读是数据变了，幻读是数据的行数变了。举例：
在这里插入图片描述

事务隔离级别

一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差
在这里插入图片描述
在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读。

InnoDB默认的隔离级别是可重复读，可重复读是无法避免幻读问题的，但是InnoDB实现的可重复读避免了幻读问题。

写对读：MVCC

可重复读解决脏读、不可重复读、幻读等问题，使用的是MVCC：MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。
在这里插入图片描述
MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要是依靠数据的隐藏列(也可以称之为标记位)和undo log。其中数据的隐藏列包括了该行数据的版本号、删除时间、指向undo log的指针等等；当读取数据时，MySQL可以通过隐藏列判断是否需要回滚并找到回滚需要的undo log，从而实现MVCC；隐藏列的详细格式不再展开。

下面结合前文提到的几个问题分别说明：

（1）脏读
在这里插入图片描述
当事务A在T3时间节点读取zhangsan的余额时，会发现数据已被其他事务修改，且状态为未提交。此时事务A读取最新数据后，根据数据的undo log执行回滚操作，得到事务B修改前的数据，从而避免了脏读。

（2）不可重复读
在这里插入图片描述
当事务A在T2节点第一次读取数据时，会记录该数据的版本号（数据的版本号是以row为单位记录的），假设版本号为1；当事务B提交时，该行记录的版本号增加，假设版本号为2；当事务A在T5再一次读取数据时，发现数据的版本号2大于第一次读取时记录的版本号1，因此会根据undo log执行回滚操作，得到版本号为1时的数据，从而实现了可重复读。

（3）幻读
InnoDB实现的可重复读通过next-key lock机制避免了幻读现象。
next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。当然，这里我们讨论的是不加锁读：此时的next-key lock并不是真的加锁，只是为读取的数据增加了标记（标记内容包括数据的版本号等）；准确起见姑且称之为类next-key lock机制。还是以前面的例子来说明：
在这里插入图片描述
当事务A在T2节点第一次读取0<id<5数据时，标记的不只是id=1的数据，而是将范围(0,5)进行了标记，这样当T5时刻再次读取0<id<5数据时，便可以发现id=2的数据比之前标记的版本号更高，此时再结合undo log执行回滚操作，避免了幻读。

概括来说，InnoDB实现的可重复读，通过锁机制、数据的隐藏列、undo log和类next-key lock，实现了一定程度的隔离性，可以满足大多数场景的需要。不过需要说明的是，可重复读虽然避免了幻读问题，但是毕竟不是Serializable，不能保证完全的隔离，下面是一个例子，大家可以自己验证一下。
在这里插入图片描述

一致性

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）。

可以说，一致性是事务追求的最终目标：原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

参考：
《MySQL技术内幕：InnoDB存储引擎》
《高性能MySQL》
《MySQL运维内参》
博客 https://www.cnblogs.com/kismetv/p/10331633.html

晓哥哥zZ

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySql事务原理

ISAM不支持事务，硬盘崩溃时数据无法恢复，不支持外键MyISAMISAM的扩展，不支持事务、外键，但查询速度快，表级锁InnoDBMySql5.5版本后默认引擎支持事务、外键数据多版本读取支持行级锁其它存储引擎Memery/Merge/CSV…查看数据引擎show engines;查看当前默认数据引擎show variables like '%storage_e...
复制链接

扫一扫