高级数据库五：浅谈数据库隔离级别与锁机制

痛定思痛。 2022-06-08 10:53 295阅读 0赞

因为数据库中的事务是具有隔离性的，一个事务的运行不应该影响另一个事务的运行。  
但是因为并行机制的存在，会有一系列的问题：

> 脏读：事务A修改了一个数据，但未提交，事务B读到了事务A未提交的更新结果，如果事务A提交失败，事务B读到的就是脏数据。  
> 不可重复读：在同一个事务中，对于同一份数据读取到的结果不一致。比如，事务B在事务A提交前读到的结果，和提交后读到的结果可能不同。  
> 幻读：在同一个事务中，同一个查询多次返回的结果不一致。通常是因为在事务A中进行了一次全局操作，如新增了一条记录；事务B在事务A提交前后各执行了一次查询操作，发现后一次比前一次多了一条记录。

所以我们要有一定的隔离机制来满足数据库并行速度和操作正确性的需求。

## 隔离级别 ##

### 可读取未确认（Read uncommitted） ###

写事务阻止其他写事务，避免了更新遗失。但是没有阻止其他读事务。

存在的问题：脏读。即读取到不正确的数据，因为另一个事务可能还没提交最终数据，这个读事务就读取了中途的数据，这个数据可能是不正确的。

解决办法就是下面的“可读取确认”。

### 可读取确认（Read committed） ###

写事务会阻止其他读写事务。读事务不会阻止其他任何事务。

大部分数据库使用的默认隔离级别，兼顾速度和正确性。

存在的问题：不可重复读。即在一次事务之间，进行了两次读取，但是结果不一样，可能第一次id为1的人叫“李三”，第二次读id为1的人就叫了“李四”。因为读取操作不会阻止其他事务。

解决办法就是下面的“可重复读”。

### 可重复读（Repeatable read） ###

读事务会阻止其他写事务，但是不会阻止其他读事务。

存在的问题：幻读。可重复读阻止的写事务包括update和delete（只给存在的表加上了锁），但是不包括insert（新行不存在，所以没有办法加锁），所以一个事务第一次读取可能读取到了10条记录，但是第二次可能读取到11条，这就是幻读。

解决办法就是下面的“串行化”。

### 可串行化（Serializable） ###

读加共享锁，写加排他锁。这样读取事务可以并发，但是读写，写写事务之间都是互斥的，基本上就是一个个执行事务，所以叫串行化。

### 游标稳定性(CS-Cursor Stability) ###

这种隔离级别已经基本被淘汰了。

游标稳定性隔离级别只锁定事务声明和当前所引用的行。

游标稳定性隔离级别在隔离事务效果方面非常宽松，介于RC和RR之间。它可以防止脏读，但有可能出现不可重复读和幻像读。

在一个事务中，只有正在被读取的那一行（游标指向的行）将被加上[NS锁][NS]，其他未被处理的行上不被加锁。这种隔离级只能保证正在被处理的行的值不会被其他并发的程序所改变。

所获取的锁一直有效，直到游标重定位或事务终止为止，如果游标重定位，原来行上的锁就被释放，并获得游标现在所引用行上的锁。

此外，如果事务修改了它检索到的任何行，那么在事务终止之前，其他事务不能更新或删除该行，即使游标不再位于被修改的行。

与可重复读和读稳定性隔离级别一样，其他事务在其他行上进行的更改，在这些更改提交之前对于使用游标稳定性隔离级别(这是默认的隔离级别)的事务是不可见的。

> 如果如家酒店客房预定程序在游标稳定性隔离级别下运行，那么会有什么影响呢？
> 
> 当一个顾客检索某个日期段内所有可用房间的列表，然后查看产生的列表上每个房间的信息时(每次查看一个房间)，您可以更改旅馆中任何房间的房价，除了该顾客当前正在查看的房间外(对于指定的日期段)。
> 
> 同样，其他顾客可以对任何房间进行或取消预订，但是这个顾客当前正在查看的房间除外(对于指定的日期段)。也就是说，对于第一个顾客当前正在查看的房间记录，您和其他顾客都不能进行任何操作。当第一个顾客查看列表中另一个房间的信息时，您和其他顾客就可以修改他刚才查看的房间记录(如果这个顾客没有预订这个房间的话)。
> 
> ![225350845.jpg][]

能够解决的问题是

![图1][1]

如图所示，在事务2修改A之后，事务1再修改A，则会导致事务二的修改无效，从而丢失信息。所以在事务1读取A的时候就给他加一个cursor lock，直到修改完成并不再需要任何关于A的操作或者COMMIT之后。这样就**有可能**解决这个问题。

### 快照隔离(Snapshot Isolation) ###

这种隔离级别可以保证一个事务中的读操作所读到的东西都是事务开始时的那样。如果事务的写入不与该快照进行的读取有任何并发更新冲突，则事务将在SI下进行提交。

这样会导致WRITE SKEW ANOMALY问题。

![图2][2]

两个SI级别的修改同时发生，他们看到的景象是相同的，他们做出的修改也是不同行的。所以他们的修改可以成功。

但是理论上修改操作效果应该如下图所示：

![图3][3]

### 主流数据库隔离级别 ###

来自[Peter Bailis :: Highly Available, Seldom Consistent][Peter Bailis _ Highly Available_ Seldom Consistent]

<table> 
 <thead> 
  <tr> 
   <th align="center">Database</th> 
   <th align="center">Default Isolation</th> 
   <th align="center">Maximum Isolation</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">Actian Ingres 10.0/10S</td> 
   <td align="center">S</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">Aerospike</td> 
   <td align="center">RC</td> 
   <td align="center">RC</td> 
  </tr> 
  <tr> 
   <td align="center">Akiban Persistit</td> 
   <td align="center">SI</td> 
   <td align="center">SI</td> 
  </tr> 
  <tr> 
   <td align="center">Clustrix CLX 4100</td> 
   <td align="center">RR</td> 
   <td align="center">?</td> 
  </tr> 
  <tr> 
   <td align="center">Greenplum 4.1</td> 
   <td align="center">RC</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">IBM DB2 10 for z/OS CS</td> 
   <td align="center">S</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">IBM Informix 11.50</td> 
   <td align="center">Depends RR</td> 
   <td align="center"></td> 
  </tr> 
  <tr> 
   <td align="center">MySQL 5.6</td> 
   <td align="center">RR</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">MemSQL 1b</td> 
   <td align="center">RC</td> 
   <td align="center">RC</td> 
  </tr> 
  <tr> 
   <td align="center">MS SQL Server 2012</td> 
   <td align="center">RC</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">NuoDB</td> 
   <td align="center">CR</td> 
   <td align="center">CR</td> 
  </tr> 
  <tr> 
   <td align="center">Oracle 11g</td> 
   <td align="center">RC</td> 
   <td align="center">SI</td> 
  </tr> 
  <tr> 
   <td align="center">Oracle Berkeley DB</td> 
   <td align="center">S</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">Oracle Berkeley DB JE</td> 
   <td align="center">RR</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">Postgres 9.2.2</td> 
   <td align="center">RC</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">SAP HANA</td> 
   <td align="center">RC</td> 
   <td align="center">SI</td> 
  </tr> 
  <tr> 
   <td align="center">ScaleDB 1.02</td> 
   <td align="center">RC</td> 
   <td align="center">RC</td> 
  </tr> 
  <tr> 
   <td align="center">VoltDB</td> 
   <td align="center">S</td> 
   <td align="center">S</td> 
  </tr> 
  <tr> 
   <td align="center">Legend</td> 
   <td align="center">RC: read committed<br> RR: repeatable read<br> S: serializability</td> 
   <td align="center">SI: snapshot isolation<br> CS: cursor stability<br> CR: consistent read</td> 
  </tr> 
 </tbody> 
</table>

## 锁机制 ##

### 一次性锁协议 ###

事务开始时，即一次性申请所有的锁，之后不会再申请任何锁，如果其中某个锁不可用，则整个申请就不成功，事务就不会执行，在事务尾端，一次性释放所有的锁。一次性锁协议不会产生死锁的问题，但事务的并发度不高。

### 两阶段锁协议 ###

整个事务分为两个阶段，前一个阶段为加锁，后一个阶段为解锁。在加锁阶段，事务只能加锁，也可以操作数据，但不能解锁，直到事务释放第一个锁，就进入解锁阶段，此过程中事务只能解锁，也可以操作数据，不能再加锁。

两阶段锁协议使得事务具有较高的并发度，因为解锁不必发生在事务结尾。它的不足是没有解决死锁的问题，因为它在加锁阶段没有顺序要求。如两个事务分别申请了A, B锁，接着又申请对方的锁，此时进入死锁状态，如下图所示：

![图4][4]

### 时间戳排序协议 ###

每个事务都有一个唯一的时间戳，也就是其进入系统的时间。

时间戳有大小之分，如果事务Ti比Tj先进入系统，则TS(Ti)

### 参考文献 ###

*  [DB2锁机制][NS]
 *  [Peter Bailis :: Highly Available, Seldom Consistent][Peter Bailis _ Highly Available_ Seldom Consistent]
 *  循序渐进DB2-系统管理、运行维护与应用案例 BY 牛新庄

[NS]: http://www.cnblogs.com/BradMiller/archive/2013/07/18/3197906.html
[225350845.jpg]: /images/20220608/fbd39930f27a4c069c1a4dc70788ef0c.png
[1]: /images/20220608/e46e1d626af54bcabe72147b12120579.png
[2]: /images/20220608/e26e70fdf68544d2b713ae539bf902ac.png
[3]: /images/20220608/cd4f0225b1904df0a42907b2eca09ce0.png
[Peter Bailis _ Highly Available_ Seldom Consistent]: http://www.bailis.org/blog/when-is-acid-acid-rarely/
[4]: /images/20220608/1d5cdacd827a4726852ac3629817dfaf.png