|
回顾前文:
一文学会MySQL的explain工具
一文读懂MySQL的索引结构及查询优化
(同时再次强调，这几篇关于MySQL的探究都是基于5.7版本，相关总结与结论不一定适用于其他版本)
就软件开发而言，既要保证数据读写的效率，还要保证并发读写数据的可靠性、正确性。因此，除了要对MySQL的索引结构及查询优化有所了解外，还需要对MySQL的事务隔离级别及MVCC机制有所认知。
MySQL官方文档中的词汇表(https://dev.mysql.com/doc/refman/5.7/en/glossary.html)有助于我们对相关概念、理论的理解。下文中我会从概念表中摘录部分原文描述，以加深对原理机制的理解。
事务隔离级别
事务是什么
Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database,either all the changes succeed when the transaction is committed,or all the changes are undone when the transaction is rolled back.
事务是由一组SQL语句组成的原子操作单元，其对数据的变更，要么全都执行成功(Committed)，要么全都不执行(Rollback)。
Database transactions,as implemented by InnoDB,have properties that are collectively known by the acronym ACID,for atomicity,consistency,isolation,and durability.
InnoDB实现的数据库事务具有常说的ACID属性，即原子性(atomicity)，一致性(consistency)、隔离性(isolation)和持久性(durability)。
-
原子性：事务被视为不可分割的最小单元，所有操作要么全部执行成功，要么失败回滚(即还原到事务开始前的状态，就像这个事务从来没有执行过一样)
-
一致性：在成功提交或失败回滚之后以及正在进行的事务期间，数据库始终保持一致的状态。如果正在多个表之间更新相关数据，那么查询将看到所有旧值或所有新值，而不会一部分是新值，一部分是旧值
-
隔离性：事务处理过程中的中间状态应该对外部不可见，换句话说，事务在进行过程中是隔离的，事务之间不能互相干扰，不能访问到彼此未提交的数据。这种隔离可通过锁机制实现。有经验的用户可以根据实际的业务场景，通过调整事务隔离级别，以提高并发能力
-
持久性：一旦事务提交，其所做的修改将会永远保存到数据库中。即使系统发生故障，事务执行的结果也不能丢失
In InnoDB,all user activity occurs inside a transaction. If autocommit mode is enabled,each SQL statement forms a single transaction on its own. By default,MySQL starts the session for each new connection with autocommit enabled,so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error,the commit or rollback behavior depends on the error
MySQL默认采用自动提交(autocommit)模式。也就是说，如果不显式使用START TRANSACTION或BEGIN语句来开启一个事务，那么每个SQL语句都会被当做一个事务自动提交。
A session that has autocommit enabled can perform a multiple-statement transaction by starting it with an explicit START TRANSACTION or BEGIN statement and ending it with a COMMIT or ROLLBACK statement.
多个SQL语句开启一个事务也很简单，以START TRANSACTION或者BEGIN语句开头，以COMMIT或ROLLBACK语句结尾。
If autocommit mode is disabled within a session with SET autocommit = 0,the session always has a transaction open. A COMMIT or ROLLBACK statement ends the current transaction and a new one starts.
使用SET autocommit = 0可手动关闭当前session自动提交模式。
并发事务的问题
引出事务隔离级别
相关文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
Isolation is the I in the acronym ACID; the isolation level is the setting that fine-tunes the balance between performance and reliability,and reproducibility of results when multiple transactions are making changes and performing queries at the same time.
也就是说当多个并发请求访问MySQL，其中有对数据的增删改请求时，考虑到并发性，又为了避免脏读、不可重复读、幻读等问题，就需要对事务之间的读写进行隔离，至于隔离到啥程度需要看具体的业务场景，这时就要引出事务的隔离级别了。
InnoDB offers all four transaction isolation levels described by the SQL:1992 standard: READ UNCOMMITTED,READ COMMITTED,REPEATABLE READ,and SERIALIZABLE. The default isolation level for InnoDB is REPEATABLE READ.
InnoDB存储引擎实现了SQL标准中描述的4个事务隔离级别：读未提交(READ UNCOMMITTED)、读已提交(READ COMMITTED)、可重复读(REPEATABLE READ)、可串行化(SERIALIZABLE)。InnoDB默认隔离级别是可重复读(REPEATABLE READ)。
设置事务隔离级别
既然可以调整隔离级别，那么如何设置事务隔离级别呢？详情见官方文档：https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html
MySQL5.7.18版本演示如下：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18 |
+-----------+
1 row in set (0.00 sec)
mysql> set global transaction isolation level REPEATABLE READ;
Query OK,0 rows affected (0.00 sec)
mysql> set session transaction isolation level READ COMMITTED;
Query OK,0 rows affected (0.00 sec)
mysql> select @@global.tx_isolation,@@session.tx_isolation,@@tx_isolation;
+-----------------------+------------------------+----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+----------------+
| REPEATABLE-READ | READ-COMMITTED | READ-COMMITTED |
+-----------------------+------------------------+----------------+
1 row in set (0.00 sec)
MySQL8.0.21版本演示如下：
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.01 sec)
mysql> set global transaction isolation level REPEATABLE READ;
Query OK,0 rows affected (0.00 sec)
mysql> select @@global.transaction_isolation,@@session.transaction_isolation,@@transaction_isolation;
+--------------------------------+---------------------------------+-------------------------+
| @@global.transaction_isolation | @@session.transaction_isolation | @@transaction_isolation |
+--------------------------------+---------------------------------+-------------------------+
| REPEATABLE-READ | READ-COMMITTED | READ-COMMITTED |
+--------------------------------+---------------------------------+-------------------------+
1 row in set (0.00 sec)
注意：
transaction_isolation was added in MySQL 5.7.20 as a synonym for tx_isolation,which is now deprecated and is removed in MySQL 8.0. Applications should be adjusted to use transaction_isolation in preference to tx_isolation.
Prior to MySQL 5.7.20,use tx_isolation and tx_read_only rather than transaction_isolation and transaction_read_only.
如果使用系统变量(system variables)来查看或者设置事务隔离级别，需要注意MySQL的版本。在MySQL5.7.20之前，应使用tx_isolation；在MySQL5.7.20之后，应使用transaction_isolation。
You can set transaction characteristics globally,for the current session,or for the next transaction only.
事务的隔离级别范围(Transaction Characteristic Scope)可以精确到全局(global)、当前会话(session)、甚至是仅针对下一个事务生效(the next transaction only)。
- 含
global关键词时，事务隔离级别的设置应用于所有后续session，已存在的session不受影响
- 含
session关键词时，事务隔离级别的设置应用于在当前session中执行的所有后续事务，不会影响当前正在进行的事务
- 不含
global以及session关键词时，事务隔离级别的设置仅应用于在当前session中执行的下一个事务
数据准备
为了演示脏读、不可重复读、幻读等问题，准备了一些初始化数据如下：
-- ----------------------------
-- create database
-- ----------------------------
create database `transaction_test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- switch database
use `transaction_test`;
-- ----------------------------
-- table structure for `tb_book`
-- ----------------------------
CREATE TABLE `tb_book` (
`book_id` int(11) NOT NULL,`book_name` varchar(64) DEFAULT NULL,`author` varchar(32) DEFAULT NULL,PRIMARY KEY (`book_id`),UNIQUE KEY `uk_book_name` (`book_name`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
BEGIN;
INSERT INTO `tb_book`(`book_id`,`book_name`,`author`) VALUES (1,'多情剑客无情剑','古龙');
INSERT INTO `tb_book`(`book_id`,`author`) VALUES (2,'笑傲江湖','金庸');
INSERT INTO `tb_book`(`book_id`,`author`) VALUES (3,'倚天屠龙记',`author`) VALUES (4,'射雕英雄传',`author`) VALUES (5,'绝代双骄','古龙');
COMMIT;
脏读(read uncommitted)
事务A读到了事务B已经修改但尚未提交的数据
操作：
-
session A事务隔离级别设置为read uncommitted并开启事务，首次查询book_id为1的记录；
- 然后
session B开启事务，并修改book_id为1的记录，不提交事务，在session A中再次查询book_id为1的记录；
- 最后让
session B中的事务回滚，再在session A中查询book_id为1的记录。
session A：
mysql> set session transaction isolation level read uncommitted;
Query OK,0 rows affected (0.00 sec)
mysql> begin;
Query OK,0 rows affected (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情剑客无情剑 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情剑客无情剑 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> commit;
Query OK,0 rows affected (0.00 sec)
session B：
mysql> begin;
Query OK,0 rows affected (0.00 sec)
mysql> update tb_book set book_name = '多情刀客无情刀' where book_id = 1;
Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> rollback;
Query OK,0 rows affected (0.00 sec)
结果：事务A读到了事务B还没提交的中间状态，即产生了脏读。
不可重复读(read committed)
事务A读到了事务B已经提交的修改数据
操作：
-
session A事务隔离级别设置为read committed并开启事务，首次查询book_id为1的记录；
- 然后
session B开启事务，并修改book_id为1的记录，不提交事务，在session A中再次查询book_id为1的记录；
- 最后提交
session B中的事务，再在session A中查看book_id为1的记录。
session A：
mysql> set session transaction isolation level read committed;
Query OK,0 rows affected (0.01 sec)
mysql> begin;
Query OK,0 rows affected (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情剑客无情剑 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情剑客无情剑 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> select * from tb_book where book_id = 1;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
+---------+-----------------------+--------+
1 row in set (0.00 sec)
mysql> commit;
Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK,0 rows affected (0.00 sec)
结果：事务B没有提交事务时，事务A不会读到事务B修改的中间状态，即read committed解决了上面所说的脏读问题，但是当事务B中的事务提交后，事务A读到了修改后的记录，而对于事务A来说，仅仅读了两次，却读到了两个不同的结果，违背了事务之间的隔离性，所以说该事务隔离级别下产生了不可重复读的问题。
幻读(repeatable read)
事务A读到了事务B提交的新增数据
操作：
-
session A事务隔离级别设置为repeatable read并开启事务，并查询book列表
-
session B开启事务，先修改book_id为5的记录，再插入一条新的数据，提交事务，在session A中再次查询book列表
- 在
session A中更新session B中新插入的那条数据，再查询book列表
session A：
mysql> set session transaction isolation level repeatable read;
Query OK,0 rows affected (0.00 sec)
mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
| 2 | 笑傲江湖 | 金庸 |
| 3 | 倚天屠龙记 | 金庸 |
| 4 | 射雕英雄传 | 金庸 |
| 5 | 绝代双骄 | 古龙 |
+---------+-----------------------+--------+
5 rows in set (0.00 sec)
mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
| 2 | 笑傲江湖 | 金庸 |
| 3 | 倚天屠龙记 | 金庸 |
| 4 | 射雕英雄传 | 金庸 |
| 5 | 绝代双骄 | 古龙 |
+---------+-----------------------+--------+
5 rows in set (0.00 sec)
mysql> update tb_book set book_name = '圆月弯剑' where book_id = 6;
Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
| 2 | 笑傲江湖 | 金庸 |
| 3 | 倚天屠龙记 | 金庸 |
| 4 | 射雕英雄传 | 金庸 |
| 5 | 绝代双骄 | 古龙 |
| 6 | 圆月弯剑 | 古龙 |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)
mysql> rollback;
Query OK,0 rows affected (0.00 sec)
mysql> update tb_book set book_name = '绝代双雄' where book_id = 5;
Query OK,1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> insert into tb_book values (6,'圆月弯刀','古龙');
Query OK,1 row affected (0.00 sec)
mysql> commit;
Query OK,0 rows affected (0.00 sec)
mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
| 2 | 笑傲江湖 | 金庸 |
| 3 | 倚天屠龙记 | 金庸 |
| 4 | 射雕英雄传 | 金庸 |
| 5 | 绝代双雄 | 古龙 |
| 6 | 圆月弯刀 | 古龙 |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)
结果：事务B已提交的修改记录(即绝代双骄修改为绝代双雄)在事务A中是不可见的，说明该事务隔离级别下解决了上面不可重复读的问题，但魔幻的是一开始事务A中虽然读不到事务B中的新增记录，却可以更新这条新增记录，执行更新(update)后，在事务A中居然可见该新增记录了，这便产生了所谓的幻读问题。
为什么会出现这样莫名其妙的结果？ 别急，后文会慢慢揭开这个神秘的面纱。先看如何解决幻读问题。
串行化(serializable)
serializable事务隔离级别可以避免幻读问题，但会极大的降低数据库的并发能力。
SERIALIZABLE: the isolation level that uses the most conservative locking strategy,to prevent any other transactions from inserting or changing data that was read by this transaction,until it is finished.
操作：
-
session A事务隔离级别设置为serializable并开启事务，并查询book列表，不提交事务；
- 然后
session B中分别执行insert、delete、update操作
session A：
mysql> set session transaction isolation level serializable;
Query OK,0 rows affected (0.00 sec)
mysql> select * from tb_book;
+---------+-----------------------+--------+
| book_id | book_name | author |
+---------+-----------------------+--------+
| 1 | 多情刀客无情刀 | 古龙 |
| 2 | 笑傲江湖 | 金庸 |
| 3 | 倚天屠龙记 | 金庸 |
| 4 | 射雕英雄传 | 金庸 |
| 5 | 绝代双雄 | 古龙 |
| 6 | 圆月弯刀 | 古龙 |
+---------+-----------------------+--------+
6 rows in set (0.00 sec)
session B：
mysql> insert into tb_book values (7,'神雕侠侣','金庸');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> delete from tb_book where book_id = 1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update tb_book set book_name = '绝代双骄' where book_id = 5;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
结果：只要session A中的事务一直不提交，session B中尝试更改数据(insert、delete、update)的事务都会被阻塞至超时(timeout)。显然，该事务隔离级别下能有效解决上面幻读、不可重复读、脏读等问题。
注意：除非是一些特殊的应用场景需要serializable事务隔离级别，否则很少会使用该隔离级别，因为并发性极低。
事务隔离级别小结
事务隔离级别 |
脏读 |
不可重复读 |
幻读 |
| 
