【数据库】 MySQL事务

小胖鲸~

已于 2023-02-16 17:53:35 修改

阅读量1.6k

点赞数

分类专栏：数据库文章标签：数据库 java 开发语言

于 2023-02-16 17:30:13 首次发布

本文链接：https://blog.csdn.net/weixin_48814356/article/details/129064560

版权

数据库专栏收录该内容

20 篇文章 1 订阅

订阅专栏

文章详细介绍了MySQL中的事务概念，包括自动提交、隐式提交和显式提交的案例，以及事务的四大特性——原子性、持久性、隔离性和一致性。原子性确保事务操作要么全部完成，要么全部回滚；持久性通过redolog保证数据在提交后即使MySQL宕机也不会丢失；隔离性通过锁机制和MVCC防止并发事务间的干扰；一致性则关注事务前后数据库的一致状态。文章还讨论了InnoDB如何通过ACID原则来保证事务处理的可靠性。

摘要由CSDN通过智能技术生成

四， InnoDB 事务的ACID如何保证

MySQL事务

一，什么是事务

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。一个完整的业务需要批量的DML(insert、update、delete)语句共同联合完成。事务只和DML语句有关，或者说DML语句才有事务。这个和业务逻辑有关，业务逻辑不同，DML语句的个数不同。

以银行转账为例：账户转账是一个完整的业务，最小的单元，不可再分——也就是说银行账户转账是一个事务：

updatet_act setbalance=balance - 400whereactno=1;

updatet_act setbalance=balance + 400whereactno=2;

以上两条DML语句必须同时成功或者同时失败，最小单元不可再分，当第一条DML语句执行成功后，并不能将底层数据库中的第一个账户的数据修改，只是将操作记录了一下；这个记录是在内存中完成的；当第二条DML语句执行成功后，和底层数据库文件中的数据完成同步。若第二条DML语句执行失败，则清空所有的历史操作记录，要完成以上的功能必须借助事务。

事务：一组逻辑操作单元，使数据从一种状态变换到另一种状态。

事务处理（事务操作）：保证每个事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的操作都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所做的所有修改，整个事务回滚(rollback)到最初状态。

为确保数据库中数据的一致性，数据的操纵应当是离散的成组的逻辑单元：当它全部完成时，数据的一致性可以保持，而当这个单元中的一部分操作失败，整个事务应全部视为错误，所有从起始点以后的操作应全部回退到开始状态。

二，MySQL使用事务

mysql提交数据的方式有三种类型：自动提交、隐式提交及显式提交

1、自动提交：

若把AUTOCOMMIT设置为ON，则在插入insert、修改update、删除delete语句执行后，系统将自动进行提交，这就是自动提交。由于MySQL默认已经开启自动提交，我们可以通过对应的设置来开启或者关闭自动提交。

查看变量autocommit自动提交是否开启

方法一：

select@@autocommit;

结果为1，开启；可以设置为0关闭

方法二：

show variables like 'autocommit';

结果为on，开启；可以设置为off关闭

自动提交案例：

（1）创建数据库chap07

create database chap07;

（2）在数据库库chap07下创建表t1

create table t1(id int);

（3）刷新日志文件

flush logs;

（4）查看二进制日志文件

show binary logs;

（5）向表t1插入数据

insert into t1 values(1);

（6）查看所有二进制日志文件

show binary logs;

（7）打开日志文件binlog.000007可以看到自动提交事物

show binlog events in 'binlog.000007'\G

（8）更新t1表，将id改为2

update t1 set id=2 where id=1;

（9）打开日志文件binlog.000007可以看到自动提交事物

show binlog events in 'binlog.000007'\G

关闭自动提交，设置为0即可，可以很大程度上提高数据库性能

方法一：

Set global autocommit=0;

set autocommit=0;

关闭自动提交案例：

（1）关闭自动提交

set autocommit=0;

（2）查看自动提价状态

select @@autocommit;

（3）向表t1中插入数据

（4）表1的所有数据如下

select * from t1;

（5）查看二进制文件的第726行，可以看到没有任何东西

show binlog events in 'binlog.000007' from 726\G

方法二

vim /etc/my.cnf

autocommit=0

2、隐式提交语句：

是在进行事务时执行特定的语句，导致像是使用 commit 提交命令一样使事务提前结束。

导致隐式提交的非事务语句：

DDL语句：（ALTER、CREATE 、truncate和 DROP）

DCL语句：（GRANT、REVOKE 和 SETPASSWORD）

锁定语句：（LOCK TABLES和 UNLOCKTABLES）

隐式提交案例：

（1）查看自动提交状态，为关闭

select @@autocommit;

（2）在自动提交关闭的状态下创建表t2

create table t2(id int,name char(20));

（3）查看二进制文件，发现会自动提交二进制文件

show binlog events in 'binlog.000007' \G

（4）向表t2添加age列

alter table t2 add age int;

（6）查看二进制文件，发现会自动提交二进制文件

show binlog events in 'binlog.000007' \G

3、开始事务流程：

在默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION。

#开启事务

begin：

#提交事务，完成一个事务，一旦事务提交成功，就说明具备ACID特性了。

commit

#回滚事务，将内存中，已执行过的操作，回滚回去

rollback

显示提交案例：

手动提交事物

（1）检查autocommit是否为关闭状态

方法一：mysql8.0[chap07]>show variables like'autocommit';

方法二：

select @@autocommit;

（2）开启事务

begin;

（3）给表t1写入数据

insert into t1 values(3),(4);

（4）提交事物

commit;

（5）查看表t1中的所有信息，数据插入成功

select * from t1;

（6）查看二进制文件，可以看到插入数据的日志

回滚事务

（1）继续向表中插入数据

insert into t1 values(10);

（2）回滚

rollback;

（3）提交事物

commit;

（4）查看表t1中的所有信息，并没有插入成功

select * from t1;

（5）查看二进制日志文件，也没有记录

show binlog events in 'binlog.000007' from 1686\G

手动只有commit提交后，才会写入binlog中，如果中间有rollback，则所有操作会被撤销，

不会写入rollback中

三，事务的特性

事务是由一组SQL语句组成的逻辑处理单元，它的ACID特性如下：

原子性（Atomicity）：原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

隔离性（Isolation）：事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability）：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

四， InnoDB 事务的ACID如何保证

1、原子性（Atomicity）

原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做；如果事务中一个sql语句执行失败，则已执行的语句也必须回滚，数据库退回到事务前的状态。

在说明原子性原理之前，首先介绍一下MySQL的事务日志。MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo 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之前的状态。

2、持久性（Durability）

持久性是指事务一旦提交，它对数据库的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

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大大减少。

我们知道，在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速度大大加快。

3、隔离性（Isolation）

与原子性、持久性侧重于研究事务本身不同，隔离性研究的是不同事务之间的相互影响。隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰。严格的隔离性，对应了事务隔离级别中的Serializable (可串行化)，但实际应用中出于性能方面的考虑很少会使用可串行化。

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

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

（1）一个事务的写操作对另一个事务写操作的影响

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

锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁与表锁

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

（2）一个事务写操作对另一个事务读操作的影响

并发情况下，读操作可能存在的三类问题：

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。

如何解决脏读和不可重复读：可在查询时对查询的数据加锁（共享锁或排它锁）。由于锁的特性，当某事务对数据进行加锁读后，其他事务无法对数据进行写操作，因此可以避免脏读和不可重复读。

幻读的影响：会造成一个事务中先产生的锁，无法锁住后加入的满足条件的行。产生数据一致性问题，在一个事务中，先对符合条件的目标行做变更，而在事务提交前有新的符合目标条件的行加入。这样通过binlog恢复的数据是会将所有符合条件的目标行都进行变更的。

幻读产生的原因：行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

如何解决幻读：需要通过next-key lock。next-key lock是行锁的一种，实现相当于record lock(记录锁) + gap lock(间隙锁)；其特点是不仅会锁住记录本身(record lock的功能)，还会锁定一个范围(gap lock的功能)。

将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。

因此，加锁读同样可以避免脏读、不可重复读和幻读，保证隔离性。

4、事务的隔离级别

SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。隔离级别与读问题的关系如下：

隔离级别	脏读	不可重复读	幻读
Read uncommitted 读未提交	可能	可能	可能
Read committed 读已提交	不可能	可能	可能
Repeatable read 可重复读	不可能	不可能	可能
Serializable 可串行化	不可能	不可能	不可能

在实际应用中，读未提交在并发时会导致很多问题，而性能相对于其他隔离级别提高却很有限，因此使用较少。可串行化强制事务串行，并发效率很低，只有当对数据一致性要求极高且可以接受没有并发时使用，因此使用也较少。因此在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（后文简称RR）。

可以通过命令set transaction命令设置事务隔离级别：

设置事务隔离级别：set transaction isolation level

查询当前事务隔离级别

show variables like '%isolation%';

InnoDB默认的隔离级别是RR，需要注意的是，在SQL标准中，RR是无法避免幻读问题的，但是InnoDB实现的RR避免了幻读问题。

RR解决脏读、不可重复读、幻读等问题，使用的是MVCC：MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。

MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

1）隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

2）基于undo log的版本链：前面说到每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

3）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时事务已经提交了，因此数据对ReadView可见。