数据库隔离级别理解MYSQL为例

最新推荐文章于 2022-01-13 13:44:51 发布

郭二关

最新推荐文章于 2022-01-13 13:44:51 发布

阅读量673

点赞数 3

分类专栏：数据库事务文章标签： MYSQL事务数据库事务隔离级别幻读

本文链接：https://blog.csdn.net/u011125703/article/details/89049098

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

事务

1 篇文章 0 订阅

订阅专栏

数据库事务的隔离级别有4种，由低到高分别为READ UNCOMMITTED 、READ COMMITTED 、REPEATABLE READ 、SERIALIZABLE。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。下面通过事例一一阐述它们的概念与联系。

创建数据库表

CREATE TABLE `t_user` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(255) DEFAULT '',

  `amount` double(16,2) DEFAULT '0.00',

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;





INSERT INTO `t_user`(`id`, `name`, `amount`) VALUES (1, 'a', 100.00);

INSERT INTO `t_user`(`id`, `name`, `amount`) VALUES (2, 'b', 100.00);

创建好示例数据库表之后，我们可以通过以下语句查询事务隔离级别：

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

默认情况下，MYSQL会返回如下结果：

其中，@@GLOBAL.tx_isolation 为全局隔离级别， @@tx_isolation为当前Session隔离级别

可以看出MYSQL 的默认隔离级别为 REPEATABLE-READ 可重复读级别。

1，READ UNCOMMITTED

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

示例代码：

-- 会话1（注意，会话Session指的是一次数据库会话，如你在Navicat中打开两个查询窗口，则为两个会话）
set SESSION transaction isolation LEVEL READ UNCOMMITTED;  --1.1

START TRANSACTION;   --1.2

SELECT * from t_user;  --1.3

COMMIT; --1.4

-- 会话2


START TRANSACTION; --2.1

UPDATE t_user SET amount = amount-100 where NAME = 'a'; --2.2

SELECT * from t_user; --2.3

UPDATE t_user SET amount = amount+100 where NAME = 'b'; --2.4

COMMIT; --2.5

1)，先将会话1中的隔离级别改为 READ UNCOMMITTED (1.1)。开启事务，执行查询1.3，此时查询结果为：

2)，在会话2中开启事务，然后执行 2.2, 执行2.3查询数据得到结果：

3)，此时在会话1中再次执行1.3，结果如下：

会发现，在会话1中执行的更新事务没有提交，但是会话2中的查询能够查询出来，一个事务可以读取另一个未提交事务的数据意思在此。

脏读

隔离级别READ UNCOMMITTED会导致脏读的发生。什么是脏读呢？上面例子中，会话2执行完2.2语句之后，会话1读取到的是2.2修改之后的结果，假设现在会话2出错回滚了事务，那么会话1中的数据就是“脏数据”。读取到事务回滚前的数据，即为脏读。

那怎么解决脏读呢？MYSQL 第二个事务隔离级别READ COMMITTED 读提交，就是为了解决这个脏读问题。

2.READ COMMITTED

READ COMMITTED 读提交，就是一个事务要等另一个事务提交后才能读取数据。

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

示例：

1)，将会话1中的Session的事务隔离级别改为 READ COMMITTED

set SESSION transaction isolation LEVEL READ COMMITTED;

重复上述3步，此时第三步查询结果为：

可以看到，在会话1中的事务没有提交的情况下，会话2将不会读取到会话1中更新的结果！

2)，执行完成会话1中的语句，提交事务后，再次执行1.3，结果为：

当会话1中的事务提交后，会话2事务中的查询语句才能够查询到会话1中更新的内容。

不可重复读

在上面的例子中，会话2提交完事务之后，假如会话1重新读取了这个数据，会发现这个数据已经被修改了。在同一个事务中，读取两次相同的数据，得到不同的结果，此为不可重复读。

那怎么解决可能的不可重复读问题？Repeatable read ！

3.REPEATABLE READ

REPEATABLE READ 可重复读，就是在当某一个事务开始后，读取到的数据，不再允许修改操作

实例：

1），将会话1中的Session的事务隔离级别改为 REPEATABLE READ

set SESSION transaction isolation LEVEL REPEATABLE READ;

2），重复上述的两步，再在会话1中执行1.3，结果为：

3，执行1.4，结束会话1中的事务后，再次执行1.3，结果为：

由此可见，在隔离级别REPEATABLE READ 下开启事务后，到该事务结束之前，这个会话查询到的内容都是更新前的，不会受到别的事务更新数据的影响。

隔离级别REPEATABLE READ可以解决不可重复读问题。但是需要注意的是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。（因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作）。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

-- 对幻读的误解！

上面这段事例是摘下面引用博客的，当时没有仔细验证，其实这段是错误的！网上搜索幻读大多也是这种表述，大致意思是另一个事务如果插入了一条记录，那么其他事务再查询会查询到这条记录。下面我们做个简单的还原实验。

1，按照文章开始创建的表，那初始数据如下：

2，准备两个会话窗口，第一个会话开启事务，执行语句:

BEGIN; -- begin 和 start TRANSACTION 是一样的

select * from t_user where name = 'a';

结果如下：

3，在第二个会话窗口执行语句，插入一条name='a'的纪录


INSERT INTO t_user VALUES(4,'a',100);

4，再回到第一个窗口执行重新查询一下，结果是：

5，第一个窗口提交事务之后，重新查询，才能够查出刚才在另一个事务插入的纪录

COMMIT;

select * from t_user where name = 'a';

从这个结果可以看出来，在隔离级别RR(Repeatable Read)下，一个事务能查询出另一个事务插入的纪律这个理解是错误的~

幻读导致的问题

那么既然幻读不会造成上面的问题，那实际上幻读会导致什么问题呢？举个例子，假如我现在要实现一个注册的功能，userName字段要唯一，用户发起一个注册请求，后台先查询一下是否已经存在相同的userName，有则返回异常，无则插入注册信息，返回注册成功。那么由此引出一个问题，如果有两个事务在同时执行这段逻辑，会不会出现什么问题呢？我们来模拟一下。先建表

CREATE TABLE `t_user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) NOT NULL DEFAULT '',
  `pwd` varchar(256) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_userName` (`userName`) USING BTREE
) ENGINE=InnoDB;

同样准备两个会话A,B，按下面步骤执行的出结果