行级锁实验:sql语句条件中的索引对锁的影响
问题：在innerDB的engine下,mvcc行级锁可能会发生粒度从行级别升级为表级别的情况，严重影响并发，那这种情况是如何发生的呢？
验证步骤：
前提：建表（id为主键，name没有建立索引，age建立了索引）
create table test ( id int auto_increment, name varchar(64) , age int , PRIMARY key (id), KEY IX_AGE (age) );
插入一些记录
insert into test(id ,name,age) value(1,'xx',1),(2,'xx',2),(3,'chen',3),(4,'chen',4), (5,'123456',5),(6,'123456',6),(7,'123456',7);
取消自动提交
set autocommit=0;
案例1
| session1 | session2 |
|
事务1：以主键id为条件进行update，更新成功未提交 [SQL]update test set name='yy' where id = 1 ; Affected rows: 0 Time: 0.001s |
事务2：以主键id为条件进行update，条件与事务1不同，更新成功未提交 [SQL]update test set name ='zz' where id = 2; Affected rows: 1 Time: 0.002s |
| commit; | commit; |
具体过程如下
1、事务1先执行，事务1首先获取到锁并给id=1这一行加行级锁，并拷走一份快照去进行更新。并更新成功
2、事务2紧接着事务1也开始执行，事务2首先能够获取到锁并给id=2这一行加行级锁，并拷贝走一份快照去更新并更新成功
我们可以看到，两者事务都没有阻塞，行级锁生效，粒度没有增大，锁确实加在了不同的行上，提高了并发
案例2
| session1 | session2 |
|
事务1： 以主键id为条件进行update,更新成功未提交 [SQL]update test set name='aa' where id = 5 ; Affected rows: 1 Time: 0.002s |
事务2：以name为条件进行更新，等待事务1提交而阻塞 [SQL]update test set name ='harara' where name = 'chen'; |
|
#事务1提交 commit; |
#事务1提交后，事务2更新成功
[SQL]update test set name ='harara' where name = 'chen'; Affected rows: 2 Time: 0.002s |
| commit; |
具体过程如下
1.事务1先执行，事务1首先获取到锁并给id=5这一行加mvcc行级锁，并拷走一份快照去进行更新
2.事务2紧接着事务1开始执行，事务2由于索引失效，所以事务2尝试获取整个表的锁并给整张表加锁之后试图拷贝一份快照去更新，但是发现整个表中id=5的字段已经上锁，由于排它锁的性质所以只能进入等待
3.事务1提交成功后释放锁，事务2才可以获取整个表的锁并拷贝快照进行更新
案例1与案例2小结：我们看到由于name字段上没有索引，innerDB默认从行锁升级为表锁
案例3：update与insert,insert与insert不会阻塞(如果维持主键自增)
| session1 | session2 |
|
事务1：对test表插入一条记录，插入成功未提交 [SQL]insert into test(name,age) values('测试',18); Affected rows: 1 Time: 0.023s |
事务2：对name为123456的字段进行更新，进入阻塞 [SQL]update test set name ='654321' where name='123456'; |
|
#事务1提交 commit; |
#事务1提交后，事务2更新成功 [SQL]update test set name ='654321' where name='123456'; Affected rows: 2 Time: 22.221s |
|
commit; |
案例3小结：无论是事务1还是事务2先执行，后执行的都会进入阻塞，原因就是锁的粒度上升为表锁
说明：如果锁的时间太长，会报异常
间隙锁
【什么是间隙锁】
当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于健值在条件范围内但并不存在的记录，叫做"间隙(GAP)"，
InnoDB也会对这个"间隙"加锁，这种锁机制就是所谓的间隙锁(Next-Key锁)
| session1 | session2 |
|
事务1：以age为条件，age在(2,5)内的数据进行更新，更新成功，先不提交 [SQL]update test set name=CONCAT(name,'-',age) where age>2 and age<5 ; Affected rows: 2 Time: 0.002s |
事务2：插入age为3（在2和5之间）的数据，阻塞产生，暂时不能插入
[SQL]insert into test(name,age) values('3',3); |
|
#事务1提交 commit; |
#事务1提交成功后，事务2数据插入成功 [SQL]insert into test(name,age) values('3',3); Affected rows: 1 Time: 12.843s |
| commit; |
参考地址
MVCC——行级锁实验：sql语句如果发生了索引失效会怎么样：https://blog.csdn.net/whathellll/article/details/82111271
