MySQL详解三

事务

在谈 MySQL 事务之前我们需要达成一个共识，就是事务触发的前提条件是多条连接并发的的访问，对于单条连接我们并不会去讨论事务这个东西，因为对于单条 MySQL 连接来说，他是一个串行化的操作，串行化的操作没有必要讨论事务，但是对于多条连接来说，每条连接都会存在多个 SQL 语句，那么就会不可避免的出现时序问题，就需要谈到事务这个特性。

首先我们要去理解事务是什么东西，事务的本质是并发控制的单元，是用户定义的一个操作序列，这些操作要么都做，要么都不做，是一个不可分割的工作单位。

就像我们之前谈到的原子操作一样，i++操作如果没有进程加锁或者是他不是一个原子变量，就会出现线程安全的问题，最终就会导致数据的错乱，而 MySQL 中的事务也是如此，多条 SQL 语句执行的过程中，如果某一条语句执行错误，就需要回滚到事务最开始的状态，也就是说，我们要么看到的是事务没有被执行的状态，要么就是看到事务已经被执行以后的状态，并不会去看到对应的中间态，这就是事务。

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

事务控制语句

MySQL 的事务控制语句由以下几条构成：

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

ACID特性

原子性

一个事务中的所有操作，要么是已经执行完毕，要么就是还没有开始执行，他不会在事务执行的过程中被打断，如果一个事务在执行过程中出现了异常，就会回到事务最开始的执行状态，就像这个事务什么都没有发生过一样，这就是事务的原子性。

事务的回滚操作是通过 undolog 来进行实现的， undolog 是事务的回滚日志，它记录的是事务每步具体操作，当回滚时，回放事务具体操作的逆运算。

一致性

事务的一致性我们需要从两个方面去进行探讨，首先数据库完整约束，也就是我们前面所说的设置主键约束，唯一约束等等，只要我们进行设置了，数据库就会为我们做出一个保证，这是数据库所去决定的数据库的完整约束。

也就是说，事务的前后，所有的数据都保持一个一致的状态，不能违反数据的一致性检测，在事务执行前后，数据库完整性约束没有被破坏，（例如：一个表的姓名是唯一键，如果一个事务对姓名进行修改，但是在事务提交或事务回滚后，表中的姓名变得不唯一了，这样就破坏了一致性。）

另一方面就是逻辑上的一致性，逻辑上的一致性是用户进行定义的，用户认为当前的多条 SQL 语句是一个整体，这个事务单元需要提交之后才会被其他事务可见。

隔离性

隔离性所探讨的就是各个事务之间的影响程度，因为数据库是支持多个事务对数据并发的进行读写和修改的能力的，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致，不同的隔离性就对应着不同的现象，事务隔离分为不同级别，包括读未提交（ Read uncommitted ）、读提交（ read committed ）、可重复读（ repeatable read ）和串行化（ Serializable ），隔离性是通过MVCC和锁进行实现的，我们后续会介绍到。

持久性

事务一旦完成，要将数据所做的变更记录下来，包括数据存储和多副本的网络备份。

事务提交后，事务 DML 操作将会持久化，这是通过 Redlog 进行实现的， Redlog 是重做日志，用于MySQL崩溃后进行数据恢复，保证数据的持久性。

事务的隔离级别

在了解事务的隔离级别之前，我们需要知道一些跟隔离级别相关的 SQL 语句：

-- 设置隔离级别
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 AUTO_INCREMENT,`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;INSERT INTO `account_t` VALUES (7,'M',1000), (1, 'C', 1000),(2, 'B', 1000),(3, 'A', 1000);

读未提交(Read Uncommitted)

读未提交(Read Uncommitted)：在该隔离级别，所有的事务都可以看到其他事务没有提交的执行结果。（实际生产中不可能使用这种隔离级别的），但是相当于没有任何隔离性，也会有很多并发问题，如脏读，幻读，不可重复读等。

我们开启两个 MySQL 客户端来进行操作：

我们会发现，当前 A 客户端的事务并没有 commit，B 客户端已经读到了 A 客户端进行的修改，这就是脏读现象，一个事务读到了另一个事务还没有提交的结果，也就是中间执行的结果。

那么当前的解决办法就是提高我们的隔离级别为读已提交（Read Committed）。

读已提交（Read Committed）

读已提交（Read Committed） ：该隔离级别是大多数数据库的默认的隔离级别（不是 MySQL 默认的）。它满足了隔离的简单定义:一个事务只能看到其他的已经提交的事务所做的改变。这种隔离级别会引起不可重复读，即一个事务执行时，如果多次 select， 可能得到不同的结果；

我们可以看见，在当前隔离级别下，脏读的问题已经解决掉，但是这个隔离级别会产生不可重复读的现象发生。

我们可以看见，B 客户端在一个事务中进行两次读取，读取到了不同的结果，这种现象就叫做不可重复读。

解决掉不可重复读的方法就是将隔离级别提高到可重复读（Repeatable Read）。

可重复读（Repeatable Read）

可重复读（Repeatable Read）：这是 MySQL 默认的隔离级别，它确保同一个事务，在执行中，多次读取操作数据时，会看到同样的数据行，但是会有幻读问题。

我们可以看见，在当前隔离级别下，A 客户端的事务已经提交，B 客户端事务在事务未提交之前，两次读取到的结果是一样的，就解决掉了不可重复读的问题，但是他又会造成幻读的问题，当前读与快照读不一致，我们来看下面：

我们当前在 A 客户端插入了一条记录，然后 commit 当前事务，对于 B 客户端来说，我在 A commit 之前读取到的 id > 3的数据是只有 id = 7 这一个的，那么 B 客户端就觉得自己可以插入数据，但是此时 A 已经插入并提交了，B 在进行插入的时候就会报错，违反了主键约束，这种两次读取同一个范围内的记录得到的结果集不一样，当前读与快照读不一致称之为幻读。

解决幻读我们可以考虑使用加锁操作，我们来看下面：

这种方式 A 客户端已经执行可 insert 语句了，此时 B 客户端去查询的时候通过 for update 进行加锁，就会等待，等到 A 客户端 commit 以后，此时 B 客户端才可以查询到对应的数据，这就很好的解决了幻读的问题。

另一种加锁操作就是 B 客户端先进行加锁，然后 A 客户端再去添加数据，此时 A 客户端就会等待，然后等 B 客户端 commit 以后会报错，这本身也是不违反逻辑的。

串行化（serializable）

这是事务的最高隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决了幻读的问题。它在每个读的数据行上面加上共享锁，但是可能会导致超时和锁竞争，一般很少进行使用。

MVCC

聚集索引的隐藏列

首先我们要了解，聚集索引当中是存在隐藏列的，他们分别是：

• trx_id：事务修改记录时，trx_id 记录该事务修改 id；
• roll_pointer：事务修改记录时，将旧记录写入到 undolog 中，roll_pointer 指向旧版本的记录。

比如说当前有一个学生表，包含年龄跟姓名，我们在表中插入一条记录，作为事务1，他的字段就应该是这样的：
此时我们需要去执行事务 2，修改张三的名字为李四，那么现在当前事务的 trx_id = 2，roll_pointer 就会指向原来事务 1 的那张表的记录；

然后又有一个事务 3 ，此时要修改李四的 age 为 38，此时版本链就应该如下：

我们所说的回滚，其实就是根据这个事务的操作，然后根据版本链，去寻找之前的一些记录，将对应的数据进行回恢复即可。

read view

在 read committed 和 read repeatable 隔离级别下，MVCC 采用 read view 来实现的，它们的区别在于创建 read view 时机不同：

• read committed 隔离级别会在事务中每个 select 都会生成一个新的 read view，也意味着在同一个事务多次读取同一条数据可能出现数据不一致，因为在多次读取期间可能有其他事务修改了该条记录，并提交了；
  
• read repeatable 隔离级别是启动事务时生成一个 read view，在整个事务读取数据都使用这个 read view，这样保证了在事务期间读到的数据都是事务启动前的记录；
  

当前读 VS 快照读

• 当前读：读取最新的记录，就叫做当前读。
• 快照读：读取历史版本，就叫做快照读

事务可见性问题

Read View 在 MySQL 源码中就是一个类，本质是用来进行可见性判断的，当事务对某个记录执行快照读的时候，对该记录创建一个Read View，根据这个 Read View 来判断，当前事务能够看到该记录的哪个版本的数据。

构成：

• m_ids：创建 read view 的时候，已启动但未提交的事务 id 列表；
• min_trx_id：创建 read view 的时候，已启动但未提交的事务最小事务 id；
• max_trx_id：创建 read view 的时候，预分配给下一个未开始事务分配的 id；
• creator_trx_id：创建该 read view 的 id。

我们需要知道的是，同一个事务是可以看到自身的修改的。

根据 min_trx_id 和 max_trx_id 可以将事务 id 划分为三部分，就可以总结为下面这个图结构：

RR与RC本质区别

• 正是Read View生成时机的不同，从而造成RC，RR级别下快照读的结果的不同；
• 在RR级别下的某个事务的对某条记录的第一次快照读会创建一个快照及Read View，将当前系统活跃的其他事务记录起来；
• 此后在调用快照读的时候，还是使用的是同一个Read View，所以只要当前事务在其他事务提交更新之前使用过快照读，那么之后的快照读使用的都是同一个Read View，所以对之后的修改不可见；
• 即RR级别下，快照读生成Read View时，Read View会记录此时所有其他活动事务的快照，这些事务的修改对于当前事务都是不可见的。而早于Read View创建的事务所做的修改均是可见；
• 而在RC级别下的，事务中，每次快照读都会新生成一个快照和Read View，这就是我们在RC级别下的事务中可以看到别的事务提交的更新的原因；
• 总之在RC隔离级别下，是每个快照读都会生成并获取最新的Read View；而在RR隔离级别下，则是同一个事务中的第一个快照读才会创建Read View, 之后的快照读获取的都是同一个Read View；
• 正是RC每次快照读，都会形成Read View，所以，RC才会有不可重复读问题。

锁

在 Mysql 中，通常情况下读操作使用 MVCC 来解决并发问题，那么写操作全部都是要进行加锁操作的，接下来我们就来看几种锁操作：

全局锁

全局锁通常用于全库备份，让整个数据库处于只读状态。

# 加锁
flush tables with read lock
# 解锁
unlock tables

表级锁

MyISAM 默认就是使用的表级锁，主要有以下几种类型：

行级锁

InnoDB 采用的就是行级锁，可以分为以下几类：

接下来我们来看一下一张表，更加熟悉一下如何加锁的，加的什么锁：

聚集索引，查询命中：update students set score = 100 where id = 15;

聚集索引，查询未命中：update students set score = 100 where id = 16;

辅助唯一索引，查询命中： UPDATE students SET score = 100 WHERE no = ‘S0003’;

辅助唯一索引，查询未命中： UPDATE students SET score = 100 WHERE no = ‘S0008’;

辅助非唯一索引，查询命中： UPDATE students SET score = 100 WHERE name = ‘Tom’;

辅助非唯一索引，查询未命中： UPDATE students SET score = 100 WHERE name = ‘John’;

无索引： UPDATE students SET score = 100 WHERE score = 22;

辅助索引，范围查询： UPDATE students SET score = 100 WHERE age <= 23;

修改索引值： UPDATE students SET name = ‘John’ WHERE id = 15;

死锁

造成死锁其实就是一个加锁顺序的问题，如下：