leisurexi's Blog.

MySQL事务

MySQL
字数统计: 5.4k阅读时长: 19 min
2020/01/18 Share

本篇文章主要介绍 MySQL 中的事务，以及事务的实现原理。

事务特点

事务的特点主要是 ACID，即：

  • Atomicity (原子性)： 一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作。
  • Consistency (一致性)： 数据库总是从一个一致性状态转换到另一个一致状态。
  • Isolation (隔离性)： 通常来说，一个事务所做的修改在最终提交以前，对其它事务是不可见的。注意这里的 ”通常来说“ ，后面的事务隔离级别会说道。
  • Durability (持久性)： 一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性的安全性与刷新日志级别也存在一定关系，不同的级别对应不同的数据安全级别。

为了更好地理解 ACID，以银行账户转账为例：

1
2
3
4
5
START TRANSACTION;
SELECT balance FROM account WHERE account_no = 10233276;
UPDATE account SET balance = balance - 200 WHERE account_no = 10233276;
UPDATE account SET balance = balance + 200 WHERE account_no = 10322211;
COMMIT;
  • 原子性： 要么完全提交 (10233276的余额减少200，10322211的余额增加200)，要么完全回滚 (两个账户的余额都不发生变化)。
  • 一致性： 这个例子的一致性体现在200元不会因为数据库系统运行到第3行之后，第4行之前时崩溃而导致不翼而飞，因为事务还没有提交。
  • 隔离性： 允许在一个事务中的操作语句会与其它事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询余额时，它仍然能够看到事务A中被减去的200元 (账户钱不变)，因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变。
  • 持久性： 事务一旦提交，对数据修改是永远的。

事务的隔离性是用过锁、MVCC 等实现。

事务的原子性、一致性和持久性则是通过事务日志实现。

事务的隔离级别

并发事务带来的问题

  • 更新丢失 (Lost Update)： 当两个或多个事务选择同一行数据，然后基于最初选定的值更新该行时，由于每个事务都不知道其它事务的存在，就会发生丢失更新的问题——最后的更新覆盖了之前其它事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了之前编辑人员所做的修改 (最后一个编辑人员所做的修改覆盖之前编辑人员所做的更改)。如果在一个编辑人完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。
  • 脏读 (Dirty Reads)： 一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取这一条记录，如果不加控制，第二个事务读取了这些 ”脏“ 数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫作 ”脏读“。
  • 不可重复读 (Non-Repeatable Reads)： 一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变。这种现象就叫做 ”不可重复读“。
  • 幻读 (Phantom Reads)： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其它事务插入了满足其查询条件的新数据，这种现象就称为 ”幻读“。

幻读和不可重复读的区别

  • 不可重复读的重点是修改： 在用一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。(因为中间有其它事务提交了修改)
  • 幻读的重点在于新增或删除： 在用一事务中，同样的条件，第一此和第二次读出来的记录数不一样。(因为中间有其它事务所做的 插入/删除 提交了)

4种隔离级别

SQL标准定义了4类隔离级别，每一种级别都规定了一个事务中所造成的修改，哪些在事务内和事务间是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

第1级别：Read Uncommitted (读取未提交内容)

  • 所有事务都可以看到其它未提交事务的执行结果。
  • 本隔离级别很少用于实际应用，因为它的性能也不比其它级别好多少。
  • 该级别引发的问题是—— 脏读 (Dirty Read)： 读取到了未提交的数据。

第2级别：Read Committed (读取提交的内容)

  • 这是大多数数据库系统的默认隔离级别 (但不是 MySQL 默认的)。
  • 它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。
  • 这种隔离级别可以避免 脏读 ，但出现的问题是—— 不可重复读 (Norrepeatable Read)： 不可重复读意味着我们在同一个事务中执行完全相同的 SELECT 语句是可能看到不一样的结果。导致这种情况的原因可能有：
    • 有一个交叉的事务有新的 COMMIT ，导致了数据的改变。
    • 一个数据库被多个实例操作，同一事务的其它实例在该实例处理期间可能会有新的 COMMIT 。

第3级别：Repeatable Read (可重复读)

  • 这是 MySQL 的默认隔离级别。
  • 它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。
  • 这种隔离级别可以避免 脏读 、不可重复读 ，但出现的问题是—— 幻读 (Phantom Read)： 当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户再读取该范围的数据行时，会发现有新的 ”幻影“ 行。
  • InnoDB 和 Falcon 存储引擎通过多版本 并发控制 (MVCC，Multiversion Concurrency Control) 机制来解决 幻读 问题；InnoDB 还通过 间隙锁 解决 幻读 问题。

第4级别：Serializable (可串行化)

  • 这是最高的隔离级别。
  • 它通过强制事务排序，是指不可能相互冲突，从而解决 幻读 问题。简言之，它是在每个读的数据行上加上 共享锁。
  • 在这个级别，可能导致大量的 超时现象和锁竞争。

隔离级别的比较

v2-17425f8aaf39eb83a451f9c8a8133427_hd.png

事务日志

事务日志可以帮助提高事务效率：

  • 使用事务日志，存储引擎在修改表的数据时只需修改其内存拷贝，再把该修改行为记录到持久化在硬盘上的事务日志中，而不用每次都将修改的数据本身持久化到磁盘。
  • 事务日志采用的是追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序 I/O ，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。
  • 事务日志持久以后，内存中被修改的数据在后台可以慢慢刷回到磁盘。
  • 如果数据的修改已经记录到事务日志并持久化，但数据本身没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这一部分修改的数据。

目前来说，大多数存储引擎都是这样实现的，我们通常称之为 预写式日志 (Write-Ahead Logging) ，修改数据需要写两次磁盘。

MySQL中的事务实现原理

事务的实现是基于数据库的存储引擎。不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有 InnoDB 和 NDB 。

InnoDB 是 MySQL 默认的存储引擎，默认的隔离级别是 可重复读 (Repeatable Read) ，并且在 可重复读 的级别下更进一步，通过多版本 并发控制 解决了 不可重复读 问题，加上 间隙锁 (也就是并发控制) 解决 幻读 问题。因此 InnoDB 的 可重复读 隔离级别其实现了 串行化 级别的效果，而且保留了比较好的并发性能。

事务的隔离性是通过锁实现的，而事务的原子性、一致性和持久性则是用过事务日志实现。说到事务日志，不得不说的就是 redo 和 undo 。

1. redo log

在 InnoDB 的存储引擎中，事务日志用过重做 (redo) 日志和 InnoDB 存储引擎的日志缓冲 (InnoDB Log Buffer) 实现。事务开启时，事务中的操作，都会先写入存储引擎的日志缓冲中，在事务提交之前，这些缓冲的日志都需要提前刷新到磁盘上持久化，这就是 DBA 们口中常说的 “日志先行” (Write-Ahead Logging)。当事务提交之后，在 Buffer Pool 中映射的数据文件才会慢慢刷新到磁盘。此时如果数据库崩溃或者宕机，那么当系统重启进行恢复时，就可以根据 redo log 中记录的日志，把数据库恢复到崩溃前的一个状态。未完成的事务，可以继续提交，也可以选择回滚，这基于恢复的策略而定。

在系统重启的时候，就已经为 redo log 分配了一块连续的存储空间，以顺序追加的方式记录 redo log ，通过顺序 I/O 来改善性能。所有的事务共享 redo log 的存储空间，它们的 redo log 按语句的执行顺序，一次交替的记录在一起。如下一个简单实例：

1
2
3
4
5
6
7
8
9
记录1：<trx1, insert...>

记录2：<trx2, delete...>

记录3：<trx3, update...>

记录4：<trx1, update...>

记录5：<trx3, insert...>

2. Undo log

undo log 主要为事务的回滚服务。在事务执行的过程中，除了记录 redo log ，还会记录一定量的 undo log。undo log 记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据 undo log 进行回滚操作。单个事务的回滚，只会回滚当前事务做的操作，并不会影响到其他的事务做的操作。

以下是 undo + redo 事务的简化过程：

假设有2个数值，分别为A和B,值为1，2

  1. start transaction;
  2. 记录 A = 1 到 undo log;
  3. update A = 3；
  4. 记录 A = 3 到 redo log；
  5. 记录 B = 2 到 undo log；
  6. update B = 4；
  7. 记录B = 4 到redo log；
  8. 将 redo log 刷新到磁盘
  9. commit;

在 1-8 的任意一步系统宕机，事务未提交，该事务就不会对磁盘上的数据做任何影响，如果在8-9之间宕机，恢复之后可以选择回滚，也可以选择继续完成事务提交，因为此时 redo log 已经持久化。如果 9 之后系统宕机，内存映射中变更的数据还来不及刷回磁盘，那么系统恢复之后，可以根据 redo log 把数据刷回磁盘。

所以，redo log 其实保障的是事务和持久性和一致性，而 undo log 则保障了事务的原子性。

MVCC 的实现

MySQL 的大多数事务型存储引擎实现都不是简单的行级锁。基于提升并发性考虑，一般都同时实现了多版本并发控制 (MVCC)，包括 Oracle、PostgreSQL。不过实现各不相同。

MVCC 的实现是通过保存数据在某一个时间点的快照来实现的。也就是说不管执行时间多长，每个事务看到的数据都是一致的。

并发控制分为 乐观 (optimistic) 并发控制和 悲观 (pressimistic) 并发控制。

InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现。这两个列一个保存了行的创建时间，一个保存行的过期时间 (删除时间)。当然存储的并不是真实的时间而是系统版本号 (system version number)。每开始一个新的事务，系统版本号都会自动新增。事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。

REPEATABLE READ (可重读) 隔离级别下 MVCC 如何工作：

  • SELECT： InnoDB 会根据以下条件检查每一行记录：

    • InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的。
    • 行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。

    只有符合上述两个条件的才会被查询出来。

  • INSERT： InnoDB 为新插入的每一行数据保存当前系统版本号作为行版本号。

  • DELETE： InnoDB 为删除的每一行保存当前系统版本号作为删除标识。

  • UPDATE： InnoDB 为插入的一行新记录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识。保存这两个版本号，使大多数操作都不用加锁。使数据操作简单，性能很好，并且能保证只会读取到符合要求的行。不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。

MVCC 只在 COMMITED READ (读提交) 和 REPEATABLE READ (可重复读) 两种隔离级别下工作。

可以认为 MVCC 是行级锁的一个变种，但是它很多情况下避免了加锁操作，开销更低。虽然不同数据库的实现机制有所不同，但大都实现了非阻塞的读操作 (读操作不用加锁，且能避免出现 不可重复读 和 幻读)，写操作也只锁定必要的行 (写必须加锁，否则不同事物并发写会导致数据不一致)。

举例说明

1
2
3
4
create table user(
	id int primary auto_increment,
    name varchar(50)
);

transaction 1：

1
2
3
4
start transaction;
insert into user(name) values('leisurexi');
insert into user(name) values('西大大');
commit;

假设系统初始事务 ID 为1。

ID NAME 创建时间 过期时间 (删除时间)
1 leisurexi 1 undefined
2 西大大 1 undefined

transaction 2：

1
2
3
4
start transaction;
select id, name from user; --(1)
select id, name from user; --(2)
commit;

假设当执行事务2的过程中，准备执行 语句 (2) 时，开始执行事务3：

SELECT

transaction 3：

1
2
3
start transaction;
insert into user(name) values('罗大大');
commit;
ID NAME 创建时间 过期时间 (删除时间)
1 leisurexi 1 undefined
2 西大大 1 undefined
3 罗大大 3 undefined

事务3执行完毕，开始执行事务2的 语句 (2)，由于事务2只能查询创建小于等于 2 的，所以事务3新增的记录在事务2中是查不出来，这就通过 乐观锁 的方式避免了 幻读 的产生。

UPDATE

假设当执行事务2的过程中，准备执行 语句 (2) 时，开始执行事务4：

transaction 4：

1
2
3
start transaction;
update user set name = '赵四' where id = 2;
commit

InnoDB 执行 UPDATE 实际上是新插入了一行记录，并保存其创建时间为当前事务的 ID，同时保存当前事务 ID 到要 UPDATE 的行的删除时间。

ID NAME 创建时间 过期时间 (删除时间)
1 leisurexi 1 undefined
2 西大大 1 4
2 赵四 4 undefined

事务4执行完毕，开始执行事务2的 语句 (2)，由于事务2只能查询创建时间小于等于 2 并且过期时间大于等于 2 的记录，所以事务4修改的记录在事务2中是查不出来的，这样就保证了事务在两次读取时读取到的数据的状态是一致的。

DELETE

假设当执行事务2的过程中，准备执行 语句 (2) 时，开始执行事务5：

1
2
3
start transaction;
delete from user where id = 2;
commit;
ID NAME 创建时间 过期时间 (删除时间)
1 leisurexi 1 undefined
2 西大大 1 5

事务5执行完毕，开始执行事务2的 语句 (2)，由于事务2只能查询创建时间小于等于 2 并且过期时间大于等于 2 的记录，所以还是可以查询两条记录，这样就保证了事务在两次读取时读取到的数据状态时一致的。

MySQL 中的事务使用

MySQL 的服务层不管理事务，而是由下层的存储引擎实现。比如 InnoDB 。

MySQL 支持本地事务的语句。

1
2
3
4
START TRANSACTION | BEGIN [WORK] 
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] 
SET AUTOCOMMIT = {0 | 1}
  • START TRANSACTION 或 BEGIN 语句： 开始一项新的事务。
  • COMMIT 和 ROLLBACK： 用来提交或者回滚事务。
  • CHAIN 和 RELEASE 子句： 分别用来定义在事务提交或者回滚之后的操作，CHAIN 会立即启动一个新事务，并且和刚才的事务具有相同的隔离级别，RELEASE 则会断开和客户端的连接。
  • SET AUTOCOMMIT： 可以修改当前连接的提交方式，如果设置了 SET AUTOCOMMIT = 0 ，则设置之后的所有事务都需要通过明确的命令进行提交或者回滚。

事务使用注意点：

  • 如果在锁表期间，用 START TRANSACTION 命令开启一个新事务，会造成一个隐含的 unlock tables 被执行。
  • 在同一个事务中，最好不使用不同存储引擎的表，否则 ROLLBACK 时需要对不支持事务类型的表进行特别的处理，因为 COMMIT、ROLLBACK 只能对支持事务类型的表进行提交和回滚。
  • 和 Oracle 的事务管理相同，所有的 DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交。
  • 在事务中可以通过定义 SAVEPOINT (例如：mysql> savepoint test; 定义 savepoint ，名称为 test)，指定回滚事务的一部分，但是不能指定提交事务的一部分。对于复杂的应用，可以定义多个不同的 SAVEPOINT ，满足不同的条件时，回滚不同的 SAVEPOINT 。需要注意的是，如果定义了相同名字的 SAVEPOINT ，则后面定义的 SAVEPOINT 会覆盖之前的定义。对于不再需要使用的 SAVEPOINT ，可以通过 RELEASE SAVEPOINT 命令删除 SAVEPOINT ，删除后的 SAVEPOINT ，不能再执行 ROLLBACL TO SAVEPOINT 命令，否则会报异常。

MySQL之DDL、DML、DCL、TCL的区别

DDL (Data Definition Language)： 主要命令有 CREATE、ALTER、DROP 等，DDL 主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上。

DML (Data Manipulation Language)： 命令有 SELECT、UPDATE、INSERT、DELETE ，这4条命令是用来对数据库里的数据进行操作的语言。

DCL (Data Control Language)： 是数据库控制功能。主要用来设置或更改数据库用户或角色权限的语句，包括 grant、deny、revoke 等语句。

TCL (Transaction Control Language)： 事务控制语言。主要命令有 SAVEPOINT、ROLLBACK、START TRANSACTION、COMMIT 等。

自动提交（AUTOCOMMIT）:

InnoDB 默认采用事务 自动提交(AUTOCOMMIT) 机制。也就是说，如果不是显示开启一个事务，则每条 SQL 语句都 形成独立事务 。如果该语句执行后没有返回错误，MySQL 会自动执行 COMMIT 。但是如果该语句返回错误，则根据 错误情况 执行 COMMIT 或 ROLLBACK 。

可以通过以下命令修改当前会话的提交模式。

1
2
-- 1或者ON表示启动，0或者OFF表示禁用
SET AUTOCOMMIT = 0;

注意：

  • 关闭后，会话将始终开启一个事务。直到你显示提交或回滚该事务后，一个新事务又被开启。
  • 如果关闭了 AUTOCOMMIT 的会话没有显示提交事务，然后会话被关闭，MySQL 将回滚事务。
  • 有一些命令，在执行之后会强制执行 COMMIT 提交当前的活动事务。例如：ALTER TABLE、LOCK TABLES。

隐式锁定：

InnoDB 在事务执行过程中，使用两阶段锁协议：

  • 随时都可以执行锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。
  • 锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会释放，并且所有的锁都是在 同一时刻 被释放。

显示锁定：

InnoDB 也支持通过特定的语句进行显示锁定（存储引擎层）：

1
2
select ... lock in share mode --共享锁 
select ... for update --排他锁

MySQL Server 层的显示锁定：

1
lock table 和 unlock table

参考

https://www.jianshu.com/p/f692d4f8a53e

https://zhuanlan.zhihu.com/p/29166694

https://blog.csdn.net/w_linux/article/details/79666086

原文作者：leisurexi

原文链接：https://github.com/leisurexi/category/2020/01/18/MySQL/MySQL事务.html

发表日期：January 18th 2020, 12:09:00 am

更新日期：January 19th 2020, 9:17:03 pm

版权声明：本文采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可

CATALOG
  1. 1. 事务特点
  2. 2. 事务的隔离级别
    1. 2.1. 并发事务带来的问题
    2. 2.2. 幻读和不可重复读的区别
    3. 2.3. 4种隔离级别
      1. 2.3.1. 第1级别：Read Uncommitted (读取未提交内容)
      2. 2.3.2. 第2级别：Read Committed (读取提交的内容)
      3. 2.3.3. 第3级别：Repeatable Read (可重复读)
      4. 2.3.4. 第4级别：Serializable (可串行化)
    4. 2.4. 隔离级别的比较
    5. 2.5. 事务日志
    6. 2.6. MySQL中的事务实现原理
    7. 2.7. MVCC 的实现
      1. 2.7.1. REPEATABLE READ (可重读) 隔离级别下 MVCC 如何工作：
        1. 2.7.1.1. 举例说明
        2. 2.7.1.2. SELECT
        3. 2.7.1.3. UPDATE
        4. 2.7.1.4. DELETE
    8. 2.8. MySQL 中的事务使用
  3. 3. 参考
Total : 26
2020
2019
CentOS 7 JVM 设计模式 Zookeeper Kafka MySQL RabbitMQ 并发 Nacos Spring IoC 集合 ELK Redis
CentOS-7 JVM 设计模式 CentOS-7/Zookeeper MySQL RabbitMQ 并发 CentOS-7/Zookeeper/Kafka CentOS-7/MySQL Spring-IoC CentOS-7/MySQL/Nacos 集合 ELK Redis