MySQL之Lock探索(二)-白红宇

联动贴：

http://blog.itpub.net/29510932/viewspace-1814690/

----------------------------

---------

-----------------------------------------接前文------------------------------------------------------------------------------------------------

前文写了一些锁相关的东西，后半篇写一些各种情况下的锁的情况

-------------------------------------------------------------------------------正文------------------------------------------------------------------------------------------------

测试方法：两个session分别执行简单的insert/delete/update语句，模拟锁的情况

测试环境：官方MySQL-5.6.26，隔离级别REPEATABLE-READ和READ COMMITTED

测试内容：测试表student，试验分为六组，RR，RC两种隔离级别，唯一索引，非唯一索引，无索引三种情况，数据和表结构如下（根据实验的要求，可能简单改一下数据）

1.RR隔离级别，非唯一索引

session1:

delete from student where sid = 10005;

session2:

insert into student values(10006,'se10006');

insert into student values(10008,'se10008');

insert into student values(10010,'se10010');

分析：

从MySQL实现的角度来看，在sid上具备索引，根据MySQL加锁的特点，session1会在索引sid上加上X锁，

但是sid是非唯一索引，如果只对sid=10005这一条记录加锁，并不能阻止其他的事务继续插入sid=10005的数据，从而会误删其他事务插入的sid=10005的数据，

所以MySQL会在10005的前后加上GAP锁（间隙锁），阻止其他事务插入sid处于10005-10007和10007-10009区间的任何数据

加锁的示意图如下

所以10006和10008的数据刚好在GAP锁的范围之内，mysql锁的信息

10010在GAP锁的范围之外（10010既不在10005-10007，也不再10007-10009范围内），所以能够正常的插入

2.RR隔离级别，唯一索引

session1:
delete from student where sid = 10005;
session2:
insert into student values(10006,'se10006');
insert into student values(10008,'se10008');
insert into student values(10010,'se10010');

分析：
从MySQL实现的角度来看，在sid上具备索引，根据MySQL加锁的特点，session1会会在索引sid上加上X锁，
由于sid是唯一索引，所以对与MySQL来说，它能确认在这个索引上，至多只会存在一条数据满足sid=10005，

唯一索引本身的特性会阻止其他的事务继续插入sid=10005的数据，从而防止误删其他事务插入的sid=10005的数据，
所以MySQL只需要在10005加上X锁，而无需加上GAP锁，锁住10005前后的间隙，所以session2的所有语句都不会阻塞。
加锁的示意图如下

MySQL的信息

3.RR隔离级别，无索引
session1:
delete from student where sid = 10005;
session2:
insert into student values(10004,'se10004');
insert into student values(10008,'se10008');
insert into student values(10010,'se10010');

分析：

从MySQL实现的角度来看，在student表上没有索引，根据MySQL本身的特点，MySQL会自动创建一个隐藏的聚簇索引作为主键，

当delete操作执行时，student表不存在任何索引，所以MySQL会用X锁锁住所有的行，锁的标记位在自动创建的聚簇索引上；

当session2试图修改or操作student表的数据时，无法在聚簇索引上加锁，进行锁等待。

加锁示意图如下

MySQL的信息

可以看到由于不存在索引，session1锁住了整个表的所有行，而session2则在等待X锁的释放，X锁是加在自动生成的聚簇索引"GEN_CLUST_INDEX"上。

session2的其他语句有同样的效果，图略去。

4.RC隔离级别，无索引
session1:
delete from student where sid = 10005;
session2:
insert into student values(10006,'se10006');

insert into student values(10005,'se10005');

分析：RC隔离级别，无索引的情况，session1依然会对表的所有行加上X锁，session2的操作会被阻塞

加锁情况：

然而实际情况如下

这是因为MySQL在处理这种锁的情况的时候，会去过滤实际需要锁定的行（sid=10005），然后释放掉不需要的那些行的锁，实际上对所有行的加锁操作还是存在的；

5.RC隔离级别，非唯一索引
session1:
delete from student where sid = 10005;
session2:
insert into student values(10006,'se10006');

update student set sname = '1111' where sid = 10005;

update student set sname = '1111' where sid = 10006;
分析：
RC隔离级别，非唯一索引的情况，session1只会对表的符合条件的行加上X锁，这里补上两个update来看一下X锁的情况

session2的执行情况，可以看到同样在操作10005的时候，由于锁等待，对10005的update操作被阻塞了，但是对10006的操作是正常的

加锁情况同4

锁情况

6.RC隔离级别，唯一索引
session1:
delete from student where sid = 10005;
session2:
insert into student values(10006,'se10006');

update student set sname = '1111' where sid = 10005;

update student set sname = '1111' where sid = 10006;
分析：
同情况5，只会对符合条件的行加上X锁。

加锁情况同4

7.RR隔离级别
这个情况单独写出来：当delete语句指定的行，在表当中找不到的时候，会有特殊的处理

表内容小改：

session1:
delete from student where sid = 10007;
session2:
insert into student values(10006,'se10006');
insert into student values(10008,'se10008');
insert into student values(10010,'se10010');

分析：student表存在唯一索引，在这种情况下，因为表中不存在这一行数据，所以MySQL会找到>10007且不符合条件的第一行数据，加上X锁，然后在这一条数据的前面加上GAP锁

加锁示意图

mysql的信息

当存在非唯一索引时，与唯一索引是一样的加锁策略，

在无索引的情况下，则是锁住表的全部行。

PS:写在最后，产生死锁的原因，是因为两个事务都需要同时持有多个锁，但是他们分别持有对方需要的一部分锁。

---------------------------------------------------------------------------------结尾----------------------------------------------------------------------------------------------

前几个试验的时候，session2的insert貌似选的不是很合理，不过实际上GAP锁确实是有加持，在后面的第七种情况里面也能看出来

终于填完了锁的坑，接下来还有metadata lock， index_merge，还有啥来着......太多了....._(:з」∠)_