MySQL事务隔离

DevLGQ

DataBaseInterviewMySQL

发布于：2020年9月28日

次浏览

字数：3.9k字

时长：15分钟

MySQL事务隔离

TPS: Transaction Per Second, 事务每秒处理数。

解决的是并发编程中原子性的问题。

事务隔离SQL演示

-- 创建测试表 默认都是自动提交事务
create table A(
    id int not null auto_increment,
    balance decimal(10, 4) not null ,
    primary key (id)
);
show create table A;

-- 事务隔离
-- console 1
start transaction ;
insert into A (balance) values(0);
-- console 2
select * from huge.A; -- 看不到, 因为 console1 还没提交, 要2者都提交, 其它事务才能看到数据
-- console 1/2
commit;

-- 防止脏读 其它事务看到该事务阶段性的变更
-- console 1/2 开启事务
start transaction ;
-- console 1
select balance from A where id=1;
update A set balance=100 where id=1;
select * from A; -- balance=100
-- console 2
select * from A; -- 0 如果看到是100, 就是 脏读 Dirty Read
update A set balance=100 where id=1; -- 卡住, 因为被 console1 锁住了 行锁
insert A(balance) values(10000); -- 可以插进去, 说明没有锁住整张表
-- console 1/2
commit;

-- 防止幻读 指看到了一个不存在的数据(未提交新创建的行)
-- console 1/2
start transaction;
-- console 2
insert into A(balance) values (100000);
-- console 1
select * from A; -- 没有读到新插入的数据, 防止了 幻读(Phantom Read).
-- console 2
delete from A where id=2;
-- console 1/2
rollback;

-- 解决更新2个账户问题
-- 方案 1
-- console 1
update A set balance = balance + 100 where id = 1;
-- console 2
update A set balance = balance + 100 where id = 1;

-- 方案 2 for update, 使用 行级锁(排斥锁)
-- console 1
update A set balance = balance + 100 where id = 1;
-- console 2
select balance from A where id = 1 for update ; -- 会等待其它事务提交了之后再读取到该数据

-- 方案 3 使用乐观锁
-- 添加一个 版本号
alter table A add column `version` int default 0 not null ;
-- console 1
-- 先查询 version
select version from A where id = 1;
update A set balance = balance + 100, version = 1 where id = 1 and version = 0;
-- console 2 要检查版本
update A set balance = balance + 100, version = 1 where id = 1 and version = 0;

数据库三范式

第一范式：强调列的原子性，即数据库的每一列都是不可分割的原子数据项。

第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。

第三范式：任何非主属性不依赖其它非主属性。

事务四大特征

原子性(Atomic): 操作不可再分. start/rollback/commit 控制
一致性(Consistency): 事务的执行前后数据关系不被破坏. 例如: 账户的钱不被算错.
- 工具: 锁.
  - 共享锁(读锁): 允许其它(select for share)read, 不允许write(select for update/update)
  - 互斥锁(写锁): 不允许写(select for share, select for update)
  - 从范围上, 表锁/行锁.
  - select * from table for ... 触发表级的共享锁.
  - select * from table where id = 1 for update 触发行级的共享锁.
隔离性(Isolation)
- 多个事务执行隔离(支持并发).
- 默认级别是RR(Repeatable Read, 可重复读), 没有脏读, 幻读
  - 比他弱: Read Uncommitted, 可以读到未提交的数据, 允许脏读; Read Committed, 只能读到提交的数据, 不能脏读, 可以幻读.
  - 比他强: Serializable, 串行化, 读取每行数据都上锁.
- MVCC: Multiple Version Concurrency Control(多版本并发控制)
持久性(Durability): 没有100%的持久性. 事务提交后, 变更可以持久化到存储上, DBMS应该提供适当的冗余, 使其能耐住系统的故障, 断电后不会丢失. 例如InnoDB, 会对页面所有的修改操作, 写入一个专门的文件, 并在数据库启动时, 从此文件恢复操作, 也就是redo log file. 没有100%的, MySQL也不能保证磁盘坏不坏.

事务隔离级别

-- 查看事务隔离级别
-- MySQL5
select @@tx_isolation;
-- MySQL8
select @@transaction_isolation;
-- mysql 默认隔离级别 REPEATABLE-READ
-- 最低隔离级别 未提交读 允许未提交事务的数据被读
-- read uncommitted 未提交读
-- read committed 已提交读 oracle 默认隔离级别
-- repeatable read 可重复读 mysql 默认隔离级别
-- serializable 串行化
-- 设置本session的事务隔离级别 未提交读
set session transaction isolation level read uncommitted;

更新丢失

Lost update. MySQL所有事务隔离级别在数据库层面上均可避免.

取款事务	存款事务
开始事务	开始事务
查询转账余额100元
	查询转账余额100元
	存入20元, 余额变为120元
	提交事务
取出10元, 余额改为90元
回滚事务, 恢复余额为100元	更新丢失

脏读

模拟脏读, 设置级别为未提交读.

-- session 1
start transaction;
update A set balance = balance - 100 where id = 4;
select * from A where id = 4; -- 900
-- session1 由于异常, 回滚了
rollback; -- 1000

-- session 2
start transaction;
select * from A where id = 4; -- 900 但是session 1其实还没有提交
-- 这个时候先账户存入钱
update A set balance = 900 + 200 where id = 4;

READ-COMMIITED 事务隔离级别以上可避免. 一个事务读到另一个事务未提交的数据.

不可重复读

模拟不可重复读, 设置级别为已提交读.

-- session 1
start transaction;
select * from A where id = 1; -- 在一个事务, 多次读取, 结果不一致
commit;
-- session 2
start transaction;
update A set balance = balance + 200;
select * from A where id = 1;
commit;

REPEATABLE-READ 事务隔离级别以上可以避免. 事务A多次读取数据, 事务B对数据更新并提交了, 导致事务A多次读取数据时结果不一致.

幻读

模拟幻读, 设置级别为已提交读.

-- session 1
start transaction;
update A set balance = 3000 where id = 9;
-- session 2
start transaction;
insert into `A` (balance) values (1005);
-- session 1 当前读 阻塞
select * from A lock in share mode ;
-- session 2
commit;
-- seesion 1
select * from A;

SERIALIZABLE 事务级别可避免. 事务A读取与搜索条件先匹配的若干行, 事务B以插入或删除行的方式来修改事务A的结果集, 导致事务A看起来像出现幻觉似的.

理论上, 不可重复读的隔离级别是做不到避免幻读的, MySQL通过间隙锁解决了该问题.

事务隔离级别总结

事务隔离级别	更新丢失	脏读	不可重复读	幻读
未提交读	避免	发生	发生	发生
已提交读	避免	避免	发生	发生
可重复读	避免	避免	避免	发生
串行化	避免	避免	避免	避免

在一个事务内, 不可重复读侧重于对同一数据的修改, 而幻读则侧重于新增或删除. 出于性能问题的考虑, 具体按照业务来选择事务隔离.

当前读

读取到的是最新版本, 本质上就是加了锁的增删改查语句, 不管是共享锁还是排斥锁.

select ... lock in share mode(共享锁，可以上多个), select ... for update(排斥锁，等释放才能锁)
update, delete, insert(排斥锁)

先读取最新的数据, 进行上锁, 然后再执行更新操作.

快照读

读到的是历史版本, 不加锁的非阻塞读, 前提是事务级别不是SERIALIZABLE, SERIALIZABLE是串行读的, 会导致快照读也会变成当前读. 快照读是为了提高并发性能.

是基于多版本并发控制, 也就是MVCC(Multi-Version Concurrency Control), 可以认为MVCC是行级锁的变种, 但在很多情况下避免了加锁操作, 因此开销更低. 因为基于多版本, 所以快照读可能读到的并不是数据的最新版本而是历史版本.

快照读和当前读验证

-- session 1 READ-COMMITTED
start transaction;
-- 添加一条记录
insert into `A` (balance) values (1008);
commit;

-- session 2 READ-COMMITTED
start transaction;
-- 快照读
select * from `A`;
-- 当前读
select * from `A` lock in share mode;
commit;

-- 结论: 在 RC 级别下, 当前读 和 快照读 读到的版本是一致的

-- session 3 REPEATABLE-READ
start transaction;
update account_innodb set money = 300 where id = 1;
commit;

-- session 4 REPEATABLE-READ
start transaction;
-- 快照读 结果还是 600 和没修改前的结果是一致的
select * from account_innodb where id = 1;
-- 当前读 300 如果有其它事务修改了, 而且没有提交就会阻塞, 提交的话就不会阻塞.
select * from account_innodb where id = 1 lock in share mode;
commit;

-- 在 RR 级别下, 有可能读到历史版本. 如果在创建快照前就更新了, 读到的数据就是新的!!! 就是说创建快照的时机, 决定了读取数据的版本!

InnoDB可重复读隔离级别下如何避免幻读

两个方面

表象 : 快照读(非阻塞读) – 伪 MVCC
内在 : next-key锁(行锁 + gap锁)

RC, RR级别下InnoDB的非阻塞读实现

数据行里的 DB_TRX_ID(最近一次对本行记录的修改的事务ID), DB_ROLL_PTR(rollback, pointer, 回滚指针), DB_ROW_ID(行ID, InnoDB表没有主键和唯一键的时候会自动创建隐藏ID, 就是这个)字段
undo log(当对数据做了变更时就会产生undo记录), 此记录中存储的是旧数据, 当一个旧事务需要取数据时, 为了能读取到老版本的数据, 就要顺着undo链找到满足其可见性的记录.
- insert undo log : 事务对insert新记录产生的, 只在事务回滚时需要, 并且在事务提交后就可以立即丢弃掉.
- update undo log : 事务对delete或者update操作时产生的, 不仅在事务回滚时需要, 快照读也需要. 只有当数据库所使用的快照中不涉及该日志记录, 对应的回滚日志才会被线程删除.

下图是简易版, 事务对行记录的更新过程

事务A修改 Field2 的值, 从12 -> 32 , 流程如下:

排它锁锁定当前行.
拷贝一份修改前的值到 undo log 里, 修改Field2值, 填写事务ID.
使用回滚指针(DB_ROLL_PTR)指向 undo log 中修改前的行.
假设之后有其它事务使用快照读来读取该记录, 这时候对应的undo log还没有清除, 此时又有一个事务对数据进行修改, 打算把Field3 中的 13 -> 45. 这时候会又多一条undo log记录. 按照修改时间顺序, 从近到远, 通过DB_ROLL_PTR连接起来, 数据的多个版本就是这样实现的.

Read View : 做可见性判定. 当做快照读select时, 会针对查询的数据创建出一个read view来决定当前事务能看到的是哪个版本的数据, 有可能是当前最新版本的数据, 也可能只允许看 undo log 里面某个版本的数据. 遵循可见性算法, 将要查看数据事务的 DB_TRX_ID 取出来与系统其它活跃事务ID做对比, 如果大于或等于这些ID的话, 就通过 DB_ROLL_PTR 指针去取出 undo log 上一层的 DB_TRX_ID , 直到小于这些活跃事务ID为止的版本. 这样就保证了获取到的版本是当前最稳定的版本(事务ID越大, 一般越新).

从上图可以看到当前数据记录的Field2值为32，在undo log中还有一个版本的数据分别是13，在mysql默认的隔离级别可重复读中，就可以利用undo log中保存的版本记录，做到读写并发，这种就较多MVCC。在可重复读级别下，同一个事务中多次读取操作，都是使用的第一次select语句开始时的DB_TRX_ID，做到可重复读。针对普通的查询操作，例如：select c1 from t where c2='' 这种都是快照读，快照读就是读取某个数据版本；像select c1 from t where c2 for update这种属于当前读，会读取当前最新的版本。

next-key 锁

行锁
Gap 锁 : 索引中插入新纪录的空隙. gap lock(间隙锁), 即锁定一个范围, 但不包括记录本身. 是为了防止同一事务的多次当前读出现幻读的情况. 在 RC 以及更低事务隔离级别下没有Gap锁. 这也是 RC 下无法避免幻读的原因, 而在 RR 和 serializable 级别下默认都支持的.

在 RR 级别下, 无论是增删改查, 当前读若用到主键或者唯一键会用到 gap 锁不?

第一种情况, 如果 where 条件全部命中, 也就是记录具有唯一性, 则不会使用 Gap 锁, 只会加行锁, 这样就能防止其它事务对其的修改.

那为什么图上密集索引也要加上排他锁? 如果一个并发的 sql, 通过主键索引来更新, update tb set id=90 where name=d, 要是没有锁住密集索引的话, 那么这个并发的 update 就感知不到 delete 语句的存在. 这样就违背了同一记录的更新或删除需要串行执行的约束.

验证, 下图是模拟的所有数据.

-- session 1/2
start transaction;
-- session 1
-- 删除 id 17 因为是命中的, 所以不会加 gap锁
delete from A where id = 17;
-- session 2
-- 插入 id 18 数据 可以插入
insert into A(id, balance, version) values (18, 1800, 0);
-- session 1/2
rollback;

第二种情况, 如果 where 条件部分命中或者全不命中, 则会加 Gap 锁.

-- session 1/2 不命中的情况
start transaction;
-- session 1
-- 删除 id 9 不存在的记录
delete from A where id = 9;
-- session 2
-- 插入 id 10 数据, 也是不存在的, 会阻塞.
insert into A(id, balance, version) values (10, 1800, 0);

-- session 1/2 部分命中的情况
start transaction;
-- session 1
select * from A where id in(5, 7, 10) lock in share mode;
-- session 2
-- 插入小于5的数据，没有阻塞
insert into A(id, balance, version) values (2, 1800, 0);
-- 插入5~10之间的数据, 会被阻塞
insert into A(id, balance, version) values (9, 1888, 0);
-- 插入大于10的数据, 没有被阻塞
insert into A(id, balance, version) values (18, 1888, 0);

-- session 1/2 全部命中
start transaction;
-- sesstion 1
select * from A where id in(5, 7, 11) lock in share mode;
-- session 2
-- 之前是被阻塞的, 现在没有被阻塞了
insert into A(id, balance, version) values (9, 1888, 0);

以上两种情况知道 Gap 锁会用在非唯一索引(多个命中)或者不走索引的当前读中.