# 并发控制与事务的隔离级别
# 任务描述
本关任务：
设置事务的隔离级别
# 相关知识
并发操作可能产生的数据不一致性；
MySQL 的事务隔离级别；
隔离级别，一致性和并发度的关系。
# 并发操作可能产生的数据不一致性
数据库是共享资源，允许多个用户同时访问同一数据库，特别是在互联网应用成为主流的当下，高可用性、高并发是所有应用追求的目标。但并发操作不加控制，便会产生数据的不一致性。
并发操作可能带来的数据不一致性包括：
- 丢失修改 (lost update)
- 读脏数据 (dirty read)
- 不可重复读 (non-repeatable read)
- 幻读 (phantom read)
为解决上述不一致性问题，DBMS 设计了专门的并发控制子系统，采用封锁机制进行并发控制，以保证事务的隔离性和一致性 (事务是并发控制的基本单位)。
但事务的隔离程度越高，固然一致性 -- 或者説数据的正确性越有保障，但并发度就会越低。很多时候，需要在一致性和并发度间进行取舍，从而就生产了事务的隔离级别的概念。 隔离级别越高，一致性程度越高，并发度越低。反之，隔离级别越低，并发度越高，但代价是会出现某些数据不一致现象。
对不一致性的解释：https://www.cnblogs.com/kyoner/p/11305204.html#:~:text = 这样第一个事务内的修改结果就被丢失，因此称为丢失修改。, 例如：事务 1 读取某表中的数据 A%3D20，事务 2 也读取 A%3D20，事务 1 修改 A%3DA-1，事务 2 也修改 A%3DA-1，最终结果 A%3D19，事务 1 的修改被丢失
# MySQL 的事务隔离级别
在前述的几类不一致性中，只有丢失修改是不能容忍的，所有的商用 DBMS，其事务管理和并发控制子系统都不会允许这种情形发生。所以，事务隔离级别的最低限度是容忍 “读脏”。在追求高并发的场景中，除 “丢失修改” 外，其它不一致性都是可以容忍的。
不同的 DBMS，其事务的隔离级别划分是不同的。
MySQL 的事务隔离级别从低到高分以下四级：
- 读未提交（READ UNCOMITTED）
- 读已提交（READ COMMITTED）
- 可重复读（REPEATABLE READ）
- 可串行化（SERIALIZABLE）
低隔离级别可以支持更高的并发处理，同时占用的系统资源更少，但可能产生数据不一致的情形也更多一些。
MySQL 事务隔离级别及其可能产生的问题如下表所示：
上面的表格尚未完全理解
上表说明，最低的隔离级别不能避免读脏、不可重复读和幻读，而最高的隔离级别，可保证多个并发事务的任何调度，都不会产生数据的不一致性，但其代价是并发度最低。
# 查询事务的隔离级别
可用以下语句查询 MySQL 的事务隔离级别：
select @@GLOBAL.transaction_isolation, @@transaction_isolation; |
其中， @@GLOBAL.transaction_isolation 全局变量， @@transaction_isolation 为本会话期内的变量。通常通过重设该变量的值以改变隔离级别。
上述两个变量的缺省值均为： REPEATABLE-READ ，即可重复读。
说明：MySQL8 的事务隔离级别变量名与之前的版本是不同的，请不要参考旧版本的说明文档。
# 设置事务的隔离级别
以下语句设置事务的隔离级别为可读未提交 (read uncommitted):
set session transaction isolation level read uncommitted; |
如需设置为其它级别，只需替换最后的隔离级别即可。
不同的事务隔离级别意味着不同的封锁协议，程序员只需设置事务的隔离级别即可，其它的交给 DBMS 并发子系统处理。
不过，MySQL 也有 lock tables 和 unlock tables 语句，可以直接锁表，另外，MySQL 还支持在 select 语句中使用 for share 或 for update 短语主动申请 S 锁或 X 锁 (只到事务结束才释放)。这样，即使在隔离级别为 read uncommitted 的情形下，仍有机会保证可重复读，相关内容请参阅 MySQL 官方文档。
# 示例
在数据库 testdb1 中建下表，并插入一条数据：
CREATE TABLE `dept` ( | |
`id` int(11) NOT NULL AUTO_INCREMENT, | |
`name` varchar(20) DEFAULT NULL, | |
PRIMARY KEY (`id`) | |
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; | |
insert into dept(name) values("行政部"); |
同时开启两个事务，并按下表所示的调度执行：
请大家先思考一下，事务 1 两次查询的结果分别是什么？
正确答案是：
事务 1 两次查询的结果都一样：
虽然事务 1 第二次查询时，事务 2 已提交，但事务 1 和 2 都采用了较高级别的事务隔离机制：REPEATABLE-READ (缺省的事务隔离级别)。事务 1 的两次读结果保持了一致，事务 2 对事务 1 没有影响。
两个事务同时开启，并发执行，其调度顺序具有一定的随机性，每次执行的结果都是不确定的，这取决于很多因素。如果你想演示上述确定的调度顺序，需要在事务中适当的地方加上延时语句，比如长循环，作无竟义的连接操作等，当然，更好的方法是直接采用 MySQL 的 sleep() 函数，它使得事务暂时休眠指定时间 (单位是秒)。上述两个事务可以分别改造如下：
事务 1：
use testdb1; | |
begin; | |
select * from dept; | |
set @n = sleep(5); | |
select * from dept; | |
commit; |
事务 2:
use testdb1; | |
begin; | |
set @n = sleep(1); | |
insert into dept(name) values("研发部"); | |
commit; |
事务 1 在作完第 1 次查询后，等待了 5 秒，这个时间足够轮到事务 2 完成插入并提交，然后事务 1 再次查询，此时，事务 2 早已提交。
你还可以尝试在适当的地方添上显示时间 ( select now(); ) 的语句，验证两个事务的读写语句执行的时间；
为使两个事务并发执行，可以在诸如 navicat 软件中打开两个会话，将两段代码分别粘贴到两个会话，快速点击两个会话的运行按钮让这两个事务并发执行，便可以观察到前面的执行结果。
你也可以在自己的笔记本电脑上体验：同时打开两个命令行窗口，分别进入到 MySQL 命令行环境，在完成数据库和表的初始化后，按上表的顺序，分别在两个窗口交替输入两个事务的语句并回车执行，就能观察到上述效果。建议你将两个窗口调到合适的大小，事务 1 在左，事务 2 在右，以便观察结果。手工输入的话，不需要添加延时语句，因为你输入的顺序已决定了每条语句的执行顺序。
你也可以在实训平台上验证，在命令行右侧点击 + 号，可以开启第 2 个命令行窗口。启动 MySQL, 初始化数据库后，可轮流在两个窗口中输入语句并执行，以观察效果。
# 编程要求
在右侧代码文件编辑器里根据提示补充适当的代码，将事务的隔离级别设置为 read uncommitted; 并以 rollback 语句结束事务。
-- 请不要在本代码文件中添加空行！！！ | |
use testdb1; | |
# 设置事务的隔离级别为 read uncommitted | |
set session transaction isolation level read uncommitted; | |
-- 开启事务 | |
start transaction; | |
insert into dept(name) values('运维部'); | |
# 回滚事务： | |
rollback; | |
/* 结束 */ |
# 读脏
# 任务描述
本关任务：
选择合适的事务隔离级别，构造两个事务并发执行时，发生 “读脏” 现象。
# 相关知识
读脏及其产生的原因
# 读脏
读脏 (dirty read)，或者又叫脏读，是指一个事务 (t1) 读取到另一个事务 (t2) 修改后的数据，后来事务 t2 又撤销了本次修改 (即事务 t2 以 roll back 结束)，数据恢复原值。这样，事务 t1 读到的数据就与数据库里的实际数据不一致，这样的数据被称为 “脏” 数据，意即不正确的数据。
# 读脏产生的原因
显然，产生读脏的原因，是事务 t1 读取数据时，修改该数据的事务 t2 还没有结束 ( commit 或 roll back ，统称 uncommitted ), 且 t1 读取的时间点又恰在 t2 修改该数据之后。
根据上一关介绍的基本知识，只有一种隔离级别可能会产生读脏。
# 编程要求
有表 ticket 记录了航班余票数，其结构如下表所示：
现有两个涉及该表的并发事务 t1 和 t2，分别定义在 t1.sql 和 t2.sql 代码文件中，请在两个代码文件适当的地方补充代码，构造 “读脏” 现象。t1 是读脏的那个事务，而 t2 是那个修改数据后又撤销的事务。
t1.sql
-- 事务 1: | |
use testdb1; | |
## 请设置适当的事务隔离级别 | |
set session transaction isolation level read uncommitted; | |
start transaction; | |
-- 时刻 2 - 事务 1 读航班余票，发生在事务 2 修改之后 | |
## 添加等待代码，确保读脏 | |
set @n = sleep(1); | |
select tickets from ticket where flight_no = 'CA8213'; | |
commit; |
t2.sql
-- 事务 2 | |
use testdb1; | |
## 请设置适当的事务隔离级别 | |
set session transaction isolation level read uncommitted; | |
start transaction; | |
-- 时刻 1 - 事务 2 修改航班余票 | |
update ticket set tickets = tickets - 1 where flight_no = 'CA8213'; | |
-- 时刻 3 - 事务 2 取消本次修改 | |
## 请添加代码，使事务 1 在事务 2 撤销前读脏； | |
set @n = sleep(2); | |
rollback; |
# 不可重复读
# 任务描述
本关任务：
选择合适的事务隔离级别，构造两个事务并发执行时，发生 “不可重复读” 现象。
# 相关知识
不可重复读及其产生的原因
# 不可重复读
不可重复读 (unrepeatable read)，是指一个事务 (t1) 读取到某数据后，另一个事务 (t2) 修改了该，事务 t1 并未修改该数据，但当 t1 再次读取该数据时，发现两次读取的结果不一样。
# 产生不可重复读的原因
显然，不可重复读产生的原因，是事务 t1 的两次读取之间，有另一个事务修改了 t1 读取的数据。
根据第一关介绍的基本知识，有两种隔离级别都有可能发生不可重复读。
# 编程要求
有表 ticket 记录了航班余票数，其结构如下表所示：
现有两个涉及该表的并发事务 t1 和 t2，分别定义在 t1.sql 和 t2.sql 代码文件中，请在两个代码文件适当的地方补充代码，构造 “不可重复读” 现象。t2 是发生不可重复读的那个事务，t1 在 t2 的两次连续读之间修改了数据。
由于两个事务均有读和写操作，且并发执行，其输出顺序具有不确定性，为评测方便，所有的读 (select) 操作的结果，均被写入到一个名为 result 的表中，写入时记下了事务编号、读取的时间和读到的结果。请不要修改这些语句，否则会影响评测结果。
需要补充代码的位置均在以 #开头的注释行的下一行。必要时，请复习第 1 关介绍的相关内容。
t1.sql
-- 事务 1: | |
## 请设置适当的事务隔离级别 | |
set session transaction isolation level read committed; | |
-- 开启事务 | |
start transaction; | |
-- 时刻 1 - 事务 1 读航班余票: | |
insert into result | |
select now(),1 t, tickets from ticket where flight_no = 'CZ5525'; | |
## 添加等待代码，确保事务 2 的第一次读取在事务 1 修改前发生 | |
set @n = sleep(2); | |
-- 时刻 3 - 事务 1 修改余票，并立即读取: | |
update ticket set tickets = tickets - 1 where flight_no = 'CZ5525'; | |
insert into result | |
select now(),1 t, tickets from ticket where flight_no = 'CZ5525'; | |
## 添加代码，使事务 2 的第 2 次读取在事务 1 修改之后，提交之前发生 | |
commit; | |
-- 时刻 6 - 事务 1 在 t2 也提交后读取余票 | |
## 添加代码，确保事务 1 在事务 2 提交后读取 | |
set @n = sleep(3); | |
insert into result | |
select now(), 1 t, tickets from ticket where flight_no = 'CZ5525'; |
t2.sql
-- 事务 2 | |
## 请设置适当的事务隔离级别以构造不可重复读 | |
set session transaction isolation level read committed; | |
start transaction; | |
-- 时刻 2 - 事务 2 在事务 1 读取余票之后也读取余票 | |
## 添加代码，确保事务 2 的第 1 次读发生在事务 1 读之后，修改之前 | |
set @n = sleep(1); | |
insert into result | |
select now(),2 t, tickets from ticket where flight_no = 'CZ5525'; | |
-- 时刻 4 - 事务 2 在事务 1 修改余票但未提交前再次读取余票，事务 2 的两次读取结果应该不同 | |
## 添加代码，确保事务 2 的读取时机 | |
set @n = sleep(2); | |
insert into result | |
select now(), 2 t, tickets from ticket where flight_no = 'CZ5525'; | |
-- 事务 2 立即修改余票 | |
update ticket set tickets = tickets - 1 where flight_no = 'CZ5525'; | |
-- 时刻 5 - 事务 2 读取余票（自己修改但未交的结果）: | |
set @n = sleep(1); | |
insert into result | |
select now(), 2 t, tickets from ticket where flight_no = 'CZ5525'; | |
commit; |
对上述代码的疑问：
在开启事务之后，然后 update，是否需要 commit 了，查询到的数据才是修改了的数据。
select 后接数字：https://blog.csdn.net/weixin_44840696/article/details/89166154?spm=1001.2101.3001.6650.1&utm_medium=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~Rate-1-89166154-blog-51141221.pc_relevant_vip_default&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2~default~BlogCommendFromBaidu~Rate-1-89166154-blog-51141221.pc_relevant_vip_default&utm_relevant_index=2
# 幻读
# 任务描述
本关任务：
在 repeatable read 事务隔离级别，构造两个事务并发执行时，发生 “幻读” 现象。
# 相关知识
幻读及其产生的原因
# 幻读 (phantom read)
幻读定义其实是有些争议的，在某些文献中，幻读被归为不可重复读 (unrepeatable read) 中的一类，而另一些则把它与不可重复读区分开来：幻读是指一个事务 (t1) 读取到某数据后，另一个事务 (t2) 作了 insert 或 delete 操作，事务 t1 再次读取该数据时，魔幻般地发现数据变多了或者变少了 (记录数量不一致)；而不可重复读限指事务 t2 作了 update 操作，致使 t1 的两次读操作读到的结果 (数据的值) 不一致。
# 产生幻读的原因
显然，幻读产生的原因，是事务 t1 的两次读取之间，有另一个事务 insert 或 delete 了 t1 读取的数据集。
根据第一关介绍的基本知识，除了最高级别 serializable (可串行化) 以外的任何隔离级别，都有可能发生幻读。
# 编程要求
在低隔离级别，复现幻读是很容易的。本关要求大家在较高隔离级别，即仅次于 serializable 的 repeatable read 隔离级别下重现 “幻读” 现象，这样，可更好地体验不可重复读与幻读的区别。
设有表 ticket 记录了航班余票数，其结构如下表所示：
现有两个涉及该表的并发事务 t1 和 t2，分别定义在 t1.sql 和 t2.sql 代码文件中。t2.sql 的代码如下：
-- 事务 2（采用默认的事务隔离级别 - repeatable read）: | |
use testdb1; | |
start transaction; | |
set @n = sleep(1); | |
insert into ticket values('MU5111','A330-200',311); | |
commit; |
请在代码文件 t1.sql 适当的地方补充代码，要求如下:
- 两次查询余票超过 300 张的航班信息 (第 2 次查询已替你写好)；
- 在第 1 次查询之后，事务 t2 插入了一条航班信息并提交 (t2.sql 已替你写好)；
- 第 2 次查询的记录数增多，发生 “幻读”。
- 不得修改 t1 的事务隔离级别 (保持默认的 repeatable read)
-- 事务 1（采用默认的事务隔离级别 - repeatable read）: | |
use testdb1; | |
select @@transaction_isolation; | |
start transaction; | |
## 第 1 次查询余票超过 300 张的航班信息 | |
select * from ticket where tickets > 300; | |
set @n = sleep(2); | |
-- 修改航班 MU5111 的执飞机型为 A330-300： | |
update ticket set aircraft = 'A330-300' where flight_no = 'MU5111'; | |
-- 第 2 次查询余票超过 300 张的航班信息 | |
select * from ticket where tickets > 300; | |
commit; |
# 主动加锁保证可重复读
# 任务描述
本关任务：
在事务隔离级别较低的 read uncommitted 情形下，通过主动加锁，保证事务的一致性
# 相关知识
共享锁与写锁
# MySQL 对共享锁与锁的支持
通过设置不同的隔离级别，以实现不同的一致性与并发度的需求是较通常的作法。但 MySQL 也提供了主动加锁的机制，使得在较低的隔离级别下，通过加锁，以实现更高级别的一致性。
MySQL 的 select 语句支持 for share 和 for update 短语，分别表示对表加共享 (Share) 锁和写 (write) 锁，共享锁也叫读锁，写锁又叫排它锁。
下面这条语句，会对表 t1 加共享锁:
select * from t1 for share; |
如果 select 语句涉及多张表，还可分别对不同的表加不同的锁，比如：
select * from t1,t2 for share of t1 for update of t2; |
加锁短语总是 select 语句的最后一个短语 (复杂的 select 语句可能有 where,group by, having, order by 等短语)；
不管 share 还是 update 锁，都是在事务结束时才释放。
当然，锁行为会降低并发度。
# 编程要求
有表 ticket 记录了航班余票数，其结构如下表所示：
现有两个涉及该表的并发事务 t1 和 t2，分别定义在 t1.sql 和 t2.sql 代码文件中，请在两个代码文件适当的地方补充代码，实现：
- 两个事务的隔离级别都设置成 read uncommitted;
- 事务 t1 连续查询两次航班 MU2455 的余票；
- 事务 t2 在 t1 的两次查询之间试图进行一次出票操作：将航班 MU2455 的余票减去 1 张。
- 保证事务 t1 的两次读结果是一样的 (可重复读)
- 事务 t1 结束后，在等待 t2 正常提交 (commit) 后，再查询一次全部航班的余票，MU2455 的余票应该减少 1 张。
t1.sql
-- 事务 1: | |
use testdb1; | |
set session transaction isolation level read uncommitted; | |
start transaction; | |
# 第 1 次查询航班 'MU2455' 的余票 | |
select tickets from ticket where flight_no = 'MU2455' for update; | |
set @n = sleep(5); | |
# 第 2 次查询航班 'MU2455' 的余票 | |
select tickets from ticket where flight_no = 'MU2455' for update; | |
commit; | |
-- 第 3 次查询所有航班的余票，发生在事务 2 提交后 | |
set @n = sleep(1); | |
select * from ticket; |
t2.sql
-- 事务 2: | |
use testdb1; | |
set session transaction isolation level read uncommitted; | |
start transaction; | |
set @n = sleep(1); | |
# 在事务 1 的第 1，2 次查询之间，试图出票 1 张 (航班 MU2455)： | |
update ticket set tickets = tickets - 1 where flight_no = 'MU2455'; | |
commit; |
# 可串行化
# 任务描述
本关任务：
选择除 serializable (可串行化) 以外的任何隔离级别，保证两个事务并发执行的结果是可串行化的。
# 相关知识
可串行化
# 可串行化
多个事务并发执行是正确的，当且仅当其结果与按某一次序串行地执行这些事务时的结果相同。两个事务 t1,t2 并发执行，如果结果与 t1→t2 串行执行的结果相同，或者与 t2→t1 串行执行的结果相同，都是正确的 (可串行化的)。
如果将事务的隔离级别设置为 serializable，则这些事务并发执行，无论怎么调度都会是可串行化的。但这种隔离级别会大大降低并发度，在实践中极小使用。MySQL 默认的隔离级别为 repeatable read，有的 DBMS 默认为 read committed。
# 编程要求
有表 ticket 记录了航班余票数，其结构如下表所示：
现有两个涉及该表的并发事务 t1 和 t2，分别定义在 t1.sql 和 t2.sql 代码文件中。事务 t1 两次查询航班 MU2455 的余票，事务 t2 修改航班 MU2455 的余票 (减 1)。请对两个代码文件进行修改，使得两个事务并发执行的结果与 t2→t1 串行执行的结果相同。
除两个事务的 select 和 update 语句不可修改外 (修改它们会影响输出)，你可以修改、添加代码。但不得将事务的隔离级别设置为 serializable，你可以保持默认隔离级别，或设置成其它隔离级别。
t1.sql
-- 事务 1: | |
use testdb1; | |
start transaction; | |
set @n = sleep(5); | |
select tickets from ticket where flight_no = 'MU2455'; | |
select tickets from ticket where flight_no = 'MU2455'; | |
commit; |
t2.sql
-- 事务 2: | |
use testdb1; | |
start transaction; | |
update ticket set tickets = tickets - 1 where flight_no = 'MU2455'; | |
commit; |
