行级锁实验: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