MySQL学习笔记之事务

2022-08-18 16:40:41

Mysql 事务

不是所有的 Mysql 存储引擎都实现了事务处理。支持事务的存储引擎有：InnoDB 和 NDB Cluster。不支持事务的存储引擎，代表有：MyISAM。

用户可以根据业务是否需要事务处理（事务处理可以保证数据安全，但会增加系统开销），选择合适的存储引擎。

1. 事务简介

事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败。进一步说，事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

事务就是一组原子性的 SQL 语句。具体来说，事务指的是满足 ACID 特性的一组操作。

事务内的 SQL 语句，要么全执行成功，要么全执行失败。

通过加锁的方式，可以实现不同的事务隔离机制。

想象一下，如果没有事务，在并发环境下，就可能出现丢失修改的问题。

T₁ 和 T₂ 两个线程都对一个数据进行修改，T₁ 先修改，T₂ 随后修改，T₂ 的修改覆盖了 T₁ 的修改。

2. 事务用法

2.1. 事务处理指令

Mysql 中，使用 START TRANSACTION 语句开始一个事务；使用 COMMIT 语句提交所有的修改；使用 ROLLBACK 语句撤销所有的修改。不能回退 SELECT 语句，回退 SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。

START TRANSACTION - 指令用于标记事务的起始点。
SAVEPOINT - 指令用于创建保留点。
ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退到 START TRANSACTION 语句处。
COMMIT - 提交事务。

事务处理示例：

（1）创建一张示例表

-- 撤销表 user
DROP TABLE IF EXISTS user;

-- 创建表 user
CREATE TABLE user (
  id int(10) unsigned NOT NULL COMMENT 'Id',
  username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户名',
  password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
  email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱'
) COMMENT='用户表';

（2）执行事务操作

-- 开始事务
START TRANSACTION;

-- 插入操作 A
INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@163.com');

-- 创建保留点 updateA
SAVEPOINT updateA;

-- 插入操作 B
INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@163.com');

-- 回滚到保留点 updateA
ROLLBACK TO updateA;

-- 提交事务，只有操作 A 生效
COMMIT;

（3）执行结果

SELECT * FROM user;

结果：

1	root1	root1	xxxx@163.com

2.2. AUTOCOMMIT

MySQL 默认采用隐式提交策略（autocommit）。每执行一条语句就把这条语句当成一个事务然后进行提交。当出现 START TRANSACTION 语句时，会关闭隐式提交；当 COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交；autocommit 标记是针对每个连接而不是针对服务器的。

-- 查看 AUTOCOMMIT
SHOW VARIABLES LIKE 'AUTOCOMMIT';

-- 关闭 AUTOCOMMIT
SET autocommit = 0;

-- 开启 AUTOCOMMIT
SET autocommit = 1;

3. ACID

ACID 是数据库事务正确执行的四个基本要素。

原子性（Atomicity）
- 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
- 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
一致性（Consistency）
- 数据库在事务执行前后都保持一致性状态。
- 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
隔离性（Isolation）
- 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
持久性（Durability）
- 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
- 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易。

只有满足一致性，事务的执行结果才是正确的。
在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子性，就一定能满足一致性。
在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离性，才能满足一致性。
事务满足持久化是为了能应对系统崩溃的情况。

MySQL 默认采用自动提交模式（AUTO COMMIT）。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询操作都会被当做一个事务并自动提交。

4. 事务隔离级别

4.1. 事务隔离简介

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题：

丢失修改
脏读
不可重复读
幻读

在 SQL 标准中，定义了四种事务隔离级别（级别由低到高）：

未提交读
提交读
可重复读
串行化

Mysql 中查看和设置事务隔离级别：

-- 查看事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';

-- 设置事务隔离级别为 READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 设置事务隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置事务隔离级别为 REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 设置事务隔离级别为 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

4.2. 未提交读

未提交读（READ UNCOMMITTED） 是指：事务中的修改，即使没有提交，对其它事务也是可见的。

未提交读的问题：事务可以读取未提交的数据，也被称为 脏读（Dirty Read）。

T₁ 修改一个数据，T₂ 随后读取这个数据。如果 T₁ 撤销了这次修改，那么 T₂ 读取的数据是脏数据。

4.3. 提交读

提交读（READ COMMITTED） 是指：一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。提交读解决了脏读的问题。

提交读是大多数数据库的默认事务隔离级别。

提交读有时也叫不可重复读，它的问题是：执行两次相同的查询，得到的结果可能不一致。

T₂ 读取一个数据，T₁ 对该数据做了修改。如果 T₂ 再次读取这个数据，此时读取的结果和第一次读取的结果不同。

4.4. 可重复读

可重复读（REPEATABLE READ） 是指：保证在同一个事务中多次读取同样数据的结果是一样的。可重复读解决了不可重复读问题。

可重复读是 Mysql 的默认事务隔离级别。

可重复读的问题：当某个事务读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务又再次读取该范围的记录时，会产生 幻读（Phantom Read）。

T₁ 读取某个范围的数据，T₂ 在这个范围内插入新的数据，T₁ 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。

4.5. 串行化

串行化（SERIALIXABLE） 是指：强制事务串行执行。

强制事务串行执行，则避免了所有的并发问题。串行化策略会在读取的每一行数据上都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，所以一般不会采用这个级别。

4.6. 隔离级别小结

未提交读（READ UNCOMMITTED） - 事务中的修改，即使没有提交，对其它事务也是可见的。
提交读（READ COMMITTED） - 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
重复读（REPEATABLE READ） - 保证在同一个事务中多次读取同样数据的结果是一样的。
串行化（SERIALIXABLE） - 强制事务串行执行。

数据库隔离级别解决的问题：

隔离级别	脏读	不可重复读	幻读
未提交读	❌	❌	❌
提交读	✔️	❌	❌
可重复读	✔️	✔️	❌
可串行化	✔️	✔️	✔️

5. 死锁

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

产生死锁的场景：

当多个事务试图以不同的顺序锁定资源时，就可能会产生死锁。
多个事务同时锁定同一个资源时，也会产生死锁。

5.1. 死锁的原因

行锁的具体实现算法有三种：record lock、gap lock 以及 next-key lock。record lock 是专门对索引项加锁；gap lock 是对索引项之间的间隙加锁；next-key lock 则是前面两种的组合，对索引项以其之间的间隙加锁。

只在可重复读或以上隔离级别下的特定操作才会取得 gap lock 或 next-key lock，在 Select、Update 和 Delete 时，除了基于唯一索引的查询之外，其它索引查询时都会获取 gap lock 或 next-key lock，即锁住其扫描的范围。主键索引也属于唯一索引，所以主键索引是不会使用 gap lock 或 next-key lock。

在 MySQL 中，gap lock 默认是开启的，即 innodb_locks_unsafe_for_binlog 参数值是 disable 的，且 MySQL 中默认的是 RR 事务隔离级别。

当我们执行以下查询 SQL 时，由于 order_no 列为非唯一索引，此时又是 RR 事务隔离级别，所以 SELECT 的加锁类型为 gap lock，这里的 gap 范围是 (4,+∞）。

SELECT id FROM demo.order_record where order_no = 4 for update;

执行查询 SQL 语句获取的 gap lock 并不会导致阻塞，而当我们执行以下插入 SQL 时，会在插入间隙上再次获取插入意向锁。插入意向锁其实也是一种 gap 锁，它与 gap lock 是冲突的，所以当其它事务持有该间隙的 gap lock 时，需要等待其它事务释放 gap lock 之后，才能获取到插入意向锁。

以上事务 A 和事务 B 都持有间隙 (4,+∞）的 gap 锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的 gap 锁释放，于是就造成了循环等待，导致死锁。

INSERT INTO demo.order_record(order_no, status, create_date) VALUES (5, 1, ‘2019-07-13 10:57:03’);

另一个死锁场景

InnoDB 存储引擎的主键索引为聚簇索引，其它索引为辅助索引。如果使用辅助索引来更新数据库，就需要使用聚簇索引来更新数据库字段。如果两个更新事务使用了不同的辅助索引，或一个使用了辅助索引，一个使用了聚簇索引，就都有可能导致锁资源的循环等待。由于本身两个事务是互斥，也就构成了以上死锁的四个必要条件了。

出现死锁的步骤：

综上可知，在更新操作时，我们应该尽量使用主键来更新表字段，这样可以有效避免一些不必要的死锁发生。

5.2. 避免死锁

预防死锁的注意事项：

在编程中尽量按照固定的顺序来处理数据库记录，假设有两个更新操作，分别更新两条相同的记录，但更新顺序不一样，有可能导致死锁；
在允许幻读和不可重复读的情况下，尽量使用 RC 事务隔离级别，可以避免 gap lock 导致的死锁问题；
更新表时，尽量使用主键更新；
避免长事务，尽量将长事务拆解，可以降低与其它事务发生冲突的概率；
设置合理的锁等待超时参数，我们可以通过 innodb_lock_wait_timeout 设置合理的等待超时阈值，特别是在一些高并发的业务中，我们可以尽量将该值设置得小一些，避免大量事务等待，占用系统资源，造成严重的性能开销。

另外，我们还可以将 order_no 列设置为唯一索引列。虽然不能防止幻读，但我们可以利用它的唯一性来保证订单记录不重复创建，这种方式唯一的缺点就是当遇到重复创建订单时会抛出异常。

我们还可以使用其它的方式来代替数据库实现幂等性校验。例如，使用 Redis 以及 ZooKeeper 来实现，运行效率比数据库更佳。

5.3. 解决死锁

当出现死锁以后，有两种策略：

一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过 50s 才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

但是，我们又不可能直接把这个时间设置成一个很小的值，比如 1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。

所以，正常情况下我们还是要采用第二种策略，即：主动死锁检测，而且 innodb_deadlock_detect 的默认值本身就是 on。为了解决死锁问题，不同数据库实现了各自的死锁检测和超时机制。InnoDB 的处理策略是：将持有最少行级排它锁的事务进行回滚。

主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

6. 分布式事务

在单一数据节点中，事务仅限于对单一数据库资源的访问控制，称之为 本地事务。几乎所有的成熟的关系型数据库都提供了对本地事务的原生支持。

分布式事务指的是事务操作跨越多个节点，并且要求满足事务的 ACID 特性。

分布式事务的常见方案如下：

两阶段提交（2PC） - 将事务的提交过程分为两个阶段来进行处理：准备阶段和提交阶段。参与者将操作成败通知协调者，再由协调者根据所有参与者的反馈情报决定各参与者是否要提交操作还是中止操作。
三阶段提交（3PC） - 与二阶段提交不同的是，引入超时机制。同时在协调者和参与者中都引入超时机制。将二阶段的准备阶段拆分为 2 个阶段，插入了一个 preCommit 阶段，使得原先在二阶段提交中，参与者在准备之后，由于协调者发生崩溃或错误，而导致参与者处于无法知晓是否提交或者中止的“不确定状态”所产生的可能相当长的延时的问题得以解决。
补偿事务（TCC）
- Try - 操作作为一阶段，负责资源的检查和预留。
- Confirm - 操作作为二阶段提交操作，执行真正的业务。
- Cancel - 是预留资源的取消。
本地消息表 - 在事务主动发起方额外新建事务消息表，事务发起方处理业务和记录事务消息在本地事务中完成，轮询事务消息表的数据发送事务消息，事务被动方基于消息中间件消费事务消息表中的事务。
MQ 事务 - 基于 MQ 的分布式事务方案其实是对本地消息表的封装。
SAGA - Saga 事务核心思想是将长事务拆分为多个本地短事务，由 Saga 事务协调器协调，如果正常结束那就正常完成，如果某个步骤失败，则根据相反顺序一次调用补偿操作。

分布式事务方案分析：

2PC/3PC 依赖于数据库，能够很好的提供强一致性和强事务性，但相对来说延迟比较高，比较适合传统的单体应用，在同一个方法中存在跨库操作的情况，不适合高并发和高性能要求的场景。
TCC 适用于执行时间确定且较短，实时性要求高，对数据一致性要求高，比如互联网金融企业最核心的三个服务：交易、支付、账务。
本地消息表/MQ 事务都适用于事务中参与方支持操作幂等，对一致性要求不高，业务上能容忍数据不一致到一个人工检查周期，事务涉及的参与方、参与环节较少，业务上有对账/校验系统兜底。
Saga 事务由于 Saga 事务不能保证隔离性，需要在业务层控制并发，适合于业务场景事务并发操作同一资源较少的情况。 Saga 相比缺少预提交动作，导致补偿动作的实现比较麻烦，例如业务是发送短信，补偿动作则得再发送一次短信说明撤销，用户体验比较差。Saga 事务较适用于补偿动作容易处理的场景。

分布式事务详细说明、分析请参考：分布式事务基本原理

7. 事务最佳实践

高并发场景下的事务到底该如何调优？

7.1. 尽量使用低级别事务隔离

结合业务场景，尽量使用低级别事务隔离

7.2. 避免行锁升级表锁

在 InnoDB 中，行锁是通过索引实现的，如果不通过索引条件检索数据，行锁将会升级到表锁。我们知道，表锁是会严重影响到整张表的操作性能的，所以应该尽力避免。

7.3. 缩小事务范围

有时候，数据库并发访问量太大，会出现以下异常：

MySQLQueryInterruptedException: Query execution was interrupted

高并发时对一条记录进行更新的情况下，由于更新记录所在的事务还可能存在其他操作，导致一个事务比较长，当有大量请求进入时，就可能导致一些请求同时进入到事务中。

又因为锁的竞争是不公平的，当多个事务同时对一条记录进行更新时，极端情况下，一个更新操作进去排队系统后，可能会一直拿不到锁，最后因超时被系统打断踢出。

如上图中的操作，虽然都是在一个事务中，但锁的申请在不同时间，只有当其他操作都执行完，才会释放所有锁。因为扣除库存是更新操作，属于行锁，这将会影响到其他操作该数据的事务，所以我们应该尽量避免长时间地持有该锁，尽快释放该锁。又因为先新建订单和先扣除库存都不会影响业务，所以我们可以将扣除库存操作放到最后，也就是使用执行顺序 1，以此尽量减小锁的持有时间。

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

知道了这个设定，对我们使用事务有什么帮助呢？那就是，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

码农公寓

MySQL学习笔记之事务

Mysql 事务

1. 事务简介

2. 事务用法

2.1. 事务处理指令

2.2. AUTOCOMMIT

3. ACID

4. 事务隔离级别

4.1. 事务隔离简介

4.2. 未提交读

4.3. 提交读

4.4. 可重复读

4.5. 串行化

4.6. 隔离级别小结

5. 死锁

5.1. 死锁的原因

5.2. 避免死锁

5.3. 解决死锁

6. 分布式事务

7. 事务最佳实践

7.1. 尽量使用低级别事务隔离

7.2. 避免行锁升级表锁

7.3. 缩小事务范围

8. 参考资料

码农公寓

Mysql 事务

1. 事务简介

2. 事务用法

2.1. 事务处理指令

2.2. AUTOCOMMIT

3. ACID

4. 事务隔离级别

4.1. 事务隔离简介

4.2. 未提交读

4.3. 提交读

4.4. 可重复读

4.5. 串行化

4.6. 隔离级别小结

5. 死锁

5.1. 死锁的原因

5.2. 避免死锁

5.3. 解决死锁

6. 分布式事务

7. 事务最佳实践

7.1. 尽量使用低级别事务隔离

7.2. 避免行锁升级表锁

7.3. 缩小事务范围

8. 参考资料

相关文章