>  기사  >  데이터 베이스  >  사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

青灯夜游
青灯夜游앞으로
2021-09-23 11:09:001662검색

이 글은 네 가지 사례를 통해 MySQL의 트랜잭션 격리 수준을 이해하는 데 도움이 되기를 바랍니다.

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

많은 친구들이 MySQL의 격리 수준에 대해 항상 혼란스러워했습니다. 사실 이 문제는 어떻게 설명하느냐에 따라 전혀 어렵지 않습니다. 이론만 보면 어지러울 수 밖에 없지만, 실제 몇 가지 SQL을 통해 증명해 보면 정말 간단하다는 사실을 누구나 알 수 있을 것입니다! [관련 권장 사항: mysql 비디오 튜토리얼]

오늘 송 형제는 몇 가지 간단한 사례를 통해 MySQL의 트랜잭션 격리 수준 문제를 시연하고 싶습니다.

1. 이론

MySQL에는 4가지 트랜잭션 격리 수준이 있습니다.

  • 네 가지 격리 수준의 의미는 다음과 같습니다.
  • SERIALIZABLE

격리 수준이 직렬화인 경우 사용자는 현재 시퀀스를 하나씩 실행합니다. 이 격리 수준은 트랜잭션 간의 최대 격리를 제공합니다. 업무.

  • REPEATABLE READ

이 격리 수준에서는 트랜잭션이 시퀀스로 간주되지 않습니다. 그러나 현재 실행 중인 트랜잭션의 변경 사항은 여전히 ​​외부에 표시되지 않습니다. 즉, 사용자가 다른 트랜잭션에서 동일한 SELECT 문을 여러 번 실행하면 결과는 항상 동일합니다. (실행 중인 트랜잭션으로 인해 생성된 데이터 변경 사항은 외부 세계에서 볼 수 없기 때문입니다.)
  • READ COMMITTED

READ COMMITTED 격리 수준은 REPEATABLE READ 격리 수준보다 덜 안전합니다. READ COMMITTED 수준의 트랜잭션은 다른 트랜잭션에 의한 데이터 수정 사항을 볼 수 있습니다. 즉, 동일한 트랜잭션에 대한 여러 SELECT 문은 트랜잭션 중에 다른 트랜잭션이 해당 테이블을 수정하는 경우 다른 결과를 반환할 수 있습니다.
  • READ UNCOMMITTED

READ UNCOMMITTED는 트랜잭션 간 격리를 최소화합니다. 가상 읽기 작업과 반복 불가능한 읽기 작업을 쉽게 생성하는 것 외에도 이 격리 수준의 트랜잭션은 다른 트랜잭션이 아직 커밋하지 않은 데이터를 읽을 수 있습니다. 커밋되지 않은 변경 사항은 상위 트랜잭션에 의해 커밋된 변경 사항이 취소되어 많은 수의 데이터 변경이 발생합니다.
  • MySQL 데이터베이스에서 기본 트랜잭션 격리 수준은 REPEATABLE READ

2입니다. SQL practice

다음으로, 몇 가지 간단한 SQL을 통해 독자들에게 위의 이론을 검증하겠습니다.

2.1 격리 수준 확인

다음 SQL을 통해 데이터베이스 인스턴스의 기본 전역 격리 수준과 현재 세션의 격리 수준을 확인할 수 있습니다.

MySQL8 다음 명령을 사용하여 MySQL 격리 수준을 확인하기 전에 :

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
쿼리 결과는 그림과 같습니다.

기본 격리 수준은 전역 격리 수준과 현재 세션 격리 수준 모두 REPEATABLE-READ인 것을 확인할 수 있습니다.

MySQL8부터 다음 명령을 사용하여 MySQL 기본 격리 수준을 확인하세요

:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

키워드만 변경되었으며 다른 모든 사항은 동일합니다. 사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

격리 수준은 다음 명령을 통해 수정할 수 있습니다(개발자는 수정 시 전역 격리 수준을 수정하지 않고 현재 세션 격리 수준을 수정하는 것이 좋습니다).

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

위 SQL은 현재 세션의 데이터베이스 격리 수준을 나타냅니다. UNCOMMITTED로 설정됩니다. 설정이 성공한 후 격리 수준을 다시 쿼리하여 그림 1-2와 같이 현재 세션의 격리 수준이 변경되었는지 확인합니다.

현재 세션의 격리 수준을 수정하고 세션을 변경하면 격리 수준이 기본 격리 수준으로 돌아가므로 테스트할 때 현재 세션의 격리 수준만 수정하면 됩니다.

2.2 READ UNCOMMITTED

2.2.1 테스트 데이터 준비사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

READ UNCOMMITTED는 가장 낮은 격리 수준입니다. 이 격리 수준은

dirty read, non-repeatable read 및 phantom read 문제가 있으므로 여기서는 이 격리를 살펴보겠습니다. 모든 사람이 이 세 가지 문제에 대해 무슨 일이 일어나고 있는지 이해할 수 있도록 합니다.

아래에 소개되어 있습니다.

먼저 다음과 같이 두 개의 사전 설정 데이터가 포함된 간단한 테이블을 만듭니다.

表的数据很简单,有 javaboy 和 itboyhub 两个用户,两个人的账户各有 1000 人民币。现在模拟这两个用户之间的一个转账操作。

注意,如果读者使用的是 Navicat 的话,不同的查询窗口就对应了不同的 session,如果读者使用了 SQLyog 的话,不同查询窗口对应同一个 session,因此如果使用 SQLyog,需要读者再开启一个新的连接,在新的连接中进行查询操作。

2.2.2 脏读

一个事务读到另外一个事务还没有提交的数据,称之为脏读。具体操作如下:

  • 首先打开两个SQL操作窗口,假设分别为 A 和 B,在 A 窗口中输入如下几条 SQL (输入完成后不用执行):

START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';
UPDATE account set balance=balance-100 where name='itboyhub';COMMIT;
  • 在 B 窗口执行如下 SQL,修改默认的事务隔离级别为 READ UNCOMMITTED,如下:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
  • 接下来在 B 窗口中输入如下 SQL,输入完成后,首先执行第一行开启事务(注意只需要执行一行即可):

START TRANSACTION;SELECT * from account;COMMIT;
  • 接下来执行 A 窗口中的前两条 SQL,即开启事务,给 javaboy 这个账户添加 100 元。

  • 进入到 B 窗口,执行 B 窗口的第二条查询 SQL(SELECT * from user;),结果如下:

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

可以看到,A 窗口中的事务,虽然还未提交,但是 B 窗口中已经可以查询到数据的相关变化了。

这就是脏读问题。

2.2.3 不可重复读

不可重复读是指一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读。具体操作步骤如下(操作之前先将两个账户的钱都恢复为1000):

  1. 首先打开两个查询窗口 A 和 B ,并且将 B 的数据库事务隔离级别设置为 READ UNCOMMITTED。具体 SQL 参考上文,这里不赘述。

  2. 在 B 窗口中输入如下 SQL,然后只执行前两条 SQL 开启事务并查询 javaboy 的账户:

START TRANSACTION;SELECT * from account where name='javaboy';COMMIT;

前两条 SQL 执行结果如下:

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

  1. 在 A 窗口中执行如下 SQL,给 javaboy 这个账户添加 100 块钱,如下:
START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';COMMIT;

4.再次回到 B 窗口,执行 B 窗口的第二条 SQL 查看 javaboy 的账户,结果如下:

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

javaboy 的账户已经发生了变化,即前后两次查看 javaboy 账户,结果不一致,这就是不可重复读

和脏读的区别在于,脏读是看到了其他事务未提交的数据,而不可重复读是看到了其他事务已经提交的数据(由于当前 SQL 也是在事务中,因此有可能并不想看到其他事务已经提交的数据)。

2.2.4 幻象读

幻象读和不可重复读非常像,看名字就是产生幻觉了。

我举一个简单例子。

在 A 窗口中输入如下 SQL:

START TRANSACTION;insert into account(name,balance) values('zhangsan',1000);COMMIT;

然后在 B 窗口输入如下 SQL:

START TRANSACTION;SELECT * from account;delete from account where name='zhangsan';COMMIT;

我们执行步骤如下:

  • 首先执行 B 窗口的前两行,开启一个事务,同时查询数据库中的数据,此时查询到的数据只有 javaboy 和 itboyhub。

  • 执行 A 窗口的前两行,向数据库中添加一个名为 zhangsan 的用户,注意不用提交事务。

  • 执行 B 窗口的第二行,由于脏读问题,此时可以查询到 zhangsan 这个用户。

  • 执行 B 窗口的第三行,去删除 name 为 zhangsan 的记录,这个时候删除就会出问题,虽然在 B 窗口中可以查询到 zhangsan,但是这条记录还没有提交,是因为脏读的原因才看到了,所以是没法删除的。此时就产生了幻觉,明明有个 zhangsan,却无法删除。

这就是幻读

看了上面的案例,大家应该明白了脏读不可重复读以及幻读各自是什么含义了。

2.3 READ COMMITTED

和 READ UNCOMMITTED 相比,READ COMMITTED 主要解决了脏读的问题,对于不可重复读和幻象读则未解决。

将事务的隔离级别改为 READ COMMITTED 之后,重复上面关于脏读案例的测试,发现已经不存在脏读问题了;重复上面关于不可重复读案例的测试,发现不可重复读问题依然存在。

上面那个案例不适用于幻读的测试,我们换一个幻读的测试案例。

还是两个窗口 A 和 B,将 B 窗口的隔离级别改为 READ COMMITTED

然后在 A 窗口输入如下测试 SQL:

START TRANSACTION;insert into account(name,balance) values('zhangsan',1000);COMMIT;

在 B 窗口输入如下测试 SQL:

START TRANSACTION;SELECT * from account;insert into account(name,balance) values('zhangsan',1000);COMMIT;

测试方式如下:

  • 首先执行 B 窗口的前两行 SQL,开启事务并查询数据,此时查到的只有 javaboy 和 itboyhub 两个用户。

  • 执行 A 窗口的前两行 SQL,插入一条记录,但是并不提交事务。

  • 执行 B 窗口的第二行 SQL,由于现在已经没有了脏读问题,所以此时查不到 A 窗口中添加的数据。

  • 执行 B 窗口的第三行 SQL,由于 name 字段唯一,因此这里会无法插入。此时就产生幻觉了,明明没有 zhangsan 这个用户,却无法插入 zhangsan。

2.4 REPEATABLE READ

和 READ COMMITTED 相比,REPEATABLE READ 进一步解决了不可重复读的问题,但是幻象读则未解决。

REPEATABLE READ 中关于幻读的测试和上一小节基本一致,不同的是第二步中执行完插入 SQL 后记得提交事务。

由于 REPEATABLE READ 已经解决了不可重复读,因此第二步即使提交了事务,第三步也查不到已经提交的数据,第四步继续插入就会出错。

注意,REPEATABLE READ 也是 InnoDB 引擎的默认数据库事务隔离级别

2.5 SERIALIZABLE

SERIALIZABLE 提供了事务之间最大限度的隔离,在这种隔离级别中,事务一个接一个顺序的执行,不会发生脏读、不可重复读以及幻象读问题,最安全。

如果设置当前事务隔离级别为 SERIALIZABLE,那么此时开启其他事务时,就会阻塞,必须等当前事务提交了,其他事务才能开启成功,因此前面的脏读、不可重复读以及幻象读问题这里都不会发生。

3. 总结

总的来说,隔离级别和脏读、不可重复读以及幻象读的对应关系如下:

隔离级别 脏读 不可重复读 幻象读
READ UNCOMMITTED 允许 允许 允许
READ COMMITED 不允许 允许 允许
REPEATABLE READ 不允许 不允许 允许
SERIALIZABLE 不允许 不允许 不允许

性能关系如图:

사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.

好了,这篇文章就和小伙伴们先说这么多,大家不妨写几行 SQL 试一试。

更多编程相关知识,请访问:编程视频!!

위 내용은 사례를 통해 MySQL의 트랜잭션 격리 수준을 이해할 수 있습니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명:
이 기사는 juejin.cn에서 복제됩니다. 침해가 있는 경우 admin@php.cn으로 문의하시기 바랍니다. 삭제