（7）MySQL进阶篇SQL优化（InnoDB锁-事务隔离级别 ）
1.概述
在我们在学习InnoDB锁知识点之前，我觉得有必要让大家了解它的背景知识，因为这样才能让我们更系统地学习好它。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。
2.事务（Transaction）及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。
●原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
●一致性（Consistent）：在事务开始和结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的，数据都必须保持一致状态，以保持数据的完整性。例如A向B银行转帐，不可能A扣了钱，而B收不到钱。
●隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。隔离性有4个隔离级别：未提交读、已提交读、可重复读和可序列化。
●持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。
3.并发事务处理带来的问题
相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况：
●更新丢失（Lost Update）：事务A，B都更新同一数据，由于每个事务都不知道其他事务的存在，所以事务A修改完原始数据后，查询看到的数据有可能是事务B修改后的数据，反之亦然。如果一个事务完成并提交之前，另一个事务不能访问同一数据，则可避免此问题。
●脏读（Dirty Reads）：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
●不可重复读（Non-Repeatable Reads）：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
●幻读（Phantom Reads）：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
注意：不可重复读和幻读其实很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。
4.事务隔离级别（重点）
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本上可分为以下两种：
●一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。
●另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度来看，好象是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（MultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用程序对读一致性和事务隔离程度的要求也是不同的，比如许多应用程序对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。为了解决“隔离”与“并发”的矛盾，ISO/ANSI SQL92定义了4种事务隔离级别：
|
事务隔离级别 |
脏读 |
不可重复读 |
幻读 |
|
未提交读（Read uncommitted） |
是 |
是 |
是 |
|
已提交读（Read committed） |
否 |
是 |
是 |
|
可重复读（Repeatable read） |
否 |
否 |
是 |
|
可序列化（Serializable） |
否 |
否 |
否 |
看到表格中4种事务隔离级别特性，相信大家起初都跟我一样一头雾水，不知道如何下手，后面我通过度娘搜索相关资料了解一下，用订单表（orders）通过以下示例跟大家一一解释。
4.1未提交读（Read uncommitted）
●两个事务，一个事务未提交的数据，另一个事务可以读取到，这里读取到的数据叫做“脏数据”。
●这种隔离级别最低，这种级别一般是在理论上存在，实际应用中数据库隔离级别一般都高于该级别。
MySQL默认的事务隔离级别为可重复读（Repeatable read），输入以下命令即可知道：
-- 查看事务隔离级别模式 SHOW VARIABLES LIKE 'transaction_isolation%'; -- 5.0版本 SELECT @@tx_isolation -- 8.0版本 SELECT @@transaction_isolation
示例：
|
session_1 |
session_2 |
|
（1）将当前会话事务隔离级别修改为未提交读并查询订单金额。 |
（1）将当前会话事务隔离级别修改为未提交读并更新订单金额。 |
-- 将默认事务隔离级别修改为未提交读 |
-- 将默认事务隔离级别修改为未提交读 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) -- 开启事务 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- 更新订单金额 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
|
|
（2）session_2会话事务未提交，当前会话就可以查询到session_2已更新好的订单金额了，这就是脏数据。 |
（2）查询订单金额。 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
|
|
（3）当前会话事务因某些原因撤销。 |
-- 回滚 MySQL [(none)]> ROLLBACK; Query OK, 0 rows affected (0.00 sec) |
|
|
（3）当前会话再更新一次订单金额。 |
（4）查询订单初始金额。 |
-- 更新订单金额 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
|
（4）查询订单金额。 |
|
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
|
|
（5）当前会话更新订单金额后，本来金额应为90000才对，结果却是95000，从这就可以验证，当前会话未更新之前第二次查询到的订单金额95000就是脏数据了。 |
注意：这里需要注意一点是，修改事务隔离级别是可以设置为全局跟当前会话范围的：
SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
事务隔离级别的作用范围分为两种：
●全局级：对所有的会话有效。
●会话级：只对当前的会话有效。
所以上述示例修改当前会话事务隔离级别模式并不是全局生效的，而是只针对当前会话生效。例如session_1修改事务隔离级别为未提交读模式，并不会对session_2、session_3...会话事务生效，而当前会话结束后，下次开启新的会话时默认事务隔离级别还是可重复读模式，除非修改为全局范围。
4.2已提交读（Read committed）
●Oracle默认隔离级别。
●两个事务，数据只有一个事务提交了后，另一个事物才能读取到，即：对方事物提交之后的数据，当前事物才能读取到。
●已提交读隔离级别高于未提交读。这种级别可以避免“脏数据”，但是会导致“不可重复读取”，存在幻读问题。
示例：
|
session_1 |
session_2 |
|
（1）将当前会话事务隔离级别修改为已提交读并查询订单金额。 |
（1）将当前会话事务隔离级别修改为已提交读并更新订单金额。 |
-- 将默认事务隔离级别修改为已提交读 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) -- 开启事务 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
-- 将默认事务隔离级别修改为已提交读 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; Query OK, 0 rows affected (0.00 sec) -- 开启事务 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- 更新订单金额 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
|
|
（2）session_2会话事务未提交，当前会话查询到的订单金额并没有改变，在该模式下解决了之前存在脏数据的问题了。 |
（2）查询订单金额。 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
|
|
（3）当前会话提交事务。 |
-- 提交事务 MySQL [(none)]> COMMIT; Query OK, 0 rows affected (0.01 sec) |
|
|
（3）再次查询订单金额。 |
|
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
4.3可重复读（Repeatable read）
●MySQL默认隔离级别。
●两个事务，一个事务提交之后的数据，另一个事务读取不到。
●这种隔离级别高于读已提交，可以避免“不可重复读取”，但是会导致“幻像读”。
示例：
|
session_1 |
session_2 |
|
（1）将当前会话事务隔离级别修改为可重复读并查询订单金额。 |
（1）将当前会话事务隔离级别修改为可重复读并更新订单金额。 |
-- 将默认事务隔离级别修改为可重复读 |
-- 将默认事务隔离级别修改为可重复读
|
|
（2）session_2会话事务未提交，当前会话查询到的订单金额并没有改变，在该模式下也解决了脏数据的问题了。 |
（2）查询订单金额。 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
|
|
（3）当前会话提交事务。 |
-- 提交事务 MySQL [(none)]> COMMIT; Query OK, 0 rows affected (0.00 sec) |
|
|
（3）查询订单金额，三次查询数据结果都一致，没有出现不可重读的情况。之所以数据的一致性得到保证，原因是：在可重复读的隔离级别下，MySQL采用的是MVCC机制（不了解的可以点击上文链接了解下），select操作不会更新版本号，是快照读（历史版本）。 |
（4）查询订单金额。 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 95000.000000 | +--------------+ 1 row in set (0.00 sec) |
|
（4）在当前会话再更新订单金额。 |
|
-- 更新订单金额 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
|
|
（5）查询订单金额，明明只减5000金额，订单金额却变成90000（产生幻读），原因是：在可重复读的隔离级别下，MySQL采用的是MVCC机制，insert、update和delete会更新版本号，是当前读（当前版本）。 |
（5）尝试再更新订单金额，会提示失败，这说明可重复读隔离级别下MySQL已经不出现幻读的情况了。 |
-- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +--------------+ | OrderAmount | +--------------+ | 90000.000000 | +--------------+ 1 row in set (0.00 sec) |
-- 更新订单金额 MySQL [(none)]> UPDATE goods.orders SET OrderAmount=OrderAmount-5000 WHERE ID=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|
4.4可序列化（Serializable）
Serializable完全串行化的读，每次读都需要获得表级共享锁，读写操作相互互斥，这样可以更好的解决数据一致性的问题，但是同样会大大的降低数据库的实际吞吐性能。所以该隔离级别因为并发性比较低、损耗太大，一般很少在开发中使用。
●MySQL中事务隔离级别为Serializable时会锁表，因此不可能出现脏读数据、不可重复读、幻读的情况。
●两个事务，当一个事务操作数据库时，另一个事务只能排队等待。
●这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，多个事务之间串行，而不并发。
●这种隔离级别很少使用，吞吐量太低，用户体验差。
示例：
|
session_1 |
session_2 |
|
（1）将当前会话事务隔离级别修改为可序列化。 |
（1）将当前会话事务隔离级别修改为可序列化并查询订单金额。 |
-- 将默认事务隔离级别修改为可序列化 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) -- 开启事务 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec)
|
-- 将默认事务隔离级别修改为可序列化 MySQL [(none)]> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; Query OK, 0 rows affected (0.00 sec) -- 开启事务 MySQL [(none)]> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) -- 查询订单金额 MySQL [(none)]> SELECT OrderAmount FROM goods.orders; +---------------+ | OrderAmount | +---------------+ | 100000.000000 | +---------------+ 1 row in set (0.00 sec) |
|
|
（2）插入一条订单数据（其实已锁住订单表了）。 |
-- 插入订单数据 MySQL [(none)]> INSERT INTO goods.orders(OrderAmount) VALUES (5000); Query OK, 1 row affected (0.00 sec) |
|
|
（2）查询订单数据，会报错，那是因为订单表已被锁了。 |
|
MySQL [(none)]> SELECT OrderAmount FROM goods.orders; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
参考文献：
深入浅出MySQL大全
数据库事务（以MySQL8.0.11为例）
