MVCC——行级锁实验：sql语句如果发生了索引失效会怎么样

前提：set autocommit=0模拟多线程事务

 

问题：从国上一篇博客我们已经知道，update和insert在什么情况下使用mvcc行级锁不会出现阻塞和什么情况下会出现阻塞，接下来我们考虑将锁与索引结合起来，看看索引在mvcc行级锁中所发挥的作用

 

你需要知道：在innerDB的engine下，mvcc行级锁可能会发生粒度从行的粒度升级为表级别的粒度，严重影响并发，那这种情况是如何发生的呢，如果发生了，我们需要在索引建立和sql优化之间应该如何去做呢？

 

不要着急，我们来看案例：

 

案例一：test2表上进行dml（id为主键，name没有建立索引，普通的列）

 

案例1：

事务1：以主键id为条件进行update，更新成功未提交

事务2：以主键id为条件进行update，条件与事务1不同，更新成功未提交

具体过程如下：

1.事务1先执行，事务1首先获取到锁并给id=16这一行加mvcc行级锁，并拷贝走一份快照去进行更新。并更新成功

2.事务2紧接着事务1执行也开始执行，事务2首先能够获取到锁并给id=15这一行加mvcc行级锁，并拷贝走一份快照去更新并更新成功。

显然我们看到：两者事务都没有阻塞，mvcc行级锁生效，粒度没有增大，锁确实加在了不同的行上，提高了并发。

 

案例2：

事务1：在update以id为条件进行更新并不提交，更新成功

事务2：在update以name为条件进行更新，等待事务1提交而阻塞

 

这时候提交事务1

 

当事务1提交过后：

事务2：等待事务1提交过后更新成功：

具体过程如下：

1.事务1先执行，事务1首先获取到锁并给id=16这一行加mvcc行级锁，并拷贝走一份快照去进行更新。

2.事务2紧接着事务1执行也开始执行，事务2由于索引失效，所以事务2尝试获取整个表的锁并给整张表加锁并之后试图拷贝一份快照去更新，但是发现整个表中的id=16的字段已经上锁，由于排它锁的性质所以只能进入等待。

3.事务1提交成功后释放锁，事务2才可以获取整个表的锁并拷贝快照进行更新。

案例1与案例2小结：我们看到由于name字段上没有索引，只要主键有索引，所以事务2的更新sql语句会造成索引失效，那么对于索引失效的语句，innerDB默认从行锁升级为表锁。

 

案例3：update与insert，insert与insert不会阻塞（如果维持主键自增）

事务1：对test2进行插入操作，插入成功未提交

事务2：对name为12345的字段进行更新，进入阻塞

事务1：事务1提交成功

事务2：在事务1提交后，更新成功

具体过程：与案例1和案例2基本相同

案例3小结：不论是事务1先执行还是事务2先执行，后执行的都会进入阻塞，原因就是锁的粒度上升为表锁。

 

 

案例二：间隙锁（详情尚硅谷mysql高级优化视频60集——63集）

条件中a与b字段不是主键