前言
之前分析一个死锁问题, 发现自己对数据库隔离级别理解还不够清楚, 所以趁着这几天假期, 整理一下 MySQL 事务的四大隔离级别相关知识, 希望对大家有帮助~
事务
什么是事务?
事务, 由一个有限的数据库操作序列构成, 这些操作要么全部执行, 要么全部不执行, 是一个不可分割的工作单位.
假如 A 转账给 B 100 元, 先从 A 的账户里扣除 100 元, 再在 B 的账户上加上 100 元. 如果扣完 A 的 100 元后, 还没来得及给 B 加上, 银行系统异常了, 最后导致 A 的余额减少了, B 的余额却没有增加. 所以就需要事务, 将 A 的钱回滚回去, 就是这么简单.
事务的四大特性
原子性: 事务作为一个整体被执行, 包含在其中的对数据库的操作要么全部都执行, 要么都不执行.
一致性: 指在事务开始之前和事务结束以后, 数据不会被破坏, 假如 A 账户给 B 账户转 10 块钱, 不管成功与否, A 和 B 的总金额是不变的.
隔离性: 多个事务并发访问时, 事务之间是相互隔离的, 一个事务不应该被其他事务干扰, 多个并发事务之间要相互隔离..
持久性: 表示事务完成提交后, 该事务对数据库所作的操作更改, 将持久地保存在数据库之中.
事务并发存在的问题
事务并发执行存在什么问题呢, 换句话说就是, 一个事务是怎么干扰到其他事务的呢? 看例子吧~
假设现在有表:
- CREATE TABLE `account` (
- `id` int(11) NOT NULL,
- `name` varchar(255) DEFAULT NULL,
- `balance` int(11) DEFAULT NULL,
- PRIMARY KEY (`id`),
- UNIQUE KEY `un_name_idx` (`name`) USING BTREE
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表中有数据:
脏读(dirty read)
假设现在有两个事务 A,B:
假设现在 A 的余额是 100, 事务 A 正在准备查询 Jay 的余额
这时候, 事务 B 先扣减 Jay 的余额, 扣了 10
最后 A 读到的是扣减后的余额
由上图可以发现, 事务 A,B 交替执行, 事务 A 被事务 B 干扰到了, 因为事务 A 读取到事务 B 未提交的数据, 这就是脏读.
不可重复读(unrepeatable read)
假设现在有两个事务 A 和 B:
事务 A 先查询 Jay 的余额, 查到结果是 100
这时候事务 B 对 Jay 的账户余额进行扣减, 扣去 10 后, 提交事务
事务 A 再去查询 Jay 的账户余额发现变成了 90
事务 A 又被事务 B 干扰到了! 在事务 A 范围内, 两个相同的查询, 读取同一条记录, 却返回了不同的数据, 这就是不可重复读.
幻读
假设现在有两个事务 A,B:
事务 A 先查询 id 大于 2 的账户记录, 得到记录 id=2 和 id=3 的两条记录
这时候, 事务 B 开启, 插入一条 id=4 的记录, 并且提交了
事务 A 再去执行相同的查询, 却得到了 id=2,3,4 的 3 条记录了.
事务 A 查询一个范围的结果集, 另一个并发事务 B 往这个范围中插入 / 删除了数据, 并静悄悄地提交, 然后事务 A 再次查询相同的范围, 两次读取得到的结果集不一样了, 这就是幻读.
事务的四大隔离级别实践
既然并发事务存在脏读, 不可重复, 幻读等问题, InnoDB 实现了哪几种事务的隔离级别应对呢?
读未提交(Read Uncommitted)
读已提交(Read Committed)
可重复读(Repeatable Read)
串行化(Serializable)
读未提交(Read Uncommitted)
想学习一个知识点, 最好的方式就是实践之. 好了, 我们去数据库给它设置读未提交隔离级别, 实践一下吧~
先把事务隔离级别设置为 read uncommitted, 开启事务 A, 查询 id=1 的数据
- set session transaction isolation level read uncommitted;
- begin;
- select * from account where id =1;
结果如下:
这时候, 另开一个窗口打开 MySQL, 也把当前事务隔离级别设置为 read uncommitted, 开启事务 B, 执行更新操作
- set session transaction isolation level read uncommitted;
- begin;
- update account set balance=balance+20 where id =1;
接着回事务 A 的窗口, 再查 account 表 id=1 的数据, 结果如下:
可以发现, 在读未提交(Read Uncommitted) 隔离级别下, 一个事务会读到其他事务未提交的数据的, 即存在脏读问题. 事务 B 都还没 commit 到数据库呢, 事务 A 就读到了, 感觉都乱套了... 实际上, 读未提交是隔离级别最低的一种.
已提交读(READ COMMITTED)
为了避免脏读, 数据库有了比读未提交更高的隔离级别, 即已提交读.
把当前事务隔离级别设置为已提交读(READ COMMITTED), 开启事务 A, 查询 account 中 id=1 的数据
- set session transaction isolation level read committed;
- begin;
- select * from account where id =1;
另开一个窗口打开 MySQL, 也把事务隔离级别设置为 read committed, 开启事务 B, 执行以下操作
- set session transaction isolation level read committed;
- begin;
- update account set balance=balance+20 where id =1;
接着回事务 A 的窗口, 再查 account 数据, 发现数据没变:
我们再去到事务 B 的窗口执行 commit 操作:
commit;
最后回到事务 A 窗口查询, 发现数据变了:
由此可以得出结论, 隔离级别设置为已提交读(READ COMMITTED) 时, 已经不会出现脏读问题了, 当前事务只能读取到其他事务提交的数据. 但是, 你站在事务 A 的角度想想, 存在其他问题吗?
提交读的隔离级别会有什么问题呢?
在同一个事务 A 里, 相同的查询 sql, 读取同一条记录(id=1), 读到的结果是不一样的, 即不可重复读. 所以, 隔离级别设置为 read committed 的时候, 还会存在不可重复读的并发问题.
可重复读(Repeatable Read)
如果你的老板要求, 在同个事务中, 查询结果必须是一致的, 即老板要求你解决不可重复的并发问题, 怎么办呢? 老板, 臣妾办不到? 来实践一下可重复读(Repeatable Read) 这个隔离级别吧~
哈哈, 步骤 1,2,6 的查询结果都是一样的, 即 repeatable read 解决了不可重复读问题, 是不是心里美滋滋的呢, 终于解决老板的难题了~
RR 级别是否解决了幻读问题呢?
再来看看网上的一个热点问题, 有关于 RR 级别下, 是否解决了幻读问题? 我们来实践一下:
由图可得, 步骤 2 和步骤 6 查询结果集没有变化, 看起来 RR 级别是已经解决幻读问题了~
但是呢, RR 级别还是存在这种现象:
其实, 上图如果事务 A 中, 没有 update account set balance=200 where id=5; 这步操作, select * from account where id>2 查询到的结果集确实是不变, 这种情况没有幻读问题. 但是, 有了 update 这个骚操作, 同一个事务, 相同的 sql, 查出的结果集不同, 这个是符合了幻读的定义~
这个问题, 亲爱的朋友, 你觉得它算幻读问题吗?
串行化(Serializable)
前面三种数据库隔离级别, 都有一定的并发问题, 现在放大招吧, 实践 SERIALIZABLE 隔离级别.
把事务隔离级别设置为 Serializable, 开启事务 A, 查询 account 表数据
- set session transaction isolation level serializable;
- select @@tx_isolation;
- begin;
- select * from account;
另开一个窗口打开 MySQL, 也把事务隔离级别设置为 Serializable, 开启事务 B, 执行插入一条数据:
- set session transaction isolation level serializable;
- select @@tx_isolation;
- begin;
- insert into account(id,name,balance) value(6,'Li',100);
执行结果如下:
由图可得, 当数据库隔离级别设置为 serializable 的时候, 事务 B 对表的写操作, 在等事务 A 的读操作. 其实, 这是隔离级别中最严格的, 读写都不允许并发. 它保证了最好的安全性, 性能却是个问题~
MySQL 隔离级别的实现原理
实现隔离机制的方法主要有两种:
读写锁
一致性快照读, 即 MVCC
MySQL 使用不同的锁策略(Locking Strategy)/MVCC 来实现四种不同的隔离级别. RR,RC 的实现原理跟 MVCC 有关, RU 和 Serializable 跟锁有关.
读未提交(Read Uncommitted)
官方说法:
SELECT statements are performed in a nonlocking fashion, but a possible earlier version of a row might be used. Thus, using this isolation level, such reads are not consistent.
读未提交, 采取的是读不加锁原理.
事务读不加锁, 不阻塞其他事务的读和写
事务写阻塞其他事务写, 但不阻塞其他事务读;
串行化(Serializable)
官方的说法:
InnoDB implicitly converts all plain SELECT statements to SELECT ... FOR SHARE if autocommit is disabled. If autocommit is enabled, the SELECT is its own transaction. It therefore is known to be read only and can be serialized if performed as a consistent (nonlocking) read and need not block for other transactions. (To force a plain SELECT to block if other transactions have modified the selected rows, disable autocommit.)
所有 SELECT 语句会隐式转化为
SELECT ... FOR SHARE
, 即加共享锁.
读加共享锁, 写加排他锁, 读写互斥. 如果有未提交的事务正在修改某些行, 所有 select 这些行的语句都会阻塞.
MVCC 的实现原理
MVCC, 中文叫多版本并发控制, 它是通过读取历史版本的数据, 来降低并发事务冲突, 从而提高并发性能的一种机制. 它的实现依赖于隐式字段, undo 日志, 快照读 & 当前读, Read View, 因此, 我们先来了解这几个知识点.
隐式字段
对于 InnoDB 存储引擎, 每一行记录都有两个隐藏列 DB_TRX_ID,DB_ROLL_PTR, 如果表中没有主键和非 NULL 唯一键时, 则还会有第三个隐藏的主键列 DB_ROW_ID.
DB_TRX_ID, 记录每一行最近一次修改 (修改 / 更新) 它的事务 ID, 大小为 6 字节;
DB_ROLL_PTR, 这个隐藏列就相当于一个指针, 指向回滚段的 undo 日志, 大小为 7 字节;
DB_ROW_ID, 单调递增的行 ID, 大小为 6 字节;
undo 日志
事务未提交的时候, 修改数据的镜像(修改前的旧版本), 存到 undo 日志里. 以便事务回滚时, 恢复旧版本数据, 撤销未提交事务数据对数据库的影响.
undo 日志是逻辑日志. 可以这样认为, 当 delete 一条记录时, undo log 中会记录一条对应的 insert 记录, 当 update 一条记录时, 它记录一条对应相反的 update 记录.
存储 undo 日志的地方, 就是回滚段.
多个事务并行操作某一行数据时, 不同事务对该行数据的修改会产生多个版本, 然后通过回滚指针 (DB_ROLL_PTR) 连一条 Undo 日志链.
我们通过例子来看一下~
- MySQL> select * from account ;
- +----+------+---------+
- | id | name | balance |
- +----+------+---------+
- | 1 | Jay | 100 |
- +----+------+---------+
- 1 row in set (0.00 sec)
假设表 accout 现在只有一条记录, 插入该该记录的事务 Id 为 100
如果事务 B(事务 Id 为 200), 对 id=1 的该行记录进行更新, 把 balance 值修改为 90
事务 B 修改后, 形成的 Undo Log 链如下:
快照读 & 当前读
快照读:
读取的是记录数据的可见版本(有旧的版本), 不加锁, 普通的 select 语句都是快照读, 如:
select * from account where id>2;
当前读:
读取的是记录数据的最新版本, 显示加锁的都是当前读
- select * from account where id>2 lock in share mode;
- select * from account where id>2 for update;
- Read View
Read View 就是事务执行快照读时, 产生的读视图.
事务执行快照读时, 会生成数据库系统当前的一个快照, 记录当前系统中还有哪些活跃的读写事务, 把它们放到一个列表里.
Read View 主要是用来做可见性判断的, 即判断当前事务可见哪个版本的数据~
为了下面方便讨论 Read View 可见性规则, 先定义几个变量
m_ids: 当前系统中那些活跃的读写事务 ID, 它数据结构为一个 List.
min_limit_id:m_ids 事务列表中, 最小的事务 ID
max_limit_id:m_ids 事务列表中, 最大的事务 ID
如果 DB_TRX_ID <min_limit_id, 表明生成该版本的事务在生成 ReadView 前已经提交(因为事务 ID 是递增的), 所以该版本可以被当前事务访问.
如果 DB_TRX_ID> m_ids 列表中最大的事务 id, 表明生成该版本的事务在生成 ReadView 后才生成, 所以该版本不可以被当前事务访问.
如果 min_limit_id =<DB_TRX_ID<= max_limit_id, 需要判断 m_ids.contains(DB_TRX_ID), 如果在, 则代表 Read View 生成时刻, 这个事务还在活跃, 还没有 Commit, 你修改的数据, 当前事务也是看不见的; 如果不在, 则说明, 你这个事务在 Read View 生成之前就已经 Commit 了, 修改的结果, 当前事务是能看见的.
注意啦!! RR 跟 RC 隔离级别, 最大的区别就是: RC 每次读取数据前都生成一个 ReadView, 而 RR 只在第一次读取数据时生成一个 ReadView.
已提交读(READ COMMITTED) 存在不可重复读问题的分析历程
我觉得理解一个新的知识点, 最好的方法就是居于目前存在的问题 / 现象, 去分析它的来龙去脉~ RC 的实现也跟 MVCC 有关, RC 是存在重复读并发问题的, 所以我们来分析一波 RC 吧, 先看一下执行流程
假设现在系统里有 A,B 两个事务在执行, 事务 ID 分别为 100,200, 并且假设存在的老数据, 插入事务 ID 是 50 哈~
事务 A 先执行查询 1 的操作
- # 事务 A,Transaction ID 100
- begin ;
查询 1:select * from account WHERE id = 1;
事务 B 执行更新操作, id =1 记录的 undo 日志链如下
- begin;
- update account set balance =balance+20 where id =1;
回到事务 A, 执行查询 2 的操作
begin ;
查询 1:select * from account WHERE id = 1;
查询 2:select * from account WHERE id = 1;
查询 2 执行分析:
事务 A 在执行到 SELECT 语句时, 重新生成一个 ReadView, 因为事务 B(200)在活跃, 所以 ReadView 的 m_ids 列表内容就是[200]
由上图 undo 日志链可得, 最新版本的 balance 为 1000, 它的事务 ID 为 200, 在活跃事务列表里, 所以当前事务 (事务 A) 不可见.
我们继续找下一个版本, balance 为 100 这行记录, 事务 Id 为 50, 小于活跃事务 ID 列表最小记录 200, 所以这个版本可见, 因此, 查询 2 的结果, 就是返回 balance=100 这个记录~~
我们回到事务 B, 执行提交操作, 这时候 undo 日志链不变
- begin;
- update account set balance =balance+20 where id =1;
- commit
再次回到事务 A, 执行查询 3 的操作
begin ;
查询 1:select * from account WHERE id = 1;
查询 2:select * from account WHERE id = 1;
查询 3:select * from account WHERE id = 1;
查询 3 执行分析:
事务 A 在执行到 SELECT 语句时, 重新生成一个 ReadView, 因为事务 B(200)已经提交, 不载活跃, 所以 ReadView 的 m_ids 列表内容就是空的了.
所以事务 A 直接读取最新纪录, 读取到 balance =120 这个版本的数据.
所以, 这就是 RC 存在不可重复读问题的过程啦有不理解的地方可以多读几遍哈
可重复读 (Repeatable Read) 解决不可重复读问题的一次分析
我们再来分析一波, RR 隔离级别是如何解决不可重复读并发问题的吧~
你可能会觉得两个并发事务的例子太简单了, 好的! 我们现在来点刺激的, 开启三个事务~
假设现在系统里有 A,B,C 两个事务在执行, 事务 ID 分别为 100,200,300, 存量数据插入的事务 ID 是 50~
- # 事务 A,Transaction ID 100
- begin ;
- UPDATE account SET balance = 1000 WHERE id = 1;
- # 事务 B,Transaction ID 200
- begin ; // 开个事务, 占坑先
这时候, account 表中, id =1 记录的 undo 日志链如下:
- # 事务 C,Transaction ID 300
- begin ;
- // 查询 1:select * from account WHERE id = 1;
查询 1 执行过程分析:
事务 C 在执行 SELECT 语句时, 会先生成一个 ReadView. 因为事务 A(100),B(200)在活跃, 所以 ReadView 的 m_ids 列表内容就是[100, 200].
由上图 undo 日志链可得, 最新版本的 balance 为 1000, 它的事务 ID 为 100, 在活跃事务列表里, 所以当前事务 (事务 C) 不可见.
我们继续找下一个版本, balance 为 100 这行记录, 事务 Id 为 50, 小于活跃事务 ID 列表最小记录 100, 所以这个版本可见, 因此, 查询 1 的结果, 就是返回 balance=100 这个记录~~
接着, 我们把事务 A 提交一下:
- # 事务 A,Transaction ID 100
- begin ;
- UPDATE account SET balance = 1000 WHERE id = 1;
- commit;
在事务 B 中, 执行更新操作, 把 id=1 的记录 balance 修改为 2000, 更新完后, undo 日志链如下:
- # 事务 B,Transaction ID 200
- begin ; // 开个事务, 占坑先
- UPDATE account SET balance = 2000 WHERE id = 1;
回到事务 C, 执行查询 2
- # 事务 C,Transaction ID 300
- begin ;
- // 查询 1:select * from account WHERE id = 1;
- // 查询 2:select * from account WHERE id = 1;
查询 2: 执行分析:
在 RR 级别下, 执行查询 2 的时候, 因为前面 ReadView 已经生成过了, 所以直接服用之前的 ReadView, 活跃事务列表为[100,200].
由上图 undo 日志链可得, 最新版本的 balance 为 2000, 它的事务 ID 为 200, 在活跃事务列表里, 所以当前事务 (事务 C) 不可见.
我们继续找下一个版本, balance 为 1000 这行记录, 事务 Id 为 100, 也在活跃事务列表里, 所以当前事务 (事务 C) 不可见.
继续找下一个版本, balance 为 100 这行记录, 事务 Id 为 50, 小于活跃事务 ID 列表最小记录 100, 所以这个版本可见, 因此, 查询 2 的结果, 也是返回 balance=100 这个记录~~
锁相关概念补充(附):
共享锁与排他锁
InnoDB 实现了标准的行级锁, 包括两种: 共享锁(简称 s 锁), 排它锁(简称 x 锁).
共享锁(S 锁): 允许持锁事务读取一行.
排他锁(X 锁): 允许持锁事务更新或者删除一行.
如果事务 T1 持有行 r 的 s 锁, 那么另一个事务 T2 请求 r 的锁时, 会做如下处理:
T2 请求 s 锁立即被允许, 结果 T1 T2 都持有 r 行的 s 锁
T2 请求 x 锁不能被立即允许
如果 T1 持有 r 的 x 锁, 那么 T2 请求 r 的 x,s 锁都不能被立即允许, T2 必须等待 T1 释放 x 锁才可以, 因为 X 锁与任何的锁都不兼容.
记录锁(Record Locks)
记录锁是最简单的行锁, 仅仅锁住一行. 如:
SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE
记录锁永远都是加在索引上的, 即使一个表没有索引, InnoDB 也会隐式的创建一个索引, 并使用这个索引实施记录锁.
会阻塞其他事务对其插入, 更新, 删除
记录锁的事务数据(关键词: lock_mode X locks rec but not gap), 记录如下:
- RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
- trx id 10078 lock_mode X locks rec but not gap
- Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
- 0: len 4; hex 8000000a; asc ;;
- 1: len 6; hex 00000000274f; asc 'O;;
- 2: len 7; hex b60000019d0110; asc ;;
间隙锁(Gap Locks)
间隙锁是一种加在两个索引之间的锁, 或者加在第一个索引之前, 或最后一个索引之后的间隙.
使用间隙锁锁住的是一个区间, 而不仅仅是这个区间中的每一条数据.
间隙锁只阻止其他事务插入到间隙中, 他们不阻止其他事务在同一个间隙上获得间隙锁, 所以 gap x lock 和 gap s lock 有相同的作用.
Next-Key Locks
Next-key 锁是记录锁和间隙锁的组合, 它指的是加在某条记录以及这条记录前面间隙上的锁.
RC 级别存在幻读分析
因为 RC 是存在幻读问题的, 所以我们先切到 RC 隔离级别, 分析一波~
假设 account 表有 4 条数据.
开启事务 A, 执行当前读, 查询 id>2 的所有记录.
再开启事务 B, 插入 id=5 的一条数据.
事务 B 插入数据成功后, 再修改 id=3 的记录
回到事务 A, 再次执行 id>2 的当前读查询
事务 B 可以插入 id=5 的数据, 却更新不了 id=3 的数据, 陷入阻塞. 证明事务 A 在执行当前读的时候在 id =3 和 id=4 这两条记录上加了锁, 但是并没有对 id> 2 这个范围加锁~
事务 B 陷入阻塞后, 切回事务 A 执行当前读操作时, 死锁出现. 因为事务 B 在 insert 的时候, 会在新纪录 (id=5) 上加锁, 所以事务 A 再次执行当前读, 想获取 id> 3 的记录, 就需要在 id=3,4,5 这 3 条记录上加锁, 但是 id = 5 这条记录已经被事务 B 锁住了, 于是事务 A 被事务 B 阻塞, 同时事务 B 还在等待 事务 A 释放 id = 3 上的锁, 最终产生了死锁.
因此, 我们可以发现, RC 隔离级别下, 加锁的 select, update, delete 等语句, 使用的是记录锁, 其他事务的插入依然可以执行, 因此会存在幻读~
RR 级别解决幻读分析
因为 RR 是解决幻读问题的, 怎么解决的呢, 分析一波吧~
假设 account 表有 4 条数据, RR 级别.
开启事务 A, 执行当前读, 查询 id>2 的所有记录.
再开启事务 B, 插入 id=5 的一条数据.
可以发现, 事务 B 执行插入操作时, 阻塞了~ 因为事务 A 在执行 select ... lock in share mode 的时候, 不仅在 id = 3,4 这 2 条记录上加了锁, 而且在 id> 2 这个范围上也加了间隙锁.
因此, 我们可以发现, RR 隔离级别下, 加锁的 select, update, delete 等语句, 会使用间隙锁 + 临键锁, 锁住索引记录之间的范围, 避免范围间插入记录, 以避免产生幻影行记录.
参考与感谢
解决死锁之路 - 学习事务与隔离级别
五分钟搞清楚 MySQL 事务隔离级别 https://www.jianshu.com/p/4e3edbedb9a8
4 种事务的隔离级别, InnoDB 如何巧妙实现? https://mp.weixin.qq.com/s/x_7E2R2i27Ci5O7kLQF0UA
MySQL 事务隔离级别和 MVCC
MySQL InnoDB MVCC 机制的原理及实现
MVCC 多版本并发控制 https://www.jianshu.com/p/8845ddca3b23
个人公众号
觉得写得好的小伙伴给个点赞 + 关注啦, 谢谢~
如果有写得不正确的地方, 麻烦指出, 感激不尽.
同时非常期待小伙伴们能够关注我公众号, 后面慢慢推出更好的干货~ 嘻嘻
来源: https://www.cnblogs.com/jay-huaxiao/p/12639435.html