mysql三级封锁协议_mysql: 三级封锁协议

在前面的文章中我们介绍过MySQL的事务的隔离级别 mysql: 事务的隔离级别。其实事务的隔离级别正是三级封锁协议的体现或运用。怎么说呢，为了提高性能，我们会希望数据库能并行执行我们提交的所有事务，但并行执行事务会带来一系列问题，比如我们之前提到过的脏读，不可重复读，幻读还有第一类更新丢失(指A事务回滚时覆盖了B事务已经提交的数据)和第二类更新丢失等(指A事务提交时覆盖了B事务已经提交的数据)。为了解决这些问题，MySQL事务提出了4个不同的隔离级别，而这些隔离级别的实现本质上就是通过加锁，解锁来实现的。那么我们该何时加锁，占锁多长时间，何时解锁呢？这就是我们今天的主题三级封锁协议。三级封锁协议顾名思义是3个不同级别的封锁协议，它们是以何时加锁，何时解锁来区分的。下面我们看一下具体的定义：一级封锁协议：事务T在修改数据R之前必须先对其加X锁，直到事务结束才释放。事务结束包括正常结束(COMMIT)和非正常结束(ROLLBACK)。 一级封锁协议可以防止丢失修改，并保证事务T是可恢复的。使用一级封锁协议可以解决丢失修改问题。在一级封锁协议中，如果仅仅是读数据不对其进行修改，是不需要加锁的，它不能保证可重复读和不读“脏”数据。

二级封锁协议：在一级封锁协议之上，事务T在读取数据R之前必须先对其加S锁，读完后方可释放S锁。 二级封锁协议除防止了丢失修改，还可以进一步防止读“脏”数据。但在二级封锁协议中，由于读完数据后即可释放S锁，所以它不能保证可重复读。

三级封锁协议 ：在一级封锁协议之上，事务T在读取数据R之前必须先对其加S锁，直到事务结束才释放。 三级封锁协议除防止了丢失修改和不读“脏”数据外，还进一步防止了不可重复读。

在继续往下之前，我们需要先搞清楚什么是X锁，S锁。如果我们直接在网上搜索，我们能得到这样的一个关系：

排它锁 == 写锁 == X锁 ， 共享锁 == 读锁 == S锁

简单理解就是如果我对资源A加上了排它锁，那么我既可以读取资源A，也可以插入或更新资源A，而其他人都无法对资源A再加排它锁或共享锁。 如果我对资源A加上了共享锁，那么所有人都不能再对资源A加上排它锁 (包括我自己)，而其他人也都可以对资源A再加共享锁。但我认为这个等式稍微有些问题，排它锁 == X锁 ， 共享锁 == S锁 它们一般用于对行锁定属于行级锁；而读锁和写锁一般用于对表锁定属于表级锁。也就是说它们的粒度是不一样的。举个栗子：

图一图二

直到我们将读锁释放后 unlock tables; 其它会话才能获取写锁。

对一张表加上写锁后，当前会话即可以读，又可以写，但其它所有会话即不能获取写锁，也不能获取读锁。图三

下面我们看一下X锁和S锁，首先我们需要了解，普通的select语句是不需要加锁的，而insert，update，delete，select ... for update 需要加X锁，select ... lock in share mode 这样的语句会加S锁。下面我们举个栗子:

测试前我们需要先将mysql的自动提交关闭，这样便于测试(注意要在每一个会话都执行该操作，因为这个操作只对当前会话有效):图四图五图六图七图八

前面我们已经说过了，S锁，X锁属于行级锁，我们可以锁定部分数据例如我们锁定所有uid<4的数据:图九

当然这个例子能成功主要是因为uid是主键，MySQL自动给它创建了索引，使用其它的列是不能成功的，比如我们使用uname来测试的话：

图十图十一图十二图十三

了解完X锁和S锁之后三级封锁协议就很简单了。由于每次修改数据之前都需要加X锁，所以能解决第一类更新丢失和第二类更新丢失的问题。但由于一级封锁协议读取数据时不会加S锁，所以可能出现脏读的问题，这个可以对应到读未提交的事务隔离级别。而二级封锁协议会在读取数据的时候加S锁，所以可以避免脏读的问题，但是读完就会释放S锁，所以有不可重复读的问题。三级封锁协议会在事务完成后再释放S锁，所以基本可以避免不可重复读的问题。