MySQL事务大揭秘：事务概述和隔离级别解析

Lion Long

已于 2023-11-27 22:45:06 修改

阅读量5.2k

点赞数 5

分类专栏： # MySQL 文章标签： mysql 数据库 sql 事务后端 linux 数据库开发

于 2022-11-01 14:31:33 首次发布

本文链接：https://blog.csdn.net/Long_xu/article/details/127614683

版权

MySQL 专栏收录该内容

15 篇文章 4 订阅

订阅专栏

MySQL事务概述和隔离级别

事务
ACID特性
隔离级别
- 命令
不同隔离级别并发异常
总结

事务

事务的前提：并发连接访问。MySQL的事务就是将多条SQL语句作为整体进行执行。

事务的定义：用户定义的一系列SQL操作，这些操作要么都执行，要么都不执行，是一个不可分割的单位。

在这里插入图片描述

mysql_threads
事务的本质是并发控制的单元，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单元。

事务的目的：事务将数据库从一种一致性状态转换为另一种一致性状态；保
证系统始终处于一个完整且正确的状态。

事务的组成：事务可由一条非常简单的SQL语句组成，也可以由一组复杂的SQL语句组成。

事务的特征

在数据库提交事务时，可以确保要么所有修改都已经保存，要么所有修改都不保存。事务是访问并更新数据库各种数据项的一个程序执行单元。

在 MySQL innodb 下，单条语句都具备事务；可以通过 set autocommit = 0; 设置当前会话手动提交。

事务的控制语句

-- 显示开启事务
START TRANSACTION | BEGIN
-- 提交事务，并使得已对数据库做的所有修改持久化
COMMIT
-- 回滚事务，结束用户的事务，并撤销正在进行的所有未提交的修改
ROLLBACK
-- 创建一个保存点，一个事务可以有多个保存点
SAVEPOINT identifier
-- 删除一个保存点
RELEASE SAVEPOINT identifier
-- 事务回滚到保存点
ROLLBACK TO [SAVEPOINT] identifier

事务的生命周期

（1）begin … commit。从开始事务到提交事务。
（2）begin … rollback。从开始事务到事务回滚。

事务执行过程

ACID特性

原子性（A）

事务操作要么都做（提交），要么都不做（回滚）。事务是访问并更新数据库各种数据项的一个程序执行单元，是不可分割的工作单位。
MySQL通过 undolog 来实现回滚操作。undolog 记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算。
事务包含的全部操作是一个不可分割的整体，要么全部执行，要么全部不执行。

假如数据库的事务执行到一半，数据库突然宕机，那么宕机前执行的SQL语句不会生效，undolog 会把它回滚到事务开始之前的数据。

undolog是会记录DML操作的日志，回滚就是依赖undolog日志，用来帮助回滚事务的。

一致性（C）

事务的前后，所有的数据都保持一个一致的状态，不能违反数据的一致性检测（完整性约束检查）。

一致性指事务将数据库从一种一致性状态转变为下一种一致性的状态，在事务执行前后，数据库完整性约束没有被破坏；一
个事务单元需要提交之后才会被其他事务可见。例如：一个表的姓名是唯一键，如果一个事务对姓名进行修改，但是在事务
提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性。

一致性有数据库完整约束和逻辑上一致性。

一致性由原子性、隔离性以及持久性共同来维护的。

隔离性（I）

描述：各个事务之间互相影响的程度。

目的：主要规定多个事务访问同一数据资源，各个事务对该数据资源访问的行为，不同的隔离性是应对不同的现象（脏读、
不可重复读、幻读）。

事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，并发事务之间不会相互影响，设定了不同程度的
隔离级别，通过适度破环一致性，得以提高性能。
隔离性通过 MVCC和锁来实现。MVCC是多版本并发控制，主要解决一致性非锁定读，通过记录和获取行版本，而不是使用锁来限制读操作，从而实现高效并发读性能。锁用来处理并发 DML 操作。

数据库中提供粒度锁的策略，针对表（聚集索引 B+ 树）、页（聚集索引 B+ 树叶子节点）、行（叶子节点当中某一段记录行）三种粒度加锁。

持久性（D）

事务一旦完成，要将数据所做的变更记录下来，包括数据存储和多副本的网络备份。
事务提交后，事务 DML 操作将会持久化（写入 redolog 磁盘文件哪一个页页偏移值具体数据）；即使发生宕机等故障，数据
库也能将数据恢复。

redolog 记录的是物理日志。

隔离级别

隔离级别的目的是提升并发性能。隔离级别越高，事务之间的影响级别最低，同时效率也是最低。隔离级别中，所有写操作都要加锁，唯一不同的是读操作，不同的隔离级别读操作的加锁程度不一样。

ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，各数据库厂商在正确性和性能之间做了妥协，并没有严格遵循这些标准。MySQL innodb默认支持的隔离级别是 REPEATABLE READ。

降低隔离级别都会影响一致性，准确的说是影响逻辑上的一致性。

（1） READ UNCOMMITTED。读未提交；该级别下读不加锁，写加排他锁，写锁在事务提交或回滚后释放锁。读未提交可能会造成逻辑上一致性的问题；比如，可能读到其他事务还没提交的数据，也就是读到其他事务的中间状态。
（2）READ COMMITTED。读已提交；这是大部分数据库采用的隔离级别。从该级别后支持 MVCC (多版本并发控制)，也就是提供一致性非锁定读；此时读取操作读取历史快照数据。该隔离级别下读取历史版本的最新数据，所以读取的是已提交的数据。这种隔离级别有可能存在一个问题：两个事务，一个事务有两个select读取同一行数据，另一个事务在两个select之间修改该行数据并提交，这就造成两次读的数据不一致。

（3）REPEATABLE READ。可重复读（RR）；该级别下也支持 MVCC，此时读取操作读取事务开始时的版本数据。这个隔离级别可以解决READ COMMITTED逻辑上的问题，避免了两次读取的数据不一致。这个也存在一个逻辑问题，就是如果是两次范围读取数据，而第二次读取数据使用了for update（加锁），就会造成两次读的行记录不一样，即幻读。

（4）SERIALIZABLE。可串行化；该级别下给读加了共享锁；所以事务都是串行化的执行，此时隔离级别最严苛。

命令

-- 设置隔离级别
SET [GLOBAL | SESSION] TRANSACTION ISOLATION
LEVEL REPEATABLE READ;
-- 或者采用下面的方式设置隔离级别
SET @@tx_isolation = 'REPEATABLE READ';
SET @@global.tx_isolation = 'REPEATABLE READ';
-- 查看全局隔离级别
SELECT @@global.tx_isolation;
-- 查看当前会话隔离级别
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
-- 手动给读加 S 锁
SELECT ... LOCK IN SHARE MODE;
-- 手动给读加 X 锁
SELECT ... FOR UPDATE;
-- 查看当前锁信息
SELECT * FROM information_schema.innodb_locks;

不同隔离级别并发异常

准备，建立一张数据表：

DROP TABLE IF EXISTS `account_t`;
CREATE TABLE `account_t`(
	`id` int(11) not NULL,
	`name` VARCHAR(255) DEFAULT NULL,
	`money` INT(11) DEFAULT 0,
	PRIMARY KEY (`id`),
	KEY `idx_name` (`name`)
)ENGINE=INNODB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

select * from `account_t`;
INSERT into `account_t` values(1,'c',1000),(2,'b',1000),(3,'a',1000);

脏读

一个事务读到另一个未提交事务修改的数据。事务（A）可以读到另外一个事务（B）中未提交的数据；也就是事务A读到脏数据。

-- 事务1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN
-- 先执行查询
select money from `account_t` WHERE name='a';
-- 事务1更改数据后，再次执行查询
select money from `account_t` WHERE name='a';

ROLLBACK;

-- 事务2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


BEGIN
-- 事务1先执行查询，再修改数据
UPDATE `account_t` set money=money-100 WHERE name='a';

UPDATE `account_t` set money=money+100 WHERE name='b';

COMMIT;
ROLLBACK;

在读写分离的场景下，可以将 slave 节点设置为 READ UNCOMMITTED；此时脏读不影响，在 slave 上查询并不需要特别精准的返回值。

不可重复读

事务（A) 可以读到另外一个事务（B）中提交的数据；通常发生在一个事务中两次读到的数据是不一样的情况；不可重复读在
隔离级别 READ COMMITTED 存在。

一般而言，不可重复读的问题是可以接受的，因为读到已经提交的数据，一般不会带来很大的问题，所以很多厂商（如Oracle、SQL Server）默认隔离级别就是 READ COMMITTED。

-- 事务1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN

UPDATE `account_t` set money=money-100 WHERE name='a';

UPDATE `account_t` set money=money+100 WHERE name='b';

COMMIT;

-- 事务2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN
-- 事务1更新数据并提交前执行
select money from `account_t` WHERE name='a';
-- 事务1更新数据并提交后 执行
select money from `account_t` WHERE name='a';
COMMIT;

幻读

两次读取同一个范围内的记录得到的结果集不一样。
例如：

幻读在隔离级别 REPEATABLE READ 及以下存在；但是可以在 REPEATABLE READ 级别下通过读加锁（使用 next-key locking）解决。

丢失更新

脏读、不可重复读、幻读都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了不该读的数据。
丢失更新是两个事务都是写。丢失更新分为提交覆盖和回滚覆盖；回滚覆盖会被数据库拒绝，所以不可能产生，重点关注提交覆盖。

区别

（1）脏读和不可重复读的区别在于，脏读是读取了另一个事务未提交的数据，而不可重复读是读取了另一个事务提交后的修改。本质上都是其他事务的修改影响了本事务的读取。
（2）不可重复读和幻读比较类似；不可重复读是两次读取同一条记录，得到不一样的结果；而幻读是两次读取同一个范围内的记录得到的结果集不一样（可能不同个数，也可能相同个数内容不一样，比如x一行后又添加新行）。
（3）不可重复读是因为其他事务进行了 update 操作，幻读是因为其他事务进行了 insert或者 delete 操作。