mysql学习之事务篇

事务基础

1. 数据库事务概述

1.1 存储引擎支持情况

使用SHOW ENGINES 查看mysql的存储引擎

1.2 基本概念

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

事务处理的原则：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。一个事务执行多个操作时，要么都被 提交，要么都回滚。

1.3 事务的ACID特性

原子性：

原子性指事务是一组不可分割的工作单位，要么全部提交，要么全部失败回滚。

一致性：

一致性是指事务执行前后，数据从一个合法性状态 变换到另一个 合法性状态。

那什么是合法性状态呢？ 满足 预定的约束 的状态就叫做合法的状态。由我们自己来定义的。这种约束包括数据关系的完整性和业务逻辑的完整性，不同的业务场景会有不同的约束，这种约束是外在的业务约束

举个栗子：在现实生活中，我现在有1000块钱，在坐车的时候被小偷偷了300块，那么很明显，我就少了300块，小偷的收入增加300块。这种情况就是一致性，类似于“质量守恒定律”。

隔离性：

事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务都是隔离的。

持久性：

持久性是指一个事务一旦被提交，它对数据库中数据的改变是永久性的。

持久性是通过事务日志，包括redo log 和 undo log。

1.4 事务的状态

分为以下几种：

• 活动的

事务对应的数据库操作正在执行过程中

• 部分提交的

事务最后一个操作执行完，但操作都在内存中执行，还没有刷新到磁盘

• 失败的

在活动的或者部分提交的状态时，遇到某些错误，

• 中止的

遇到失败的状态后，回滚到事务执行之前的状态

• 提交的

数据同步到磁盘上

状态转换图：

2. 如何使用事务

2.1 显示事务

way 1： START TRANSACTION 或者 BEGIN

mysql> BEGIN;
#或者 mysql> START TRANSACTION;

START TRANSACTION 语句相较于 BEGIN 特别之处在于，后边能跟随几个 修饰符 ：

① READ ONLY ：标识当前事务是一个 只读事务 ，也就是属于该事务的数据库操作只能读取数据，而不能修改数据。

② READ WRITE ：标识当前事务是一个 读写事务 ，也就是属于该事务的数据库操作既可以读取数据，也可以修改数据

③ WITH CONSISTENT SNAPSHOT ：启动一致性读。

2.2 隐式事务

MySQL中有一个系统变量 autocommit ：

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.01 sec)

默认自动提交

3. 事务隔离级别

3.1 数据并发问题

1. 脏写（ Dirty Write ）

对两个事务Session A 、Session B,如果事务Session A修改了 另一个未提交 事务Session B修改过得数据

2. 脏读（ Dirty Read ）

对两个事务Session A 、Session B，Session A 读取了 已经被Session B 更新 但还没有被提交得字段 ， 但之后若Session B 回滚，Session A读取得内容就是临时且无效得。

3. 不可重复读（ Non**-**Repeatable Read ）

对两个事务Session A 、Session B，Session A读取了一个字段，然后Session B 更新了该字段 ， 然后Session A 再次读取同一个字段，值就不同了。那就意味着发生了不可重复读。

4. 幻读（ Phantom ）

对两个事务Session A 、Session B，Session A 从一个表中读取了一个字段，然后Session B 在该表中插入了一些新的行。之后，如果Session A再次读取同一个表，就会多出几行。

3.2 SQL 中的四种隔离级别

并发问题按照严重性来排一下序：脏写 > 脏读 > 不可重复读 > 幻读

SQL标准 中设立了4个 隔离级别 ：

• READ UNCOMMITTED ：读未提交，在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。

• READ COMMITTED ：读已提交，它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。可以避免脏读，但不可重复读、幻读问题仍然存在。

• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

• REPEATABLE READ ：可重复读，事务A在读到一条数据之后，此时事务B对该数据进行了修改并提交，那么事务A再读该数据，读到的还是原来的内容。可以避免脏读、不可重复读，但幻读问题仍然存在。这是MySQL的默认隔离级别。

3.3 如何设置事务的隔离级别

通过下面的语句修改事务的隔离级别：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔离级别; 
#其中，隔离级别格式： 
> READ UNCOMMITTED
> READ COMMITTED 
> REPEATABLE READ
> SERIALIZABLE

关于设置时使用GLOBAL或SESSION的影响：

• 使用 GLOBAL 关键字（在全局范围影响）：
  • 当前已经存在的会话无效
  • 只对执行完该语句之后产生的会话起作用
• 使用 SESSION 关键字（在会话范围影响）：
  • 对当前会话的所有后续的事务有效
  • 如果在事务之间执行，则对后续的事务有效
  • 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务

事务日志

• 事务的隔离性由 锁机制 实现。
• 而事务的原子性、一致性和持久性由事务的 redo 日志和undo 日志来保证。
  • redo log 称为 重做日志 ， 提供再写入操作，恢复提交事务修改的页操作，用来保证事务的持久性
  • undo log 称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性

1. redo日志

1.1 为什么需要redo日志

因为事务包含持久性的特征，就是说对于一个已经提交的事务，在事务提交后即使系统发生了奔溃也不能丢失数据。

方法一：在事务提交之前把该事务所修改的所有页面都刷新到磁盘，但这个简单粗暴做法有些问题，效率问题

方法二：记录修改了哪些东西，即使后来系统奔溃，在重启之后也能把这种修改恢复出来

1.2 redo日志的好处、特点

1. 好处

• redo日志降低了刷盘频率
• redo日志占用的空间非常小

2.特点

• redo日志是顺序写入磁盘的
• 事务执行过程中，redo log 不断记录

1.3 redo 的组成

分为两部分：

• 重做日志的缓冲(redo log buffer)：保存在内存中，是易失的。

参数设置：innodb_log_buffer_size

• 重做日志文件(redo log file)：保存在硬盘中，是持久的。

1.4 redo日志流程

第1步：先将原始数据从磁盘中读入内存中来，修改数据的内存拷贝

第2步：生成一条重做日志并写入redo log buffer，记录的是数据被修改后的值

第3步：当事务commit时，将redo log buffer中的内容刷新到 redo log file，对 redo log file采用追加

写的方式

第4步：定期将内存中修改的数据刷新到磁盘中

1.5 redo log 的刷盘策略

redo log 不是直接写入磁盘的，InnoDB引擎会在写redo log 的时候先写redo log buffer，之后以一定的频率刷入到真正的redo log file

注意，redo log buffer刷盘到redo log file的过程并不是真正的刷到磁盘中去，只是刷入到 文件系统缓存 （page cache）中去（这是现代操作系统为了提高文件写入效率做的一个优化），真正的写入会交给系统自己来决定（比如page cache足够大了）。

InnoDB给出 innodb_flush_log_at_trx_commit 参数，该参数控制 commit提交事务时，如何将 redo log buffer 中的日志刷新到 redo log file 中。它支持三种策略：

设置为0 ：表示每次事务提交时不进行刷盘操作。

设置为1 ：表示每次事务提交时都将进行同步，刷盘操作（ 默认值 ）

设置为2 ：表示每次事务提交时都只把 redo log buffer 内容写入 page cache，不进行同步。由os自己决定什么时候同步到磁盘文件。

2. undo日志

redo log是事务持久性的保证，undo log是事务原子性的保证。在事务中 更新数据 的 前置操作 其实是要先写入一个 undo log 。

2.1 如何理解undo日志

事务需要保持原子性，也就是事务中的操作要么全部完成，要么回滚。但有时事务执行到一般的时候会出现一些情况：

• 情况一：事务执行过程中可能遇到各种错误，服务器本身错误操作系统错误
• 情况二：程序员可以在事务执行过程中手动输入rollback语句结束当前事务的执行。

undo log 不同于redo log，它是逻辑日志。可以认为当delete一条记录时undo log 中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

2.2 Undo日志的作用

• 作用1：回滚数据
• 作用2 ：MVCC

2.3 undo log 存储方式

innodb存储引擎对undo管理采用段的方式。rollback segment 称为回滚段，每个回滚段中有1024个undo log segment

mysql 5.5 之后可以支持128个rollback segment，其中一个segment可以记录1024个undo log segment。

undo log默认存放在共享表空间中。

如果开启了innodb_file_per_table，将放在每个表的.ibd文件中。

2.4 delete/update 操作的内部机制

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

通过undo log记录delete和update操作的结果发现：(insert操作无需分析，就是插入行而已)

• delete操作实际上不会直接删除，而是将delete对象打上delete flag，标记为删除，最终的删除操作是purge线程完成的。
• update分为两种情况：update的列是否是主键列。
  • 如果不是主键列，在undo log中直接反向记录是如何update的。即update是直接进行的。
  • 如果是主键列，update分两部执行：先删除该行，再插入一行目标行。

锁

1. 概述

为保证数据的一致性，需要对 并发操作进行控制 ，因此产生了 锁 。同时 锁机制 也为实现MySQL的各个隔离级别提供了保证。 锁冲突 也是影响数据库 并发访问性能 的一个重要因素。所以锁对数据库而言显得尤其重要，也更加复杂。

2. 并发问题

1. 脏读(dirty read )

事务A读取了事务B尚未提交的更新数据，在这个数据基础上进行操作。但事务B此时将事务回滚了，那么事务A读到的数据就是不被承认的

2. 不可重复读(unrepeatavle read )

不可重复读指事务A读取了事务B已经提交的更改数据。

3. 幻读(phantom read)

A事务读取到事务B提交的新增数据，此时A事务将出现幻读问题。

4. 脏写

事务A覆盖了事务B已经提交的数据，导致B事务操作的数据丢失。

3. 并发问题的解决方案

怎么解决脏读、不可重复读、幻读 这些问题呢？

有以下两种方案：

• 方案一：读操作利用多版本并发控制(MVCC) , 写操作进行加锁

• 方案二：读、写操作都采用加锁方式

性能对比：

• 采用MVCC方式的话，读-写操作并不冲突，性能更高
• 采用加锁方式的话，读-写操作本次需要排队执行，影响性能。

4.不同类型锁介绍

4.1 从数据操作类型划分：读锁、写锁

• 读锁：也称共享锁，英文S表示。针对同一份数据，多个事务的读操作可以同时进行而不会互相影响，相互不阻塞的。
• 写锁：也称排他锁，英文X表示。当前写操作没有完成前，会阻塞其他写锁和读锁。这样就能确保在给定的时间里，只有一个事务能执行写入，并防止其他用户读取正在写入的同一资源。

4.2 从数据操作的粒度划分：表级锁、页级锁、行锁

1.表锁

① 表级别的s锁、x锁

一般情况下，不会使用InnoDB存储引擎提供的表级别的 S锁 和 X锁 。只会在一些特殊情况下，比方说 崩 溃恢复 过程中用到。

不过尽量避免在使用InnoDB存储引擎的表上使用 LOCK TABLES 这样的手动锁表语句，它们并不会提供什么额外的保护，只是会降低并发能力而已。I

②意向锁(intention lock)

InnoDB支持 多粒度锁（multiple granularity locking） ，它允许 行级锁 与 表级锁 共存，而意向锁就是其中的一种 表锁 。

意向锁有两种：

• 意向共享锁：事务有意向对表中的某些行加共享锁
• 意向排他锁：事务有意向对表中的某些行加排他锁

意向锁是由存储引擎 自己维护的 ，用户无法手动操作意向锁，在为数据行加共享 / 排他锁之前，InooDB 会先获取该数据行 所在数据表的对应意向锁 。

结论：

1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。

2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。

3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。

③自增锁(AUTO-INC锁)

在此锁定模式下，自动递增值 保证 在所有并发执行的所有类型的insert语句中是 唯一 且 单调递增 的。

但是，由于多个语句可以同时生成数字（即，跨语句交叉编号），为任何给定语句插入的行生成的值可能不是连续的。

④元数据锁

MDL 的作用是，保证读写的正确性。比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。

当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

2.InnoDB中的行锁

① 记录锁(Record Locks)

记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 。

记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁 。

• 当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可以继续获取X型记录锁；
• 当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不可以继续获取X型记录锁。

② 间隙锁(Gap Locks)

MySQL 在 REPEATABLE READ 隔离级别下是可以解决幻读问题的，解决方案有两种，可以使用 MVCC 方案解决，也可以采用 加锁 方案解决。但是在使用加锁方案解决时有个大问题，就是事务在第一次执行读取操作时，那些幻影记录尚不存在，我们无法给这些 幻影记录 加上 记录锁 。

InnoDB提出了一种称之为Gap Locks 的锁，官方的类型名称为： LOCK_GAP ，我们可以简称为 gap锁 。比如，把id值为8的那条记录加一个gap锁的示意图如下

gap锁的提出仅仅是为了防止插入幻影记录而提出的。

③ 临键锁(Next-Key Locks)

有时候我们既想 锁住某条记录 ，又想 阻止 其他事务在该记录前边的 间隙插入新记录 ，所以InnoDB就提出了一种称之为 Next-Key Locks 的锁，官方的类型名称为： LOCK_ORDINARY ，我们也可以简称为next-key锁 。

Next-Key Locks是在存储引擎 innodb 、事务级别在 可重复读 的情况下使用的数据库锁，innodb默认的锁就是Next-Key locks。

④ 插入意向锁(Insert Intention Locks)

我们说一个事务在 插入 一条记录时需要判断一下插入位置是不是被别的事务加了 gap锁 （ next-key锁也包含 gap锁 ），如果有的话，插入操作需要等待，直到拥有 gap锁 的那个事务提交。但是InnoDB规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个 间隙 中 插入 新记录，但是现在在等待。

InnoDB就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为：LOCK_INSERT_INTENTION ，我们称为 插入意向锁 。插入意向锁是一种 Gap锁 ，不是意向锁，在insert操作时产生。

插入意向锁并不会阻止别的事务继续获取该记录上任何类型的锁。

3.页锁

页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一页中可以有多个行记录。

当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。

4.3 按对待锁的态度划分： 乐观锁 、 悲观锁

从对待锁的态度来看锁的话，可以将锁分成乐观锁和悲观锁，从名字中也可以看出这两种锁是两种看待数据并发的思维方式 。需要注意的是，乐观锁和悲观锁并不是锁，而是锁的 设计思想 。

1. 悲观锁(Pessimistic Locking)

悲观锁总会假设最坏的情况发生，每次在拿数据的时候都会加上锁，这样别人想拿这个数据就会阻塞直到它拿到锁。

2.乐观锁(Optimistic Locking)

乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现。

在Java中 java.util.concurrent.atomic 包下的原子变量类就是使用了乐观锁

的一种实现方式：CAS实现的。

1. 乐观锁的版本号机制

2. 乐观锁的时间戳机制

3.两种锁的使用场景

总结一下乐观锁和悲观锁的适用场景：

1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

4.4 按枷锁的方式划分：显示锁、隐式锁

4.5 其他锁之全局锁

全局锁就是对 整个数据库实例 加锁。当你需要让整个库处于 只读状态 的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。全局锁的典型使用 场景 是：做 全库逻辑备份 。

4.6 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。

5.多版本并发控制

5.1 什么是MVCC

MVCC(Multiversion Concurrency Control) , 多版本并发控制。顾名思义，MVCC 是通过数据行的多个版本管理来实现数据库的 并发控制 。

5.2 快照都与当前读

5.2.1 快照读

快照读又叫一致性读，读取的是快照数据。不加锁的简单的 SELECT 都属于快照读，即不加锁的非阻塞读；比如这样：

SELECT * FROM player WHERE ...

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于MVCC，它在很多情况下，避免了加锁操作，降低了开销。

既然是基于多版本，那么快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读。

5.2.2 当前读

当前读读取的是记录的最新版本（最新数据，而不是历史版本的数据），读取时还要保证其他并发事务

不能修改当前记录，会对读取的记录进行加锁。加锁的 SELECT，或者对数据进行增删改都会进行当前读。

5.3 MVCC实现原理之ReadView

**隐藏字段、**Undo Log、Read View。

这个ReadView中主要包含4个比较重要的内容，分别如下：

1. creator_trx_id ，创建这个 Read View 的事务 ID

2. trx_ids ，表示在生成ReadView时当前系统中活跃的读写事务的 事务id列表 。

3. up_limit_id ，活跃的事务中最小的事务 ID。

4. low_limit_id ，表示生成ReadView时系统中应该分配给下一个事务的 id 值。low_limit_id 是系统最大的事务id值，这里要注意是系统中的事务id，需要区别于正在活跃的事务ID。

5.3.1 ReadView的规则

有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见。

• 如果被访问版本的trx_id属性值与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值小于ReadView中的 up_limit_id 值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于或等于ReadView中的 low_limit_id 值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
• 如果被访问版本的trx_id属性值在ReadView的 up_limit_id 和 low_limit_id 之间，那就需要判断一下trx_id属性值是不是在 trx_ids 列表中。
  • 如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
  • 如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问

5.4 MVCC整体操作流程

1. 首先获取事务自己的版本号，也就是事务 ID；

2. 获取 ReadView；

3. 查询得到的数据，然后与 ReadView 中的事务版本号进行比较；

4. 如果不符合 ReadView 规则，就需要从 Undo Log 中获取历史快照；

5. 最后返回符合规则的数据。

在隔离级别为读已提交（Read Committed）时，一个事务中的每一次 SELECT 查询都会重新获取一次Read View。

当隔离级别为可重复读的时候，就避免了不可重复读，这是因为一个事务只在第一次 SELECT 的时候会获取一次 Read View，而后面所有的 SELECT 都会复用这个 Read View，如下表所示：

参考：

康师傅mysql课程【MySQL数据库教程天花板，mysql安装到mysql高级，强！硬！】 https://www.bilibili.com/video/BV1iq4y1u7vj?share_source=copy_web&vd_source=60d97ca4344e44b759cf5dbbb389c32e