<<返回mysql首页 mysql

《MySQL 应用案例》

实践MySQL事务隔离级别

作者:iamswf

原作链接:https://juejin.im/post/6856789774120779783

本文通过实践来学习下MySQL的事物隔离级别。

事务(Transaction)又叫做TCL,全称是transaction control language,意思是事务控制语言。是访问和更新数据库的程序执行单元;事务中可能包含一个或多个sql语句,这些语句要么都执行,要么都不执行。作为一个关系型数据库,MySQL支持事务。

实践MySQL事务隔离级别

事务的基本要素(ACID)

1、原子性(Atomicity):事务开始后所有操作,要么全部做完,要么全部不做,不可能停滞在中间环节。事务执行过程中出错,会回滚到事务开始前的状态,所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体,就像化学中学过的原子,是物质构成的基本单位。

2、一致性(Consistency):事务开始前和结束后,数据库的完整性约束没有被破坏 。比如A向B转账,不可能A扣了钱,B却没收到。

3、隔离性(Isolation):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱,在A取钱的过程结束前,B不能向这张卡转账。

4、持久性(Durability):事务完成后,事务对数据库的所有更新将被保存到数据库,不能回滚。

事务的并发问题

1、脏读:事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的数据是脏数据

2、不可重复读:事务 A 多次读取同一数据,事务 B 在事务A多次读取的过程中,对数据作了更新并提交,导致事务A多次读取同一数据时,结果 不一致。

3、幻读:系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级,但是系统管理员B就在这个时候插入了一条具体分数的记录,当系统管理员A改结束后发现还有一条记录没有改过来,就好像发生了幻觉一样,这就叫幻读。

小结:不可重复读的和幻读很容易混淆,不可重复读侧重于修改,幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行,解决幻读需要锁表**

MySQL事务隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交(read-uncommitted)
不可重复读(read-committed)
可重复读(repeatable-read)
串行化(serializable)

环境及测试表准备

MySQL环境安装请参照这里,这里也有相关的MySQL知识。在设置好MySQL之前后,我们创建一张用于测试的表 tb_test

CREATE DATABASE test;
USE test;
CREATE TABLE tb_test (
  id int(11) NOT NULL auto_increment, 
  text varchar(200) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后插入3条测试记录:

INSERT INTO tb_test(text) VALUES ('first row');
INSERT INTO tb_test(text) VALUES ('second row');
INSERT INTO tb_test(text) VALUES ('third row');

说明:

以下实操中的ssh A是指在右侧实验区中默认的ssh环境。 ssh B是指在右侧实验区中+号中打开的新的ssh环境。

1. READ UNCOMMITTED级别

开启ssh A,将session的隔离级别设置为 READ UNCOMMITTED 级别,使用 BEGIN 语句开启一个事物,然后读取 id=1 的记录:

-- ssh A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
select * from tb_test where id = 1;

然后开启ssh B,进入mysql环境后使用test库,再使用 BEGIN 语句开启一个事物,并更新 id=1 的记录的 text 字段:

-- ssh B
USE test;
BEGIN;
UPDATE tb_test SET TEXT = '1 row' WHERE ID = 1;

执行成功后返回,但此时事务并未提交。

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

切换回ssh A,然后重新查询 id=1 的记录:

-- ssh A
select * from tb_test where id = 1;

可以看到,虽然ssh B中的事物尚未最终提交,但是也查询到了新的字段值,这就是 脏读 。这是非常危险的一种模式,因为ssh B中的事物有可能会滚,这时就存在数据不一致性。

扫尾工作,将ssh A和ssh B的事物都正式提交:

-- ssh A
COMMIT;
-- ssh B
COMMIT;

总结:

为了解决 脏读 问题,我们可以将隔离级别提高到 READ COMITTED 级别。

2. READ COMMITTED级别

在进行该隔离级别测试之前,我们先看下当前表中的数据现状:

-- ssh A
select * from tb_test;

下面开始进行测试验证。

打开ssh A,将该session的隔离级别设置为 READ COMMITTED ,然后执行查询:

-- ssh A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT * FROM tb_test;

然后打开ssh B,更新 id=1 的记录的字段 text 的值:

-- ssh B
BEGIN;
UPDATE tb_test SET TEXT = 'first rows' WHERE ID = 1;

切换回ssh A,重新查询:

-- ssh A
SELECT * FROM tb_test;

可以看到ssh B中的更新未查询出来。现在我们将ssh B的事物提交:

-- ssh B
COMMIT;

然后再切换回ssh A重新查询:

-- ssh A
SELECT * FROM tb_test;

可以看出来, READ COMMITTED 隔离级别能避免 脏读 ,但是也存在另一个问题,就是ssh A的这个事物,在同一个事物内进行的相同查询,查询出来的结果会不一样。这种不可重复读一般称为 幻读 ,这种模式在某些业务场景下也是难以接受的。

扫尾工作,将ssh A和ssh B的事物都正式提交:

-- ssh A
COMMIT;
-- ssh B
BEGIN;
UPDATE tb_test SET TEXT = 'first row' WHERE ID = 1;
COMMIT;

总结:

  1. 在MySQL底层, 脏读 问题到底是如何解决的?为什么无法避免 幻读

上面的 SELECT * FROM tb_test; 语句其实是一种普通的 无锁读 语句,在MySQL官方文档中称为 Consistent Nonlocking Reads ,这种语句防止 脏读 的原理是基于 MVCC ,即 多版本并发控制 ,简单讲就是每个提交的事物都对应一个版本。而在 READ COMMITTED 的隔离级别中,每次都读取最近已完成提交的那次事物快照,即可保证避免脏读。但是由于每次执行这种 SELECT * FROM tb_test; 时,都是读取的 最新 的已提交事物快照,因此无法避免 幻读

  1. 对于 READ COMMITTED 隔离级别,事物内部执行的语句除了普通的 无锁读 ,肯定还存在 带锁读 ,MySQL官方文档中称为 locking reads ,比如 SELECT FOR UPDATE语句UPDATE语句DELETE语句 ,这种带锁读对阻塞其他事物的 INSERT 么?

答案是不阻塞,因为该隔离级别未开启 间隙锁 ,也就是说不阻塞其他事物在查询的记录间隙(比如 SELECT * FROM tb_test where id >= 10 and id <=20 FOR UPDATE; ,这里的间隙就是 10~20 )插入新记录。这里之所以提到这一点是为了跟后面的 REPEATABLE READ 做对比,此隔离级别是开启 间隙锁 的,也就是防止在间隙内插入记录,这样的好处是可以避免 幻读 问题。

  1. 为了解决 幻读 的问题,我们可以将事物隔离级别继续升级为 REPEATABLE READ

  2. MySQL默认的隔离级别就是READ-COMMITTED;

show variables like  'transaction_isolation';

3. REPEATABLE READ级别

SQL标准中 REPEATABLE READ 是不要求防止 幻读 的,但是MySQL实现的更严格一些,做到了防止 幻读

幻读 定义:事务A 按照一定条件进行数据读取, 期间事务B 插入了相同搜索条件的新数据,事务A再次按照原先条件进行读取时,发现了事务B 新插入的数据称为幻读。如果事务A 按一定条件搜索, 期间事务B 删除了符合条件的某一条数据,导致事务A 再次读取时数据少了一条,这种情况归为不可重复读,不归为幻读。

开始测试前,我们先准备下测试数据:

下面我们开始测试。打开ssh A,将事物调整到 REPEATABLE READ 级别,并执行如下查询:

-- ssh A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM tb_test where id >= 10 and id <=20;
-- 注意我这里特意查询了一个范围:id >= 10 and id <=20

然后切换到ssh B,插入一条 id=15 的记录并提交.

-- ssh B
BEGIN;
INSERT INTO `tb_test`(`id`, `text`) VALUES (15, 'inserted row');
COMMIT;

然后切换回ssh A,重新执行前面查询:

SELECT * FROM tb_test where id >= 10 and id <=20;
-- 查询结果没有变化,还是空集

可见避免了 幻读 。那么我们在ssh B中如果删除 id=2 的记录,那么ssh A能查到吗?可以继续试试。切换到ssh B执行:

-- ssh B
BEGIN;
DELETE FROM tb_test where id = 2;
COMMIT;

再切换到ssh A执行:

-- ssh A
SELECT * FROM tb_test where id >= 10 and id <=20;
-- 查询结果依然没有变化:

可见也避免了删除场景的不可重复读问题。

扫尾工作:将ssh A的事物进行提交。

-- ssh A
COMMIT;

然后执行一个新的事务查询

-- ssh A
BEGIN;
SELECT * FROM tb_test;
COMMIT;

就可以看到了从ssh B中更新的记录:id=2的记录被删除,新增id=15的记录。

总结:

  1. MySQL REPEATABLE READ 隔离级别如何防止 脏读幻读

对于普通的 无锁读 语句, REPEATABLE READ 隔离级别防止 脏读 的原理和 REPEATABLE READ 是一样的,都是基于 MVCC ,只不过 REPEATABLE READ 事物内的 SELECT 语句每次不是读取最新的已提交快照,而是读取的第一次已提交快照,因此也防止了 幻读

  1. 对于 REPEATABLE READ 隔离级别,事物内部执行的语句除了普通的 无锁读 ,肯定也还存在 带锁读 ,比如 SELECT FOR UPDATE语句UPDATE语句DELETE语句 ,这种带锁读对阻塞其他事物的 INSERT 么?

答案是阻塞,该隔离级别开启了 间隙锁 ,因此会阻塞对 SELECT FOR UPDATE 语句选中的记录进行更新、删除,阻塞对选中的范围间隙进行插入。

重做实验

删除数据库后,从第一步即可重新开始实验。

drop database test;
移动端设备除iPad Pro外,其它移动设备仅能阅读基础的文本文字。
建议使用PC或笔记本电脑,浏览器使用Chrome或FireFox进行浏览,以开启左侧互动实验区来提升学习效率,推荐使用的分辨率为1920x1080或更高。
我们坚信最好的学习是参与其中这一理念,并致力成为中文互联网上体验更好的学练一体的IT技术学习交流平台。
您可加QQ群:575806994,一起学习交流技术,反馈网站使用中遇到问题。
内容、课程、广告等相关合作请扫描右侧二维码添加好友。

狐狸教程 Copyright 2021

进入全屏