사례를 통해 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

2.1 격리 수준 확인

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

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

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

:

SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation;

키워드만 변경되었으며 다른 모든 사항은 동일합니다.

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

2.2 READ UNCOMMITTED

2.2.1 테스트 데이터 준비

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;），结果如下：

可以看到，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 执行结果如下：

3. 在 A 窗口中执行如下 SQL，给 javaboy 这个账户添加 100 块钱，如下：

START TRANSACTION;
UPDATE account set balance=balance+100 where name='javaboy';COMMIT;

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

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;

测试方式如下：