【深入浅出事务】：MySQL事务的隔离级别（超详细）

本文链接：https://blog.csdn.net/pz_winner/article/details/78392918

本质

隔离级别定义了数据库系统中一个操作产生的影响什么时候以哪种方式可以对其他并发操作可见，隔离性是事务的ACID中的一个重要属性，核心是对锁的操作。

锁

从数据库系统角度

共享锁(Shared Lock)

读锁，保证数据只能读取，不能被修改。
如果事务A对数据M加上S锁，则事务A可以读记录M但不能修改记录M，其他事务(这里用事务B)只能对记录M再加上S锁，不能加X锁，直到事务A释放了记录M上的S锁，保证了其他事务(事务B)可以读记录M，但在事务A释放M上的S锁之前不能对记录M进行任何修改。

例子: MySql 5.5 证明S锁的特性。

数据准备

CREATE TABLE `test1` (`id`  bigint(1) NOT NULL DEFAULT 0 ,`name`  varchar(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,PRIMARY KEY (`id`))

INSERT INTO test1 VALUES(1,1),(2,2),(3,3)

设置事务为手动提交，方便证明上诉结论(mysql默认为自动提交，执行单句sql其实包含开启事务，执行sql，提交事务，3个步骤)。
客户端A开启一个事务A。
客户端A给test1表加上读锁。
查询test1表原有的数据(加上S锁后，可以读数据)。
事务A修改数据(加上S锁后，无法修改数据)。
B客户端开启事务B。
事务B对记录M查询(因为是S锁，其他事务可以对记录A进行select )。
事务B对记录M加读锁(事务A对记录A加上S锁后，事务B同样也可以对记录A加上S锁，证明了，mysql里的读锁就是S锁，具有共享)。
事务B对记录M加写锁(一直处于等待状态，被挂起,具体挂起的原因可以见：Innodb行锁源码学习。
事务B对记录M修改(一直处于等待状态，被挂起)。
事务A释放M上的S锁。

此时事务B才得到响应。

说明了，只有释放了读锁，另外一个事务才能加写锁，或者更新数据。

排他锁(X 锁)

写锁，若事务A对数据对象M加上X锁，事务A可以读记录M也可以修改记录M，其他事务(事务B)不能再对记录M加任何锁，直到事务A释放记录M上的锁，保证了其他事务(事务B)在事务A释放记录M上的锁之前不能再读取和修改记录M。

例子：Mysql 5.5，证明X锁的特性。

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，并且开启事务B。
事务A给记录M加上X锁。
事务A可以读记录M也可以修改记录M。
事务B不能对记录M加任何锁。
事务B也不能对记录M进行查询和修改。
事务A释放记录M上的X锁。
事务B阻塞的进程被执行，中断了6秒。

从程序员角度

悲观锁(Pessimistic[ˌpesɪˈmɪstɪk] Lock)

对数据被外界修改保持保守态度，在整个数据处理过程中，数据处于锁定状态，依赖于数据库提供的锁机制。

乐观锁(Optimistic[ˌɑ:ptɪˈmɪstɪk]Lock)

采用宽松的加锁机制，基于数据版本记录机制，具体做法：数据库表增加一个"version"字段来实现，读取数据时，将版本号一同读出，之后更新，对版本号加1，将提交数据的版本数据与数据库对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库的数据，则予以更新，否则，被认为是过期数据。

几个概念

丢失更新

事务A和事务B，同时获得相同数据，然后在各自的事务中修改数据M，事务A先提交事务，数据M假如为M+，事务B后提交事务，数据M变成了M++，最终结果变成M++，覆盖了事务A的更新。

例子：

事务A	事务B
读取X=100	读取X=100
写入X=X+100
事务结束X=200
	写入X=X+100
	事务结束X=300(事务A的更新丢失)

脏读

允许事务B可以读到事务A修改而未提交的数据，可能会造成了脏读(脏读本质就是无效的数据，只有当事务A回滚，那么事务B读到的数据才为无效的，所以这里只是可能造成脏读，当事务A不回滚的时候，事务B读到的数据就不为脏数据，也就是有效的数据，脏数据会导致以后的操作都会发生错误，一定要去避免，不能凭借侥幸，事务A不能百分之百保证不回滚，所以这种隔离级别很少用于实际应用，并且它的性能也不比其他级别好多少)。

例子：

事务A	事务B
写入X=X+100(x=200)
	读取X=200(无效数据，脏读)
事务回滚X=100
事务结束X=100
	事务结束

不可重复读

不可重复读是指在一个事务范围中2次或者多次查询同一数据M返回了不同的数据，例如：事务B读取某一数据，事务A修改了该数据M并且提交，事务B又读取该数据M(可能是再次校验)，在同一个事务B中，读取同一个数据M的结果集不同，这个很蛋疼。

例子：

事务A	事务B
读取X=100	读取X=100
写入X=X+100	读取X=100
事务结束，X=200
	读取X=200(在一个事务B中读X的值发生了变化)
	事务结束

幻读

当用户读取某一个范围的数据行时，另一个事务又在该范围内查询了新行，当用户再读取该范围的数据行时，会发现会有新的“幻影行”，例如：事务B读某一个数据M，事务A对数据M增加了一行并提交，事务B又读数据M，发生多出了一行造成的结果不一致(如果行数相同，则是不可重复读)。

例子：

事务A	事务B
	读取数据集M(3行)
在数据集M插入一行(4行)
事务结束
	读取数据M(4行)
	事务结束

在事务B里，同一个数据集M，读到的条数不一致(新增，删除)。

封锁协议

在运用S锁和X锁对数据M加锁的时候，需要约定一些规则，例如何时申请S锁或者X锁，持锁时间，这些规则就是封锁协议。
其中不同的封锁协议对应不同的隔离级别。

一级封锁协议

一级封锁协议对应READ-UNCOMMITTED 隔离级别，本质是在事务A中修改完数据M后，立刻对这个数据M加上共享锁(S锁)[当事务A继续修改数据M的时候，先释放掉S锁，再修改数据，再加上S锁]，根据S锁的特性，事务B可以读到事务A修改后的数据(无论事务A是否提交，因为是共享锁，随时随地都能查到数据A修改后的结果)，事务B不能去修改数据M，直到事务A提交，释放掉S锁。

缺点：
可能会造成如下后果

丢失更新。
脏读。
不可重复读。
幻读。

例子：MySql 5.5 证明一级封锁协议会造成脏读，不可重复读。

A客户端修改数据M，B客户端设置不同的隔离级别去查看数据M，论证该级别下会发生脏读，不可重复读(相当于客户端A修改的数据已经写到表里，客户端B传不同版本号[隔离级别]，去查看数据M，所得的查询结果也不同)。

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，修改事务隔离级别为read-uncommitted，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务B查询数据M。
事务A修改其中一行数据(查询原有基础数据，然后把id = 1 的name 修改为4 )。
事务B查看数据M，发现事务B读到了事务A未提交的数据，发生了脏读。
事务A回滚。
客户端B查询的情况。

在同一个事务B里，查询同一个数据M，居然2次不一样，造成不可重复读，其中有一次数据是无效的数据，脏读了。
假如事务A不回滚呢？那么事务B就没造成脏读，不可重复读。

例子：MySql 5.5 证明一级封锁协议会造成更新丢失

事务A提交数据M。
事务B查询数据M，事务B查询的数据M，没有脏数据，并且2次结果一致，没出现不可重复读。
事务B修改数据M。

此时事务A对数据M的修改被事务B给覆盖，造成了更新丢失。

例子：MySql 5.5 证明一级封锁协议会造成幻读

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，修改事务隔离级别为read-uncommitted，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务B查询数据M。
事务A插入一条数据。
事务B查询数据M。

事务B第二次查询的时候，数据M多了一行，像是发生了幻觉似的，有可能这一行是无效数据(当事务A回滚)。

二级封锁协议

二级封锁协议对应READ-COMMITTED隔离级别，本质是事务A在修改数据M后立刻加X锁，事务B不能修改数据M，同时不能查询到最新的数据M(避免脏读)，查询到的数据M是上一个版本(Innodb MVCC快照)的。

优点：
1.避免脏读。

缺点：
可能会造成如下后果

丢失更新。
不可重复读。
幻读。

例子：MySql 5.5 证明二级封锁协议不会造成脏读，但是会造成不可重复读(幻读，丢失更新，和上面证明方式一样，这里暂不证明了)
A客户端修改数据M，B客户端设置不同的隔离级别去查看数据M，论证该级别下会发生不可重复读(相当于客户端A修改的数据已经写到表里，客户端B传不同版本号[隔离级别]，去查看数据M，所得的查询结果也不同)。

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，修改事务隔离级别为read-committed，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务A修改其中一行数据(查询原有基础数据，然后把id = 1 的name 修改为4 )。
事务B查询数据M，数据还是和之前的一样(没有发生脏读)，事务B读不到了事务A未提交的数据。
事务A提交。
事务B查询数据，数据M被修改。

在同一个事务B中，查询数据M，2次结果不一致，证明发生了不可重复读。

三级封锁协议

三级封锁协议对应REPEATABLE-READ隔离级别,本质是二级封锁协议基础上，对读到的数据M瞬间加上共享锁，直到事务结束才释放（保证了其他事务没办法修改该数据），这个级别是MySql 5.5 默认的隔离级别。

优点：
1.避免脏读。
2.避免不可重复读。

缺点：

幻读。
丢失更新。

例子：MySql 5.5 证明三级封锁协议不会造成脏读，不可重复读(造成幻读，丢失更新，和上面证明方式一样，但是要在非mysql数据库上证明，后面有解释，这里暂不证明了)

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，修改事务隔离级别为repeatable-read，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务A查询数据M。
事务B查询数据M。
事务A更新数据M。
事务B查询数据M，发现查询的结果没变化，避免了脏读。
事务A提交事务。
事务B查询数据M，还是和之前查询的结果一样，没有变化，避免了不可重复读。
事务B提交事务

这个时候事务B才能查询到最新的数据M+。

例子：MySql 5.5 证明Mysql Innodb引擎的三级封锁协议不会造成幻读

mysql innodb的reapetable read级别是避免了幻读，mysql的实现和标准定义的RR隔离级别有差别，详情见 how-to-produce-phantom-reads。

客户端A设置手动提交，并且开启事务A。
客户端B设置手动提交，修改事务隔离级别为repeatable-read，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务A查询数据M。
事务B查询数据M。
事务A对记录M插入一条数据。
事务A提交。
事务B查看数据M。

看不到事务A新增加的一条数据，说明避免了幻读。

事务B插入一条记录。

明明刚刚查询到没有ID为4的，现在居然插不进去，哈哈哈哈哈。

例子：MySql 5.5 证明三级封锁协议在读到数据的瞬间加上共享锁，等事务结束才释放以及三级封锁协议会造成更新丢失

客户端A重新开启事务A。
客户端B设置手动提交，修改事务隔离级别为read-committed，并且开启事务B(一定要在开启事务前修改事务的隔离级别，不然当前还是保持着原来的事务隔离级别，直到当前事务提交)。
事务A修改数据M。
事务B也修改数据M，事务B的修改进程被挂起，因为事务A在对数据M修改后瞬间加上了共享锁，对于其他事务只能读。
事务A提交事务。
事务B的修改进程被唤起(等待4.98秒)。
事务B提交修改。
最终事务A，事务B查询数据M。