数据库事务

数据库事务就是一组 SQL 语句，这组 SQL 语句是一个逻辑工作单元。该单元作为一个整体永久性地修改或作为一个整体取消对数据库的修改。

定义事务的语句一般有三条：

BEGIN TRANSACTION; -- 表示事务开始
COMMIT;            -- 表示事务提交
ROLLBACK;          -- 表示事务回滚

一、 当前读和快照读

1.1 当前读

当前读读取的是数据的最新版本，要保证其他并发事务不能修改当前记录，所以会对读取的记录进行加锁。

像 select …… lock in share mode（共享锁）；select …… for update，update，insert，delete（排他锁）这些操作都会触发当前读。

1.2 快照读

像不加锁的 select …… 操作就是快照读，即不加锁的非阻塞读。

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

之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即 MVCC，可以认为 MVCC 是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。

二、MVCC（多版本并发控制）

MVCC（Multi-Version Concurrency Control），即多版本并发控制，是 MySQL 事务隔离级别中的一个重要概念。

这是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。

MVCC 在 MySQL InnoDB 中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

这仅仅只是一种理想的概念，即：维持一个数据的多个版本，使得读写操作没有冲突。

快照读就是 MySQL 为了实现 MVCC 理想模型，的其中一个具体非阻塞读功能。

2.1 MVCC 实现原理

MySQL 4.1开始支持每个 InnoDB 引擎的表单独放到一个表空间里。InnoDB 通过使用 MVCC 来获取高并发性，并且实现 SQL 标准的 4 种隔离级别，同时使用一种被称为 next-key locking 的策略来避免幻读（phantom）现象。

除此之外 InnoDB 引擎还提供了插入缓存（insert buffer）、二次写（double write）、自适应哈西索引（adaptive hash index）、预读（read ahead）等高性能技术。

2.1.1 隐式字段

• DB_TRX_ID：6byte，创建或者最后一次修改该记录的事务 ID
• DB_ROW_ID：6byte，隐藏主键【如果当前表有主键，则隐藏主键=主键；如果没有主键，则 InnoDB 会自动以 DB_ROW_ID 产生一个聚簇索引】
• DB_ROLL_PTR：7byte，回滚指针【如果是新插入的数据，回滚指针为null】

例如：insert 一条数据

name	age	gender	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID
李四	12	man	1	1	null
对用户可见			对用户不可见

2.1.2 undolog（回滚日志）

同一条数据，多次修改会存在多个历史版本，保存在 undolog 中，形成一个线性表（链表）。其中链首存储的是最新的历史记录，链尾是最早的历史记录。

数据表中有多条数据，多条数据发生了多次修改，那么则会存在多个链表。

UPDATE table_a SET name = "李四";

name	age	gender	DB_TRX_ID	DB_ROLL_PTR	DB_ROW_ID
李四	12	man	2	1	0x123
对用户可见			对用户不可见

这时旧数据会保存到 undolog 中，新数据的【回滚指针（DB_ROLL_PTR）】指向 undolog 中旧数据的【回滚指针（DB_ROLL_PTR）】

2.1.3 readview（读视图）

事务在进行快照读（select ……）的时候，会产生【readview（读视图）】

事务在执行快照读的那一刻，会生成数据库系统当前的一个快照。记录并维护系统当前活跃事务的ID（当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大）

• trx_list：系统活跃的事务 ID
• up_limit_id：列表中事务最小的 ID
• low_limit_id：系统尚未分配的下一个事务 ID

当一个事务在进行快照读时，能否读取到其他事务刚刚 update 并 commit 的记录？

1. 首先判断 DB_TRX_ID 和 update_limit_id 的关系。如果 DB_TRX_ID < update_limit_id，则当前事务能看到 DB_TRX_ID 所 commit 的记录；否则进入下个判断
2. 接着判断 DB_TRX_ID 和 low_limit_id 的关系。如果 DB_TRX_ID >= low_limit_id，则代表 DB_TRX_ID 所 commit 的记录是在生成 readview 之后，对于当前事务肯定是不可见的；反之进入下个判断
3. 最后判断 DB_TRX_ID 和 trx_list 的关系。如果 DB_TRX_ID 存在于 【活跃事务（trx_list）】中，则代表在生成 readview 的时候，DB_TRX_ID 还是处在活跃状态，还没有 commit，因此 DB_TRX_ID update 的数据在当前事务是看不到的；如果不在，则说明这个事务在 readview 生成之前就已经 commit 了，那么DB_TRX_ID update 的结果是能够看见的

举个例子

事务1	事务2	事务3	事务4
begin（开启）	begin（开启）	begin（开启）	begin（开启）
			update commit
	select ……（快照读）

此时事务2能不能读取到事务4 commit 的记录？

分析：事务2 select ……（快照读）产生的 readview

low_limit_id	1，2，3（因为4已经 commit，因此不是活跃事务）
up_limit_id	1
low_limit_id	5

DB_TRX_ID

4

1. 4 > 1（DB_TRX_ID > update_limit_id），进入下个判断
2. 4 < 5（DB_TRX_ID < low_limit_id），进入下个判断
3. 4 不存在于 trx_list 中，事务2 select ……（快照读）能够看到事务4 commit 的记录。

实际测试：

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.01 sec)

窗口1	窗口2
mysql> begin; Query OK, 0 rows affected (0.00 sec)	mysql> begin; Query OK, 0 rows affected (0.00 sec)
mysql> select * from tt17; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.00 sec)	mysql> select * from tt17; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.00 sec)
	mysql> update tt17 set name = "zhangsan"; Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> commit; Query OK, 0 rows affected (0.04 sec)
mysql> select * from tt17; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.00 sec)	mysql> select * from tt17; +----+------+------+ | id | name | age | +----+------+------+ | 1 | 1 | 1 | | 2 | 2 | 2 | | 3 | 3 | 3 | +----+------+------+ 3 rows in set (0.00 sec)

此时，在实际测试中，事务2 select ……（快照读）看不到事务4 commit 的记录，与分析结果相反！(╬￣皿￣)

继续冷静分析：

mysql> select @@transaction_isolation;
ERROR 1193 (HY000): Unknown system variable 'transaction_isolation'
-- 这里可能会遇到 mysql-connector-java 的 jar 包版本高，而 mysql 版本低的情况。低版本的 mysql 只识别 tx_isolation，不识别 transaction_isolation
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

可知当前数据的隔离级别为默认的可重复读（RR）。

因为可重复读（RR）和读已提交（RC）生成 readview 的时机是不同的。

• 读已提交（RC）每次在进行 select ……（快照读）的时候都会生成新的 readview；
• 可重复读（RR）只有在第一次进行 select ……（快照读）的时候才会生成 readview，之后的读操作都只会用第一次生成的 readview

在实际测试时，是先进行了一次 select ……（快照读），生成了 readview，与分析案例有出入，此时事务4仍然在 trx_list 中，在第三步判断时，commit 发生于生成快照读之后，因此看不到是正常现象。

总结：

• 隔离级别为读已提交（RC）的时候，事务2 select ……（快照读）可以看到事务4 commit 的记录；
• 隔离级别为可重复读（RR）的时候，事务2 select ……（快照读）看不到事务4 commit 的记录

三、事务的 ACID 特性

3.1 A【Atomicity（原子性）】

一个事务中所包含的全部 SQL 语句是一个执行整体，不可分割，要么全执行，要么全取消。

通过【回滚日志（undolog）】来实现

3.2 C【Consistency（一致性）】

即数据库在事务操作前和事务处理后，其中的数据必须都要满足业务规范约束。如果在事务中出现错误，那么系统中的所有变化将自动地回滚，系统返回到原始状态。

（例如：银行转账，应先减后加，如果减后断电没有加上，不满足业务规范的约束，事务就要回滚，转账取消）

依赖于其他三个特性来实现

3.3 I【 Isolation（隔离性）】

指的是多个事务并发地独立运行，而不能互相干扰，事务提交时根据当前数据库状态进行操作。

通过【MVCC】来实现

3.3.1 四种隔离级别

事务的四种隔离级别

3.3.2 RR 和 RC

MVCC 只在 RR 和 RC 两个隔离级别下工作。

1. 在 RR 隔离级别下，【事务A】对某些记录的第一次查询会生成一个【快照（readview）】，此后【事务A】对这些记录的查询会继续使用这个【快照（readview）】。这会导致在生成【快照（readview）】之后，其他事务对这些记录的修改对【事务A】不可见；
2. 在 RC 隔离级别下，【事务B】对某些记录的第一次查询会生成一个【快照（readview）】，但是【事务B】之后对这些记录的查询都会重新生成一个【快照（readview）】。也就是说，其他事务对这些记录的修改对【事务B】可见；
3. RC 的性能比 RR 好，因为 RC 允许【不可重复读】和【幻读】。

3.3.3 怎么处理幻读

处理幻读有两种方案：

• 将隔离级别提高到 Serializable 级别
• 加 Next-Key Lock 锁

一般不会将数据库的隔离级别设置为 Serializable，加 Next-Key Lock 锁是比较常用的方法。

InnoDB 有三种锁算法：

• 记录锁（Record Lock）：单个行记录加锁
• 间隙锁（Gap Lock）：锁定一个范围，但不含记录本身
• 临键锁（Next-Key Lock）：Record Lock + Gap Lock

table_a 表中有如下数据：

这里可知有：

• 间隙锁：（-∞，1）
• 记录锁：1
• 间隙锁：（1，10）
• 记录锁：10
• 间隙锁：（10，30）
• 记录锁：30
• 间隙锁：（30，+∞）

-- uid列为主键
SELECT * FROM table_a WHERE uid = 10;
-- 主键uid=10记录存在，只对id=10的记录加Record Lock
SELECT * FROM table_a WHERE uid = 8;
-- 主键uid=8记录不存在，加间隙锁Gap Lock：（1, 10）
SELECT * FROM table_a WHERE uid > 12;
-- 主键使用范围查询，上锁范围为：（10,30）∪30∪（30，+∞）

-- age列为索引列
SELECT * FROM table_a WHERE age = 30;
-- 条件过滤对应记录存在，除了会对主键uid=10的记录加Record Lock，
-- 还会对普通索引字段加锁：[10，50]
SELECT * FROM table_a WHERE age = 40;
-- 条件过滤对应记录不存在，只会对普通索引字段加锁：(30，50]
SELECT * FROM table_a WHERE age > 40;
-- 普通索引使用范围查询，上锁范围为：（30，+∞）

SELECT * FROM table_a WHERE name = 'AAA';
-- name列没有索引，则会进行全表扫描，所有记录都会加上临键锁，相当于锁表

3.4 D【Durability（持久性）】

也称为永久性，指的是事务在处理结束后，对数据库的修改是永久性的，即使系统故障也不会丢失。

通过【redolog】来实现

3.4.1 数据更新流程

1. 执行器先从 InnoDB 引擎中找到数据，如果在 Buffer Pool 内存中则直接返回；如果不在则【随机读】查询磁盘文件后返回
2. 执行器拿到数据之后会先修改数据，然后调用 InnoDB 引擎接口重新写入数据
3. InnoDB 引擎将数据更新到内存，同时【顺序写】数据到 redolog 中，此时 redolog 状态为 prepare
4. 执行器生成这个操作的 binlog
5. 执行器调用 InnoDB 引擎的事务提交接口，InnoDB 引擎把刚刚写完的 redolog 状态改为 commit，数据更新完成

3.4.2 【随机读写】和【顺序读写】

因为，【顺序读写】的效率远远高于【随机读写】。

所以，为了保证数据的一致性，会先将数据的更新结果【顺序写】入到 redolog 日志文件中，然后再将数据写入到对应的磁盘文件中【WAL（write ahead log）机制：先写日志，再写数据】。这样的设计，其效率优于直接随机读写磁盘文件。

由此可知，如果发生异常情况，导致实际数据未能写入到磁盘，只要日志文件保存成功了，那么数据就不会丢失，可以根据日志来进行数据恢复。

3.4.3 数据恢复分析

• redolog 处于 prepare 状态，binlog 未写入，干掉 redolog 的 prepare 状态，数据更新操作丢失、回滚；
• redolog 处于 prepare 状态，binlog 已写入，redolog 的 prepare 状态改为 commit，更新数据提交

四、不能用于事务的操作

• 创建数据库（create database）
• 修改数据库（alter database）
• 删除数据库（drop database）
• 恢复数据库（restore database）
• 加载数据库（load database）
• 备份日志文件（backup log）
• 恢复日志文件（restore log）
• 更新统计数据（update statitics）
• 授权操作（grant）
• 复制事务日志（dump tran）
• 磁盘初始化（disk init）
• 更新使用sp_configure后的系统配置（reconfigure）