|
4. 4가지 격리 수준의 SQL 시연
mysql 버전: 5.6
스토리지 엔진: InnoDB
Tool: navicat
테이블 생성 명령문: CREATE TABLE `tb_bank` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(16) COLLATE utf8_bin DEFAULT NULL,
`account` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (1, '小明', 1000); 로그인 후 복사 1 sql을 통한 시연------read-uncommitted Dirty read (2) read-uncommit으로 인한 더티 읽기 소위 더티 읽기(dirty read)는 두 트랜잭션 중 하나가 다른 트랜잭션의 커밋되지 않은 데이터를 읽을 수 있음을 의미합니다.
시나리오: 세션1은 200위안을 이체하려고 하고, 세션2는 100위안을 이체하려고 합니다. 기본은 1000입니다. 성공적으로 완료하려면 올바른 결과가 900위안이어야 합니다. 그러나 어떤 이유로 session2가 트랜잭션 롤백으로 전환되었다고 가정합니다. 이때 정확한 결과는 800위안이 되어야 합니다. 데모 단계:
① 두 개의 새 세션(navicat에서 두 개의 쿼리 창과 mysql 명령줄에서 두 개의 창으로 표시되는 세션)을 생성하고 각각 실행합니다. select @@tx_isolation;//查询当前事务隔离级别
set session transaction isolation level read uncommitted;//将事务隔离级别设置为 读未提交 로그인 후 복사 ② 두 세션 모두 트랜잭션을 엽니다 start transaction;//开启事务 로그인 후 복사 ③ Session1 및 session2: 두 작업이 수행되기 전 계정 잔고가 1000 select * from tb_bank where id=1;//查询结果为1000 로그인 후 복사 4임을 증명합니다. session2: 이때, session2의 업데이트가 먼저 수행되는 것으로 가정합니다. update tb_bank set account = account + 100 where id=1; 로그인 후 복사 ⑤ session1: session2가 커밋되기 전에 session1이 실행을 시작합니다. select * from tb_bank where id=1;//查询结果：1100 로그인 후 복사 ⑥ session2: 어떤 이유로 인해 전송이 실패하고 트랜잭션이 롤백되었습니다. rollback;//事务回滚
commit;//提交事务 로그인 후 복사 7 이때 session1은 외부로 전송이 시작되고 session1은 ⑤의 쿼리 결과 1100이 올바른 데이터라고 생각합니다. update tb_bank set account=1100-200 where id=1;
commit; 로그인 후 복사 8Session1과 session2 쿼리 결과 select * from tb_bank where id=1;//查询结果：900 로그인 후 복사 이때 session1의 dirty reading으로 인해 최종 데이터가 일치하지 않는 것을 발견했습니다. 올바른 결과는 800이어야 합니다.
이 시점에서 더티 읽기를 방지하려면 어떻게 해야 합니까? 트랜잭션 격리를 읽기-커밋으로 한 단계 높입니다. (2) 읽기-커밋은 더티 읽기를 해결합니다. 데이터를 재설정하고 데이터를 복원합니다. to account=1000 ① 두 개의 새로운 세션을 생성하고 각각 set session transaction isolation level read committed;//将隔离级别设置为 不可重复读 로그인 후 복사 (1)의 ② ③ ④ ⑤ 단계를 반복합니다. 두 트랜잭션은 효과적으로 격리되므로 session1의 트랜잭션은 in의 트랜잭션으로 인해 발생한 데이터 변경 사항을 쿼리할 수 없습니다. 세션2. 더티 읽기(Dirty Read)는 효과적으로 방지됩니다. 2. SQL을 통해 시연 ----- 읽기-커밋 비반복 읽기 (1) 읽기-커밋 비반복 읽기 데이터를 재설정하고 계정=1000으로 데이터 복원 소위 비 -반복 읽기는 트랜잭션이 커밋되지 않은 다른 트랜잭션의 데이터를 읽을 수 없지만 제출된 데이터는 읽을 수 있음을 의미합니다. 이때 두 판독 결과가 일치하지 않습니다. 따라서 반복해서 읽을 수 없습니다. READ COMMITTED 격리 수준에서는 각 읽기에 대해 스냅샷이 다시 생성되므로 각 스냅샷이 최신입니다. 따라서 트랜잭션의 각 SELECT는 다른 커밋된 트랜잭션에 의해 변경된 내용도 볼 수 있습니다.시나리오: 세션1은 쿼리용 계정을 수행합니다. , 세션2는 100을 계정으로 이체합니다.
Session1은 계좌를 조회하고 업데이트하기 위해 트랜잭션을 엽니다. 이때, session2도 해당 계좌에 대한 트랜잭션을 열고 업데이트합니다. 올바른 결과는 session1이 트랜잭션을 시작한 후 쿼리에서 읽은 결과가 동일해야 한다는 것입니다.
① 두 개의 새 세션을 생성하고 select * from tb_bank where id=1;//查询结果为1000，这说明 不可重复读 隔离级别有效的隔离了两个会话的事务。 로그인 후 복사 ② session1과 session2를 각각 트랜잭션을 열도록 설정합니다. set session transaction isolation level read committed; 로그인 후 복사 ③ session1 첫 번째 쿼리: start transaction; 로그인 후 복사 ④ session2 업데이트: select * from tb_bank where id=1;//查询结果：1000 로그인 후 복사 ⑤ session1 두 번째 쿼리: update tb_bank set account = account+100 where id=1;
select * from tb_bank where id=1;//查询结果：1100 로그인 후 복사 쿼리를 살펴보는 중 결과를 보면, 트랜잭션 개시 시 session1의 반복 읽기 결과가 일치하지 않음을 알 수 있어, 읽기 커밋 트랜잭션 격리 수준이 반복 불가능 읽기임을 알 수 있습니다. 분명히 이 결과는 우리가 원하는 결과가 아닙니다. (2) 반복 읽기 데이터를 재설정하여 데이터를 계정=1000으로 복원① 두 개의 새 세션을 생성하고 각각
select * from tb_bank where id=1;//查询结果：1100。和③中查询结果对比，session1两次查询结果不一致。 로그인 후 복사
(1)의 ② ③ ④ ⑤ session1을 두 번째로 반복합니다. 쿼리: set session transaction isolation level repeatable read; 로그인 후 복사
반복 읽기 격리 수준에서는 다중 읽기 결과가 다른 트랜잭션의 영향을 받지 않는 것을 결과에서 알 수 있습니다. 반복해서 읽을 수 있습니다. 여기서 의문이 생깁니다. session1에서 읽은 결과는 여전히 session2의 업데이트 전 결과입니다. session1에서 100을 계속 전송하면 올바른 결과인 1200을 얻을 수 있습니까? 계속 작업: ⑥ 세션 1을 100으로 전송:
select * from tb_bank where id=1;//查询结果为：1000 로그인 후 복사
여기서 속인 것 같아요, 잠금, 잠금, 잠금. session1의 업데이트 문이 차단되었습니다. session2의 업데이트 문이 커밋된 후에만 session1의 실행이 계속될 수 있습니다. 세션의 실행 결과는 1200입니다. 이때 MVCC 메커니즘은 반복 읽기의 격리 수준에서 사용되며 선택 작업은 버전 번호를 업데이트하지 않기 때문에 세션 1은 1000+100을 사용하여 계산되지 않는 것으로 확인되었습니다. 이지만 스냅샷 읽기(기록 버전)입니다. 삽입, 업데이트 및 삭제는 현재 읽은 버전(현재 버전)인 버전 번호를 업데이트합니다. 3. SQL을 통해 시연----반복 읽기의 팬텀 읽기 在业务逻辑中，通常我们先获取数据库中的数据，然后在业务中判断该条件是否符合自己的业务逻辑，如果是的话，那么就可以插入一部分数据。但是mysql的快照读可能在这个过程中会产生意想不到的结果。
场景模拟：
session1开启事务，先查询有没有小张的账户信息，没有的话就插入一条。这是session2也执行和session1同样的操作。 准备工作：插入两条数据 INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (2, '小红', 800);
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (3, '小磊', 6000); 로그인 후 복사 （1）repeatable-read的幻读 ① 新建两个session都执行 set session transaction isolation level repeatable read;
start transaction;
select * from tb_bank;//查询结果：（这一步很重要，直接决定了快照生成的时间） 로그인 후 복사 结果都是： 
② session2插入数据
INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (4, '小张', 8000);
select * from tb_bank; 로그인 후 복사 
结果数据插入成功。此时session2提交事务 ③ session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据 select * from tb_bank; 로그인 후 복사 
结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。继续执行： INSERT INTO `demo`.`tb_bank`(`id`, `name`, `account`) VALUES (4, '小张', 8000); 로그인 후 복사 
结果插入失败，提示该条已经存在，但是我们查询里面并没有这一条数据啊。为什么会插入失败呢？ 因为①中的select语句生成了快照，之后的读操作（未加读锁）都是进行的快照读，即在当前事务结束前，所有的读操作的结果都是第一次快照读产生的快照版本。疑问又来了，为什么②步骤中的select语句读到的不是快照版本呢？因为update语句会更新当前事务的快照版本。具体参阅第五章节。 （2）repeatable-read利用当前读解决幻读重复（1）中的①②
③ session1进行插入
插入之前我们先看一下当前session1是否有id=4的数据 select * from tb_bank; 로그인 후 복사 结果session1中没有该条记录，这时按照我们通常的业务逻辑，此时应该是能成功插入id=4的数据。 select * from tb_bank lock in share mode;//采用当前读 로그인 후 복사 结果：发现当前结果中已经有小张的账户信息了，按照业务逻辑，我们就不在继续执行插入操作了。
这时我们发现用当前读避免了repeatable-read隔离级别下的幻读现象。
4、serializable隔离级别
在此级别下我们就不再做serializable的避免幻读的sql演示了，毕竟是给整张表都加锁的。
五、当前读和快照读
本想把当前读和快照读单开一片博客，但是为了把幻读总结明白，暂且在本章节先简单解释下快照读和当前读。后期再追加一篇MVCC，next-key的博客吧。。。
1、快照读：即一致非锁定读。
① InnoDB存储引擎下，查询语句默认执行快照读。
② RR隔离级别下一个事务中的第一次读操作会产生数据的快照。
③ update，insert，delete操作会更新快照。
四种事务隔离级别下的快照读区别：
① read-uncommitted和read-committed级别：每次读都会产生一个新的快照，每次读取的都是最新的，因此RC级别下select结果能看到其他事务对当前数据的修改，RU级别甚至能读取到其他未提交事务的数据。也因此这两个级别下数据是不可重复读的。
② repeatable-read级别：基于MVCC的并发控制，并发性能极高。第一次读会产生读数据快照，之后在当前事务中未发生快照更新的情况下，读操作都会和第一次读结果保持一致。快照产生于事务中，不同事务中的快照是完全隔离的。
③ serializable级别：从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。Serializable隔离级别下，读写冲突，因此并发度急剧下降。（锁表，不建议使用）
2、当前读：即一致锁定读。
如何产生当前读
① select ... lock in share mode
② select ... for update
③ update，insert，delete操作都是当前读。
读取之后，还需要保证当前记录不能被其他并发事务修改，需要对当前记录加锁。①中对读取记录加S锁 (共享锁)，②③X锁 (排它锁)。
3、疑问总结
① update，insert，delete操作为什么都是当前读？
简单来说，不执行当前读，数据的完整性约束就有可能遭到破坏。尤其在高并发的环境下。
分析update语句的执行步骤：update table set ... where ...;
InnoDB 엔진은 먼저 where 쿼리를 수행하고 쿼리된 결과 세트는 첫 번째 항목에서 시작하여 현재 읽기를 실행한 다음 업데이트 작업을 수행하고 두 번째 데이터 조각을 읽고 업데이트 작업을 수행합니다. 업데이트가 실행되면 현재 읽기가 수반됩니다. 삭제도 마찬가지입니다. 결국 데이터를 삭제하기 전에 먼저 찾아야 합니다. 삽입은 약간 다릅니다. 삽입 작업을 실행하기 전에 고유 키 확인을 수행해야 합니다. [관련 권장 사항: MySQL 튜토리얼]
|
![[웹 프런트엔드] Node.js 빠른 시작](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
