《高性能 MySQL》笔记——第1章 MySQL架构与历史（一）_并发控制:服务器层与存储引擎层-CSDN博客

本文链接：https://blog.csdn.net/bm1998/article/details/107007188

本文详细介绍了MySQL的逻辑架构，包括连接管理、优化与执行等核心服务功能。此外，深入探讨了并发控制，如读写锁、锁粒度、表锁和行级锁，以及事务的四大特性（ACID）和隔离级别。强调了不同隔离级别对并发读写的影响，以及在实际应用中如何选择适合的事务隔离策略。

摘要由CSDN通过智能技术生成

声明：

本博客是本人在学习《高性能 MySQL》后整理的笔记，旨在方便复习和回顾，并非用作商业用途。

本博客已标明出处，如有侵权请告知，马上删除。

1.1 MySQL 逻辑架构

下图展示了 MySQL 的逻辑架构图。

在这里插入图片描述

最上层的服务并不是 MySQL 所独有的，大多数基于网络的客户端/服务器的工具或者服务都有类似的架构。比如连接处理、授权认证、安全等等。
第二层构架是 MySQL 比较有意思的部分。大多数 MySQL 的核心服务功能都在这一层，包括查询解析、分析、优化、缓存以及所有的内置函数（例如：日期、时间、数学和加密函数），所有跨存储引擎的功能都在这一层实现：存储过程、触发器、视图等。
第三层包含了存储引擎。存储引擎负责 MySQL 中数据的存储和提取。和 GNU/Linux 下的各种文件系统一样，每个存储引擎都有它的优势和劣势。服务器通过 API 与存储引擎进行通信。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。存储引擎 API 包含了几十个底层函数，用于执行诸如 “开始一个事务” 或者 “根据主键提取一行记录” 等操作。但存储引擎不会去解析 SQL (InnoDB 是一个例外，它会解析外键定义，因为 MySQL 服务器本身没有实现该功能)，不同存储引擎之间也不会互相通信，而只是简单地响应上层服务器的请求。

1.1.1 连接管理与安全性

每个客户端连接都会在服务器进程中拥有一个线程，这个连接的查询只会在这个单独的线程中执行，该线程只能轮流在某个 CPU 核心或者 CPU 中运行。服务器会负责缓存线程，因此不需要为每一个新建的连接创建或者销毁线程。

当客户端（应用）连接到 MySQL 服务器时，服务器需要对其进行认证。认证基于用户名，原始主机信息和密码。如果使用了安全套接字（SSL）的方式连接，还可以使用 X.509 证书认证。一旦客户端连接成功，服务器会继续验证客户端是否具有某个特定查询的权限（例如，是否允许客户端对 world 数据库的 Country 表执行 SELECT 语句）。

1.1.2 优化与执行

MySQL 会解析查询，并创建内部数据结构（解析树），然后对其进行各种优化，包括重写查询，决定表的读取顺序，以及选择合适的索引等。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和 schema，修改相关配置，是应用尽可能高效运行。

优化器并不关心使用的是什么存储引擎，但存储引擎对于优化查询是有影响的。优化器会请求存储引擎提供容量或某个具体操作的开销信息，以及表数据的统计信息等。例如，某些存储引擎的某种索引，可能对一些特定的查询有优化。

对于 SELECT 语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），如果能够在其中找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而是直接返回查询缓存中的结果集。

1.2 并发控制

无论何时，只有有多个查询需要在同一时刻修改数据，都会产生并发控制的问题。这里讨论 MySQL 在两个层面的并发控制：服务器层与存储引擎层。并发控制是一个内容庞大的话题，有大量的理论文献对其进行详细的论述。这里只是简要地讨论 MySQL 如何控制并发读写，因此读者需要有相关的知识来理解本章的内容。

以 Unix 系统的 email box 为例子，典型的 mbox 文件格式是非常简单的。一个 mbox 邮箱中的所有邮件都串行在一起，彼此首尾相连。这种格式对于读取和分析邮件信息非常友好，同时投递邮件也很容易，只要在文件末尾附加新的邮件内容即可。

但是如果两个进程在同一时刻对同一个邮箱投递邮件，会发生什么情况？显然，邮箱的数据会被破坏，两封邮件的内容会交叉地附加在邮箱文件的末尾。设计良好的邮箱投递系统会通过锁（lock）来防止数据损坏。如果客户试图投递邮件，而邮箱已经被其他客户锁住，那么就必须等待，直到锁释放才能进行投递。

这种锁的方案在实际应用环境中虽然工作良好，但是并不支持并发处理。因为在任意一个时刻，只有一个进程可以修改邮箱的数据，这在大容量的邮箱系统中是个问题。

1.2.1 读写锁

从邮箱中读取数据没有这样的麻烦，即使同一时刻多个用户并发读取也不会有什么问题。因为读取不会修改数据，所以不会出错。但是如果某个客户正在读取邮箱，同时另一个用户试图删除编号为 25 的邮件，会产生什么结果？结论是不确定的，读的客户可能会报错退出，也可能读取不到一致的邮箱数据。所以，为了安全起见，即使是读取邮箱也需要特别注意。

如果把上述的邮箱当成数据库中的一张表，把邮件当成表中的一行记录，就很容易看出，同样的问题依然存在。从很多方面来说，邮箱就是一张简单的数据库表。修改数据库表中的记录，和删除或者修改邮箱中的邮件信息，十分类似。

解决这类经典的问题的方法就是并发控制（读锁和写锁），其实非常简单。在处理并发读或者写的时候，可以通过实现一个由两种类型的锁组成锁系统来解决问题。这两种类型的锁通常被称为共享锁（shared lock）和排他锁（exclusive lock），也叫读锁（read lock）和写锁（write lock）。

这里先不讨论如何具体实现，描述一下锁的概念如下：读锁是共享的，或者说是相互不阻塞的。多个客户在同一时刻可以同时读取同一资源，而互不干扰。写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，这是出于安全策略的考虑，只有这样，才能确保给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据的时候，MySQL 会通过锁定防止其他用户读取同一数据。大多数时候，MySQL 锁的内部管理都是透明的。

1.2.2 锁粒度

一种提供共享资源并发性的方式就是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片（具体到锁定所修改的字段）进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要互相之间不发生冲突即可。

问题是加锁也需要消耗资源。锁的各种操作，包括获得锁，检查锁是否已经被解除，释放锁等，都会增加系统的开销。如果系统花费大量的时间来管理锁，而不是存取数据，那么系统的性能可能因此受到影响。

所谓的锁策略，就是在锁的开销和数据的安全性之间寻求平衡，这种平衡当然有会影响到性能。大多数商业数据库系统没有提供更多的选择，一般都是在表上施加行级锁（row-level lock），并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能地提供更好的性能。

而 MySQL 则提供多种选择，每种 MySQL 存储引擎都可以实现自己的锁策略和锁粒度。在存储引擎的设计中，锁管理是个非常重要的决定。将锁粒度固定在某个级别，可以为某些特定的应用场景提供更好的性能。但是同时却会失去对另外一些应用场景的良好支持。好在 MySQL 支持多个存储引擎的架构，所以不需要单一的通用解决方案。下面介绍两种最重要的锁策略。

1.2.2.1 表锁（table lock）

表锁是 MySQL 中最基本的锁策略，并且是开销最小的策略。表锁非常类似于前文描述的邮箱加锁机制：它会锁定整张表。一个用户在对表进行写操作（插入，删除，更新等等）前，需要先获得写锁，这个会阻塞其他用户对该表的所有读写操作。只有没有写锁的时候，其他读取的用户才能获得读锁，读锁之间是不互相阻塞的。

在特定的场景中，表锁也可能有良好的性能。例如，read local 表锁支持某些类型的并发写操作。另外，写锁也比读锁有更高的优先级，因此一个写锁请求可能会被插入到读锁队列的前面（写锁可以插入到锁队列中读锁的前面，反之读锁则不能插入写锁的前面）。

尽管存储引擎可以管理自己的锁，MySQL 本身还是会使用各种有效的表锁来实现不同的目的。例如服务器会诸如 ALTER TABLE 之类的语句使用表锁，而忽略存储引擎的锁机制。

1.2.2.2 行级锁（row lock）

行级锁可以最大程度地支持并发处理（同时也带来了最大的锁开销）。众多周知，在 InnoDB 和 XtraDB，以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而 MySQL 服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

1.3 事务

事务是一组原子性的 SQL 查询，或者说是一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中任何一条语句因为崩溃或其他原因无法执行，那么所有语句都不会执行。也就是说，事务内的语句，要么全部执行成功，要么全部执行失败。

银行应用是解释事务必要性的一个经典例子。假设一个银行的数据库有两张表：支票（checking）表和储存（savings）表。现在要从用户 Jane 的支票账户转移 200 美元到她的储存账户，那么至少需要三个步骤：

检查支票账户的余额高于 200 美元。
从支票账户余额中减去 200 美元。
在储存账户余额中增加 200 美元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

可以用 START TRANSACTION 语句开始一个事务，然后要么使用 COMMIT 提交将修改的数据持久保存，要么使用 ROLLBACK 撤销所有的修改。事务 SQL 的样本如下：

START TRANSACTION;
SELECT balance From checking WHERE custom_id = 10233276;
UPDATE checking SET balance = balance - 200.00 WHERE custom_id = 10233276;
UPDATE savings SET balance = balance + 200.00 WHERE custom_id = 10233276;
COMMIT;

单纯的事务概念并不是故事的全部。试想一下，如果执行到第四条语句时服务器崩溃了，会发生什么？天知道，用户可能会损失 200 美元。再假如，在执行到第三条语句和第四条语句之间时，另外一个进程要删除支票账户的所有余额，那么结果可能就是银行在不知道这个逻辑的情况下白白给了 Jane 200 美元。

除非系统通过严格的 ACID 测试，否则空谈事务的概念是不够的。

1.3.1 事务的四大特性（ACID）

ACID 表示原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。一个运行良好的事务处理系统，必须具备这些标准特性。

原子性：一个事物必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事物来说，不可能只执行其中的一个部分操作，这就是事务的原子性。
一致性：数据库总是从一个一致性状态转化到另一个一致性状态。在前面的例子中，一致性确保了，即使在执行第三、第四语句之间时系统崩溃，支票账户中也不会损失 200 美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。
隔离性：通常来说，一个事物所做的修改在最终提交以前，对其他事务是不可见的。在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外一个账户汇总程序开始执行，则其看到的支票账户的余额并没有被减去 200 美元。后面我们讨论隔离级别（Isolation level）的时候，会发现为什么我们要说 “通常来说” 是不可见的。
持久性：一旦事务提交，则其所作的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。持久性是个有点模糊的概念，因为实际上持久性也分很多不同的级别。有些持久性策略能够提供非常强的安全保障，而有些则未必。而且不可能有能做到 100% 的持久性保证的策略。

1.3.2 隔离级别

在 SQL 标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些是在事务内和事务间可见的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

下面简单地介绍一下四种隔离级别。

1.3.2.1 READ UNCOMMITTED（读未提交）

在 READ UNCOMMITED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也成为脏读（Dirty Read）。这个级别会导致很多问题，从性能上说 READ UNCOMMITED 不会比其他的级别好太多，但却缺乏其他级别的很多好处，除非有非常必要的理由，在实际的应用中一般很少使用。

实例演示

创建数据库，执行下面语句

DROP TABLE IF EXISTS `account`;
CREATE TABLE `account` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `balance` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `account` VALUES ('1', 'ZhangSan', '1000');
INSERT INTO `account` VALUES ('2', 'LiSi', '1500');
INSERT INTO `account` VALUES ('3', 'WangWu', '2000');

打开客户端 A，设置当前事务模式为 READ UNCOMMITED（读未提交）级别，开启事务，但不进行提交

-- 客户端 A
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

打开客户端 B，设置当前事务模式为 READ UNCOMMITED（读未提交）级别，开启事务，更新表 account，将 id 为 1 的用户余额减去 500，但不进行提交

-- 客户端 B
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

mysql> update account set balance = balance - 500 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

回到客户端 A，查询 account 表的数据，可以看到，尽管 B 的事务未提交，但客户端 A 已经能看到客户端 B 修改的数据了

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

此时，如果客户端 B 回滚，让 account 恢复到修改前的状态

-- 客户端 B
mysql> rollback;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

客户端 A 发现 id 为 1 的用户余额又变为了 1000

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

总结：上面的例子就是客户端 A 读到了客户端 B 未提交的数据，所以客户端 B 回滚后，客户 A 再次读取到的数据和之前的不一致。

1.3.2.2 READ COMMITED（读已提交）

大多数数据库系统的默认隔离级别都是 READ COMMITED（但 MySQL 不是）。READ COMMITED 满足前面提到的隔离性的简单定义：一个事务开始时，只能看见已经提交的事务所做的修改。换句话说，一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonerepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。

实例演示

打开客户端 A，设置当前事务模式为 READ COMMITED（读已提交）级别，开启事务，但不进行提交

-- 客户端 A
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

打开客户端 B，设置当前事务模式为 READ COMMITED（读已提交）级别，开启事务，更新表 account，将 id 为 1 的用户余额减去 500，但不进行提交

-- 客户端 B
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

mysql> update account set balance = balance - 500 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

回到客户端 A，查询 account 表的数据，可以看到，客户端 A 所看到的数据和之前一样，没有读到未提交的数据。

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

此时，如果客户端 B 提交了事务

-- 客户端 B
mysql> commit;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

那么客户端 A 重新读取 account 表的数据，会发现两次读取的数据不一致

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

总结：读已提交的一个弊病，就是在一个事物中，两次读取可能会读到不一样的结果。

1.3.2.3 REPEATABLE READ（可重复读）

REPEATABLE READ 解决了不可重复读问题。该级别保证了在同一个事务中多次读取同样的记录的结果是一致的。但是，理论上，可重复读隔离级别还是无法解决另一个幻读（PhantomRead）的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）。InnoDB 和 XtraDB 存储引擎通过多版并发控制（MVCC，Multivesion Concurrency Control ）解决了幻读问题。

实例演示

打开客户端 A，设置当前事务模式为 REPEATABLE READ（可重复读）级别，开启事务，但不进行提交

-- 客户端 A
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

打开客户端 B，设置当前事务模式为 REPEATABLE READ（可重复读）级别，开启事务，更新表 account，将 id 为 1 的用户余额减去 500，提交事务

-- 客户端 B
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

mysql> update account set balance = balance - 500 where id = 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

回到客户端 A 再次读取 account 表的数据，和之前的数据做对比，可以发现此时就算客户端 B 修改了 account 表的数据并提交事务，但客户端 A 在一次事物中读取的数据不变

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |    1000 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

切换到客户端 B，插入一条新数据

-- 客户端 B
mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into account(name, balance) value("Jack", 1200);
Query OK, 1 row affected

mysql> commit;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
|  5 | Jack     |    1200 |
+----+----------+---------+
4 rows in set

再回到客户端 A，查询 account 表的数据，可以看到数据还是和之前展示的一样。

-- 客户端 A
mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

这和前面说的不一样啊，不是说会产生幻读问题吗？

先来介绍两个概念：
- 快照读：读取的是快照版本，也就是历史版本。普通的 SELECT 就是快照读。
- 当前读：读取的是最新版本。SELECT … LOCK IN SHARE MODE 就是当前读。
可重复读隔离级别下，产生幻读还有一个条件，就是使用当前读，而我们上面使用的快照读。

再回到客户端 A，使用当前读查询 account 表的数据，出现了幻读。
```
-- 客户端 A
mysql> select * from account LOCK IN SHARE MODE;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
|  4 | Jack     |    1200 |
+----+----------+---------+
4 rows in set
```

1.3.2.4 SERIALIZABLE（可串行化）

SERIALIZABLE 是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读问题。简单的来说，SERIALIZABLE 会在读的每一行数据上都加上锁，所以可能导致大量的超时和锁征用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况，才可考虑用该级别。

实例演示

打开客户端 A，设置当前事务模式为 SERIALIZABLE（可传性化）级别，开启事务，但不进行提交

-- 客户端 A
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> select * from account;
+----+----------+---------+
| id | name     | balance |
+----+----------+---------+
|  1 | ZhangSan |     500 |
|  2 | LiSi     |    1500 |
|  3 | WangWu   |    2000 |
+----+----------+---------+
3 rows in set

打开客户端 B，设置当前事务模式为 SERIALIZABLE（可传性化）级别，开启事务，并插入一条记录

-- 客户端 B
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected

mysql> start transaction;
Query OK, 0 rows affected

mysql> insert into account(name, balance) value("Rose", 3000);
1205 - Lock wait timeout exceeded; try restarting transaction
terminated by user

总结：由于客户端 A 的事务先操作了 account 表，因此事务 A 会对 account 表产生表锁，事物 B 无法操作 account 表，过了事务超时时间，就会抛出异常

1.3.2.5 总结

存在问题

脏读：一个事务，读取到另一个事务中没有提交的数据。
不可重复读：在同一个事务中，两次读取到的数据不一样。
幻读：一个事务查询所有记录，另一个事务添加了一条记录，则第一个事务可以查询第二个事务的新增的记录，也就是产生幻行。

隔离级别

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
READ UNCOMMITED	YES	YES	YES	NO
READ COMMITED	NO	YES	YES	NO
REPEATABLE READ	NO	NO	YES	NO
SERIALIZABLE	NO	NO	NO	YES

注意：隔离级别从小到大安全性越来越高，但是效率越来越低。

1.3.3 死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时，也会产生死锁。多个事物同时锁定同一资源，也会产生死锁。

例如，设想下面两个事务同时处理 StockPrice 表：

-- 事务1
  START TRANSACTION;
  UPDATE StockPrice SET close = 45.50 WHERE stock_id = 4 and date = '2002-05-01';
  UPDATE StockPrice SET close = 19.80 WHERE stock_id = 3 and date = '2002-05-02';
  COMMIT;
 
-- 事务2
  START TRANSACTION;
  UPDATE StockPrice SET high = 20.12 WHERE stock_id = 3 and date = '2002-05-02';
  UPDATE StockPrice SET high = 47.20 WHERE stock_id = 4 and date = '2002-05-01';
  COMMIT;

如果凑巧，两个事务都执行了第一条 UPDATE 语句，更新了一行数据，同时也锁定了该行数据，接着每个事务都尝试去执行第二条 UPDATE 语句，却发现该行已经被对方锁定，然后两个事务都等待对方释放锁，同时又持有对方需要的锁，则陷入死循环。除非有外部因素介入才可能解除死锁。

为了解决这个问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，比如 InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方式，当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB 目前处理死锁的方法是：将持有最少行级排他锁的事务进行回滚（这是相对比较简单的死锁回滚算法）。

锁的行为和顺序是和存储引擎相关的，以同样的顺序去执行语句时，有些存储引擎会产生死锁，有些则不会。因此死锁的产生有双重原因：有些是因为真正的数据冲突，这种情况通常很难避免，但有些则是完全由于存储引擎的实现方式所导致的。

死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型的系统，这是无法避免的，所以应用程序在设计时必须如何考虑处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

1.3.4 事务日志

事务日志可以帮助提高事务的效率。使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到保存在硬盘上的事务日志中，而不是每次都将修改的数据本身写入磁盘。事务日志采用的是追加的方式，因此写日志的操作是磁盘上的一小块区域内的顺序 I/O，而不像随机 I/O 需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快的多。事务日志保存到磁盘上之后，内存中被修改的数据在后台可以慢慢地刷回磁盘。目前大多数存储引擎都是这么实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。

如果数据的修改已经记录到事务日志并持久化，但数据本身没有保存到磁盘上，此时系统崩掉了，存储引擎在重启时会自动恢复这些被修改的数据。具体的恢复方式则随着存储引擎的不同而不同。

1.3.5 MySQL 中的事务

MySQL 提供了两种事务型的存储引擎：InnoDB 和 NDB Cluster。另外还有一些第三方存储引擎也支持事务，比较知名的包括 XtraDB 和 PBXT。

1.3.5.1 自动提交（AUTOCOMMIT）

MySQL 默认采用自动提交（AUTOCOMMIT）模式。也就是说，如果不是显式地开始一个事务，则每个查询都会被当做一个事务执行提交操作。在当前连接中，可以通过设置 AUTOCOMMIT 变量来启用或禁用自动提交模式。

AUTOCOMMIT 变量来启动或禁用自动提交模式：

mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set

mysql> SET AUTOCOMMIT = 1;
Query OK, 0 rows affected

1 或者 ON 表示启用，0 或者 OFF 表示禁用。当 AUTOCOMMIT=0 时，所有的查询都是在一个是事务中，直到显式的执行 COMMIT 提交或者 ROLLBACK 回滚，该事务结束。同时又开始了另一个新事务。修改 AUTOCOMMIT 对非事务型的表，比如 MyISAM 或者内存表，不会有任何影响。对这类表来说，没有 COMMIT 或者 ROLLBACK 的概念，也可以说是相当于一直处于 AUTOCOMMIT 启用的模式。

还有一些命令，在执行之前会强制执行 COMMIT 提交当前的活动事务。在数据定义语言（DDL）中，如果是会导致大量数据改变的操作，比如 ALTER TABLE。另外还有 LOCK TABLES 等其他语句也会导致同样的效果。如果有需要，请检查对应版本的官方文档来确认所有可能导致自动提交的语句列表。

MySQL 可以通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。可以在配置文件中设置整个数据库的隔离级别，也可以只改变当前会话的隔离级别：

mysql> set session transaction isolation level read committed;

MySQL 能识别所有的 4 个 ANSI 隔离级别，InnoDB 引擎也支持所有的隔离级别。

1.3.5.2 在事务中混合使用存储引擎

MySQL服务层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务型和非事务型的表（例如 InnoDB 和 MyISAM 表）。在正常提交的情况下不会有什么问题。

但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法判定。

在非事务型的表上执行事务相关操作的时候，MySQL 通常不会发出提醒，也不会报错。有时候只有回滚的时候才会发出一个警告：“某些非事务型的表上的变更不能被回滚”。但大多数情况下，对非事务型表的操作都不会有提示。

1.3.5.3 隐式和显式锁定

InnoDB 采用的是两阶段锁定协议（two-phase locking protocol）。在事务执行的过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或 ROLLBACK 的时候才会释放，并且所有的锁是在同一时刻被释放。这些锁都是隐式锁定，InnoDB 会根据隔离级别在需要的时候自动加锁。

另外，InnoDB 也支持通过特定的语句进行显式锁定，这些语句不属于 SQL 规范：

SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

MySQL 也只支持 LOCK TABLES 和 UNLOCK TABLES 语句，这是在服务层实现的，和存储引擎无关。它们有自己的用途，并不能代替事务处理。如果应用需要用到事务，还是应该选择事务型存储引擎。

经常可以发现，应用语句将表从 MyISAM 转换到 InnoDB，但还是显式地使用 LOCK TABLES 语句，这非但没有必要，会严重影响性能，实际上 InnoDB 的行级锁工作得更好。

《高性能 MySQL》笔记——第1章 MySQL架构与历史（一）

文章目录

1.1 MySQL 逻辑架构

1.1.1 连接管理与安全性

1.1.2 优化与执行

1.2 并发控制

1.2.1 读写锁

1.2.2 锁粒度

1.2.2.1 表锁（table lock）

1.2.2.2 行级锁（row lock）

1.3 事务

1.3.1 事务的四大特性（ACID）

1.3.2 隔离级别

1.3.2.1 READ UNCOMMITTED（读未提交）

1.3.2.2 READ COMMITED（读已提交）

1.3.2.3 REPEATABLE READ（可重复读）

1.3.2.4 SERIALIZABLE（可串行化）

1.3.2.5 总结

1.3.3 死锁

1.3.4 事务日志

1.3.5 MySQL 中的事务

1.3.5.1 自动提交（AUTOCOMMIT）

1.3.5.2 在事务中混合使用存储引擎

1.3.5.3 隐式和显式锁定