一文带你理解脏读幻读不可重复读与mysql的锁事务隔离机制

jiezi

5 年前

首先说一下数据库事务的四大特性

事务的四大特性是 ACID(不是 ” 酸 ”….)

原子性指的是事务要么完全执行, 要么完全不执行.

事务完成时, 数据必须处于一致的状态. 若事务执行途中出错, 会回滚到之前的事务没有执行前的状态, 这样数据就处于一致的状态. 若事务出错后没有回滚, 部分修改的内容写入到了数据库中, 这时数据就是不一致的状态.

同时处理多个事务时, 一个事务的执行不能被另一个事务所干扰, 事务的内部操作与其他并发事务隔离.

事务提交后, 对数据的修改是永久性的.

Mysql 的锁其实可以按很多种形式分类:

按加锁机制分, 可分为乐观锁与悲观锁.
按兼容性来分, 可分为 X 锁与 S 锁.
按锁粒度分, 可分为表锁, 行锁, 页锁.
按锁模式分, 可分为记录锁,gap 锁,next-key 锁, 意向锁, 插入意向锁.

这里主要讨论 S 锁,X 锁, 乐观锁与悲观锁.

S 锁与 X 锁是 InnoDB 引擎实现的两种标准行锁机制. 查看默认引擎可使用

show variables like '%storage_engine%';

作者的 mysql 版本为 8.0.17, 结果如下:

先建好测试库与测试表, 很简单, 表就两个字段.

create database test;
use test;
create table a
(
id int primary key auto_increment,
money int
);

S 锁也叫共享锁, 读锁, 数据只能被读取不能被修改.
玩一下, 上锁!

lock table a read;

然后 …..

只能读不能改, 删, 也不能增.

X 锁也叫排他锁, 写锁, 一个事务对表加锁后, 其他事务就不能对其进行加锁与增删查改操作.

设置手动提交, 开启事务, 上 X 锁.

set autocmmmit=0;
start transaction;
lock table a write;

在开启另一个事务, 使用 select 语句.

set autocommit=0;
start transaction;
select * from a;

这里是阻塞 select 操作, 因为一直都没释放 X 锁.

同样也不能再加锁, 也是阻塞中.

回到原来那个加锁的事务, 嗯, 什么事也没有, 正常读写.

释放锁后:

unlock table;

在另一个事务中可以看到中断时间.

乐观锁就是总是假设是最好的情况, 每次去操作的时候都不会上锁, 但在更新时会判断有没有其他操作去更新这个数据, 是一种宽松的加锁机制.
mysql 本身没有提供乐观锁的支持, 需要自己来实现, 常用的方法有版本控制和时间戳控制两种.

版本控制
版本控制就是为表增加一个 version 字段, 读取数据时连同这个 version 字段一起读出来, 之后进行更新操作, 版本号加 1, 再将提交的数据的版本号与数据库中的版本号进行比较, 若提交的数据的版本号大于数据库中的版本号才会进行更新.
举个例子, 假设此时 version=1,A 进行操作, 更新数据后 version=2, 与此同时 B 也进行操作, 更新数据后 version=2,A 先完成操作, 率先将数据库中的 version 设置为 2, 此时 B 提交,B 的 version 与数据库中的 version 一样, 不接受 B 的提交.
时间戳控制
时间戳控制与版本控制差不多, 把 version 字段改为 timestamp 字段

还有一种实现方法叫 CAS 算法, 这个作者不怎么了解, 有兴趣可以自行搜索.

悲观锁就是总是假设最坏的情况, 在整个数据处理状态中数据处于锁定状态, 悲观锁的实现往往依靠数据库的锁机制. 每次在拿到数据前都会上锁.
mysql 在调用一些语句时会上悲观锁, 如(先关闭自动提交, 开启事务):

set autocommit=0;
start transaction;

两个事务都这样操作, 然后其中一个事务输入:

select * from a where xxx for update;

在另一事务也这样输入:

这时语句会被阻塞, 直到上锁的那个事务 commit(解开悲观锁).

在另一事务中可以看到这个事务被阻塞了 2.81s.

*** lock in share mode.

也会加上悲观锁.

脏读是指一个事务读取到了另一事务未提交的数据, 造成 select 前后数据不一致.

比如事务 A 修改了一些数据, 但没有提交, 此时事务 B 却读取了, 这时事务 B 就形成了脏读, 一般事务 A 的后续操作是回滚, 事务 B 读取到了临时数值.

事务 A	事务 B
开始事务	开始事务
更新 X, 旧值 X =1, 新值 X =2
	读取 X,X=2(脏读)
回滚 X =1
结束事务(X=1)	结束事务

幻读是指并不是指同一个事务执行两次相同的 select 语句得到的结果不同, 而是指 select 时不存在某记录, 但准备插入时发现此记录已存在, 无法插入, 这就产生了幻读.

事务 A	事务 B
开始事务	开始事务
select 某个数据为空, 准备插入一个新数据
	插入一个新数据
	提交, 结束事务
插入数据, 发现插入失败, 由于事务 B 已插入相同数据
结束事务

不可重复读指一个事务读取到了另一事务已提交的数据, 造成 select 前后数据不一致.
比如事务 A 修改了一些数据并且提交了, 此时事务 B 却读取了, 这时事务 B 就形成了不可重复读.

事务 A	事务 B
开始事务	开始事务
读取 X =1	读取 X =1
更新 X =2
提交, 结束事务
	读取 X =2
	结束事务

第一类丢失更新就是两个事务同时更新一个数据, 一个事务更新完毕并提交后, 另一个事务回滚, 造成提交的更新丢失.

事务 A	事务 B
开始事务	开始事务
读取 X =1	读取 X =1
修改 X =2	修改 X =3
	提交, 结束事务
回滚
结束事务(X=1)	X=1,X 本应为提交的 3

第二类丢失更新就是两个事务同时更新一个数据, 先更新的事务提交的数据会被后更新的事务提交的数据覆盖, 即先更新的事务提交的数据丢失.

事务 A	事务 B
开始事务	开始事务
读取 X =1	读取 X =1
更新 X =2
提交事务,X=2, 结束
	更新 X =3
	提交事务,X=3, 事务 A 的更新丢失, 结束

封锁协议就是在用 X 锁或 S 锁时制定的一些规则, 比如锁的持续时间, 锁的加锁时间等. 不同的封锁协议对应不同的隔离级别. 事务的隔离级别一共有 4 种, 由低到高分别是 Read uncommitted,Read committed,Repeatable read,Serializable, 分别对应的相应的封锁协议等级.

一级封锁协议对应的是 Read uncommitted 隔离级别,Read uncommitted, 读未提交, 一个事务可以读取另一个事务未提交的数据, 这是最低的级别. 一级封锁协议本质上是在事务修改数据之前加上 X 锁, 直到事务结束后才释放, 事务结束包括正常结束 (commit) 与非正常结束(rollback).

** 一级封锁协议不会造成更新丢失, 但可能引发脏读, 幻读, 不可重复读.
设置手动提交与事务隔离等级为 read uncommited, 并开启事务(注意要先设置事务等级再开启事务).**

set autocommit=0;
set session transaction isolation level read uncommitted;
start transaction;

(中间有一行打多了一个 t 可以忽略 …..)

在一个事务中修改表中的值, 不提交, 另一个事务可以 select 到未提交的值.

出现了脏读.

在一个事务中插入一条数据, 提交.

另一事务中 select 时没有, 准备 insert, 但是 insert 时却提示已经存在. 引发幻读.

未操作提交前:

另一事务修改并提交:

再次读:

引发不可重复读.

二级封锁协议本质上在一级协议的基础上(在修改数据时加 X 锁), 在读数据时加上 S 锁, 读完后立即释放 S 锁, 可以避免脏读. 但有可能出现不可重复读与幻读. 二级封锁协议对应的是 Read committed 与 Repeatable Read 隔离级别.

先设置隔离等级

set session transaction isolation level read committed;

Read committed, 读提交, 读提交可以避免脏读, 但可能出现幻读与不可重复读.

开启一个事务并更新值, 在这个事务中 money=100(更新后)

另一事务中 money 为未更新前的值, 这就避免了脏读.

注意, 事实上脏读在 read committed 隔离级别下是不被允许的, 但是 mysql 不会阻塞查询, 而是返回未修改之前数据的备份, 这种机制叫 MVCC 机制(多版本并发控制).

在一个事务中插入数据并提交.

另一事务中不能插入 ” 不存在 ” 的数据, 出现幻读.

事务修改并提交前:

事务修改并提交:

出现不可重复读.

Repeatable read 比 Read committed 严格一点, 是 Mysql 的默认级别, 读取过程更多地受到 MVCC 影响, 可防止不可重复读与脏读, 但仍有可能出现幻读.

在一个事务中修改数据, 不提交.

另一事务中两次 select 的结果都不变, 没有出现脏读.

一个事务修改数据并提交.

另一事务中 select 的结果没有发生改变, 即没有出现不可重复读.

同理, 一个事务插入一条数据并提交.

另一个事务插入时出现幻读.

三级封锁协议, 在一级封锁协议的基础上(修改时加 X 锁), 读数据时加上 S 锁(与二级类似), 但是直到事务结束后才释放 S 锁, 可以避免幻读, 脏读与不可重复读. 三级封锁协议对应的隔离级别是 Serializable.

先设置 Serializable 隔离级别

set session transaction isolation level serializable

设置事务隔离等级后开启事务并 update, 发现堵塞. 从而避免了脏读.

插入时直接阻塞, 避免了幻读.

在脏读的例子中可以知道,update 会被堵塞, 都不能提交事务, 因此也避免了不可重复读.

事务必须分为两个阶段对数据进行加锁与解锁, 两端锁协议叫 2PL(不是 2PC), 所有的加锁都在解锁之前进行.

加锁会在更新或者

select *** for update
*** lock in share mode

时进行

解锁在事务结束时进行, 事务结束包括 rollback 与 commit.

这是作者的 CSDN 地址

最后, 以下是作者的微信公众号, 里面有更多精彩文章, 欢迎关注. 一起学习, 一起成长.

参考链接
1:ACID1

2:ACID2

3:mysql 的锁 1

4: 乐观锁与悲观锁 1

5: 乐观锁与悲观锁 2

6: 乐观锁与悲观锁 3

7:mysql 修改事务隔离等级

8:mysql 三级封锁与二段锁

9: 数据库封锁协议

10:mysql 事务隔离机制 1

11:mysql 事务隔离机制 2

12:mysql 幻读

13:mysql 脏读, 不可重复读与幻读

14:mysql 两段锁 1

15:mysql 两段锁 2

1 ACID

(1) A: 原子性(Atomicity)

(2) C: 一致性(Consistency)

(3) I: 隔离性(Isolation)

(4) D: 持久性(Durability)

2 Mysql 的锁

(1) S 锁与 X 锁

Ⅰ.S 锁

Ⅱ.X 锁

(2) 乐观锁与悲观锁

Ⅰ. 乐观锁

Ⅱ. 悲观锁

4 脏读, 幻读, 不可重复读与两类丢失更新

(1) 脏读

(2) 幻读

(3) 不可重复读

(4) 第一类丢失更新

(5) 第二类丢失更新

5 封锁协议与隔离级别

(1) 一级封锁协议

a. 引发脏读

b. 引发幻读

c. 引发不可重复读

(2) 二级封锁协议

Ⅰ.Read committed

a. 避免脏读

b. 引发幻读

c. 引发不可重复读

Ⅱ.Repeatable read

a. 避免脏读

b. 避免不可重复读

c. 引发幻读

(3) 三级封锁协议

a. 避免脏读

b. 避免幻读

c. 避免不可重复读

6 两段锁协议

(1) 加锁

(2) 解锁

7. 最后