# 再来给你解释一次事务隔离级别的实现
折腾事务隔离级别有几天了, 但是自己始终有些在雾里看花的感觉, 因此这篇旨在弄懂事务隔离级别的实现, 让自己知其然也知其所以然.
# 本文中案例使用的表结构和数据如下
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `t`
VALUES
(0, 0, 0),
(5, 5, 5),
(10, 10, 10),
(15, 15, 15),
(20, 20, 20),
(25, 25, 25);
# read uncommitted
场景案例
# 事务A的update或insert未提交, session B能看到
# update执行未提交
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | select * from t where id=5 result: (5,5,5) | |
| T3 | update t set d=100 where id=5; | |
| T4 | select * from t where id=5 result: (5,5,100) | |
| T5 | commit/rollback |
# insert执行未提交
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | select * from t where id>0 and id<10 result: (5,5,5) | |
| T3 | insert into t values(6,6,6); | |
| T4 | select * from t where id>0 and id<10 result: (5,5,5),(6,6,6) | |
| T5 | commit/rollback |
# 事务A改变了行数据, 事务B的update被阻塞
# insert数据
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | select * from t where id>0 and id<10 result: (5,5,5) | |
| T3 | insert into t values(6,6,6); | |
| T4 | select * from t where id>0 and id<10 result: (5,5,5),(6,6,6) update t set d=200 where d=6; (Affected row: 0) update t set d=200 where id=6 (执行阻塞) | |
| T5 | commit/rollback |
# delete数据
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | select * from t where id>0 and id<10 result: (5,5,5) | |
| T3 | delete from t where id=5; | |
| T4 | select * from t where id>0 and id<10 result: 0 rows update t set d=200 where d=5; (执行阻塞) update t set d=200 where id=5 (执行阻塞) | |
| T5 | commit/rollback |
# 场景说明(实现原理)
以上场景案例中有两类场景, 对于第一类事务A更新了数据, 但未提交, 事务B也能看到大家都容易理解, 与read uncommitted字面意思一样, 其实现原理是在读未提交隔离级别下, 普通select能读到buffer pool或undo log中未提交的内容, update一定是先读后写并且是当前读, 因此会出现事务B update不到事务A insert的数据(上面案例中update t set d=200 where d=6;affected row 0就是如此), 但insert会在索引树上插入新节点, 所以用index或primary index做条件的update会与未提交事务的锁发生阻塞(上面案例中update t set d=200 where id=6即此情况).
# read committed
场景案例
读提交的场景案例都是常见的, 在这里不一一列举了.
# 实现原理
读提交隔离级别下, 普通select是读取行中最新数据, 在执行select语句之前构建一个视图(即当前时刻数据库数据最新的快照版本), 执行完后立即释放, 最容易出现的问题是不可重复读, 而update/insert/delete都是基于当前读的.
# repeatable read
场景案例
可重复读的案例也是最常见的, 以下只举例出较难理解的场景.
# 为什么update会被阻塞?
| session A | session B | session C | |
|---|---|---|---|
| T1 | begin; | ||
| T2 | insert into t values(6,6,6) (ok) | ||
| T3 | update t set d=200 where d=7 (block) | ||
| T4 | update set d=200 where id=7 (ok) | ||
| T5 | rollback; |
# 反证上面案例
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | update t set d=200 where d=7 (Affected row: 0) | |
| T3 | insert into t values(6,6,6); (block) | |
| T4 | rollback; |
# 在事务第一句select时构建一致性视图
| session A | session B | |
|---|---|---|
| T1 | begin; | |
| T2 | begin; | |
| T3 | insert into t values(100,100,100); (ok) | |
| T4 | commit; | |
| T5 | select * from t; (结果中包含(100,100,100)这一行) |
# 场景说明/实现原理
可重复读隔离级别下, 会在事务开始后的第一句select构建整个事务使用的一致性视图(上面案例中T5时刻查询到结果即可说明), update/delete会加next-key lock, 扫描到的索引对象都会加锁(即上面案例中update被阻塞的原因).