​​​​​​​ 事务的隔离级别以及Mysql事务的使用

 

事务的隔离级别以及Mysql事务的使用

 

转载自:http://fanyilun.me/2015/12/29/%E4%BA%8B%E5%8A%A1%E7%9A%84%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB%E4%BB%A5%E5%8F%8AMysql%E4%BA%8B%E5%8A%A1%E7%9A%84%E4%BD%BF%E7%94%A8/

 

目录

1. 前言
2. ACID
3. 事务的隔离级别
4. 传统的隔离级别实现
5. MySql的隔离级别
   1. MySql事务中的读
6. MySql的REPEATABLE_READ隔离级别
7. 悲观锁解决丢失更新的问题
8. 乐观锁解决丢失更新的问题

 

前言

因为最近在做数据库和搜素引擎同步相关的工作，学习了数据库事务相关的知识。事务相关的知识，尤其是数据库内的各种锁机制也是相当复杂的，在此仅作为一个初学者和使用者的角度作为一个分析和整理。整理过程中我放弃了许多书面而难懂的定义解释（网上很多资料杂乱难懂废话又多还不一定对），尝试找到比较容易理解的解释。有问题的话也欢迎修正。

ACID

首先作为数据库事务，需要保障的性质如下：

• 原子性：要么全部完成，要么全部不完成。
• 持久性：事务提交后即持久化到磁盘不会丢失。
• 一致性：一个事务单元需要提交之后才会被其他事务可见。
• 隔离性：设立了不同程度的隔离级别，通过适度的破坏一致性，得以提高性能。

事务的隔离级别

在复习隔离级别之前，先看一下几个与之相关的定义：

• 脏读：一个事务读取了其他事务还没有提交的变更
• 不可重复读：同样的条件，读取过的数据，再次读取出来发现值不一样了。（因为另一个事务对该条件的数据 修改提交了）
• 幻读：同样的条件，第一次和第二次读出来的记录数不一样。（因为另一个事务对该条件的数据 插入/删除 提交了）
• 第一类丢失更新：一个事务的回滚覆盖了另一个事务提交的update。
• 第二类丢失更新：一个事务提交的update覆盖了另一个事务提交的update。

而数据库的隔离级别可以看做这几种条件满足的组合：

隔离级别	脏读	不可重复读	幻读	第一类丢失更新	第二类丢失更新
读未提交	允许	允许	允许	不允许	允许
读已提交	不允许	允许	允许	不允许	允许
可重复读	不允许	不允许	允许	不允许	不允许
序列化	不允许	不允许	不允许	不允许	不允许

传统的隔离级别实现

看到上面的标准，我一定要介绍一下传统的基于锁的隔离级别的实现方式。因为SQL隔离级别的标准就是依据这种实现方式制定的。
首先还是要简单介绍一下锁：在传统数据库实现中，读操作通常加共享锁（S锁），写操作加排它锁（X锁）。假设A进程对某条记录先获取了锁，下图表示B进程能否拿到锁：

进程A已加的锁	B获取S锁	B获取X锁
S锁	能	不能
X锁	不能	不能

此时四种隔离级别本质的区别其实就是加锁方式的不同，我认为这也是对传统隔离级别更好的理解：

• 序列化：读写都加排它锁，锁的粒度到表，使得读写严格串行。
• 可重复读：读加共享锁，写加排它锁。锁的粒度到行，因此可能幻读。满足了读读并行。
• 读已提交：在可重复读的基础上，读锁可以被其他事务的写锁升级（即共享锁变成了排它锁），可以满足读写并行（写读还不能并行）。允许了持有读锁的数据被修改，因此不可重复读。
• 读未提交：不加锁。此时满足了读读，读写，写读并行。

MySql的隔离级别

需要明确的是，以上的ACID和隔离级别定义是在SQL规范层面的定义，不同数据库的实现方式和使用方式并不相同。（类似于JVM规范和JVM厂商的关系）

上面的隔离级别标准是SQL92 基于读写锁 的实现方式制定的规范，可惜之后的MVCC成为了各大厂商的主流实现方式（实际上可以理解为第五个隔离级别：SNAPSHOT ISOLATION），为了兼容落后的规范，数据库引擎们都想办法“贴”近四大隔离级别，但是和标准的预期肯定不会完全一致的。
除了MySql，包括Oracle在内的大多数数据库的默认隔离级别都是READ_COMMITED，而MySql的默认事务隔离级别是REPEATABLE_READ。（以下关于MySql的说明均指InnoDB引擎下的MySql）

MySql事务中的读

1	select * from table where ?;

MVCC实现的数据库引擎和传统数据库最大的区别其实就是上面这句读操作。普通的select语句会被执行快照读：如果数据被锁，直接通过undo log读到它被锁之前的副本。
这种方式极大的提高的读操作的性能，带来的弊端就是没有严格的保证写读的先后顺序关系。
对于RC隔离级别，快照读总是读取对应行的最新版本，但如果该行被锁住，则会读取该行版本最近一次快照。（因此不能保证可重复读）
对于RR隔离级别，快照读总是读取事务开始时刻的行版本数据。

MySql的REPEATABLE_READ隔离级别

由于实现方式已经偏离了标准的预期，MySql没有严格遵守SQL92规范，在REPEATABLE_READ级别中，不会出现幻读，而且第二类丢失更新需要用户自己加锁实现。

从隔离级别规范上看，它阻止了脏读和不可重复读，这是通过MVCC多版本并发控制解决的。解决脏读的办法是consistent read，通过时间戳避免读到事务提交之前的操作。对于不可重复读，在consistent read的基础上，让一个事务的每次读操作 都去读该事务第一次读操作时的快照版本。详见http://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html
对于幻读，尽管SQL规范中，REPEATABLE READ允许幻读，但是MySql的REPEATABLE READ级别上不会出现幻读，因为它使用了间隙锁（Next-key lock）解决了幻读的问题。详见 http://dev.mysql.com/doc/refman/5.7/en/innodb-record-level-locks.html

那么。如果想解决第二类丢失更新（read-modify-write），应该怎么办呢：

悲观锁解决丢失更新的问题

举个例子：

1 2 3 4 5

begin; SET @a = (SELECT restnum FROM book WHERE id =1) SET @b = function(@a) //抽象为某个业务逻辑操作 UPDATE book SET restnum=@b WHERE id =1 commit;

类似于上面的代码套进事务中，其实在传统数据库的REPEATABLE_READ下是安全的；如果数据库使用了更新锁(U锁)，会在第一个读操作上直接加排它锁，避免了丢失更新（否则会死锁）。但是现代数据库引擎都是基于MVCC实现的，即使数据被排它锁锁住，第一次读也不会被阻塞而是读到旧数据，到第二步写的时候就会写入旧数据计算出的结果。

因此在REPEATABLE_READ级别下，我们需要手动加锁。正确的写法是：

1 2 3 4 5

begin; //注意要关闭auto commit SET @a = (SELECT restnum FROM book WHERE id =1 FOR UPDATE) SET @b = function(@a) //抽象为某个业务逻辑操作 UPDATE book SET restnum=@b WHERE id =1 commit;

 

如上，SELECT…FOR UPDATE 可以直接在数据上加排他锁（如果where指定了主键的话会锁行，否则会锁表），直到该事务结束（提交或回滚）。
与此类似的语句还有SELECT … LOCK IN SHARE MODE是共享锁，也就是传统的S锁。显然，此处如果使用共享锁会发生典型的死锁。
MySql的SERIALIZABLE隔离级别就是在REPEATABLE READ的基础上，把所有纯SELECT操作加上LOCK IN SHARE MODE。也就是把SELECT从快照读变成了当前读，因此不要天真的以为真的会“序列化”执行。

乐观锁解决丢失更新的问题

在实际使用中，我们其实很少使用悲观锁，因为阻塞可能会造成未知的性能问题。使用乐观锁，通常是通过版本号或者时间戳，在最后提交时进行比对：

SELECT (restnum,version) FROM book WHERE id =1)
...... //业务逻辑操作 
UPDATE  book SET restnum=@xx WHERE  id =1 AND version = @version

当然，理想状态下，最好能直接用一个UPDATE语句实现，因为UPDATE会加排他锁（或者说是更新锁→排它锁），也就是不会出现上面的问题了。

← prev • next →

© 2019 Yilun Fan. All rights reserved. Powered by Hexo. Crisp theme.