一次关于InnoDB在UPDATE时对聚簇索引及二级索引加锁情况的试验

最新推荐文章于 2024-08-15 18:01:53 发布

yue_hu

最新推荐文章于 2024-08-15 18:01:53 发布

阅读量1.9k

点赞数 7

分类专栏： JAVA核心知识数据库小笔记文章标签：数据库 mysql database

本文链接：https://blog.csdn.net/yue_hu/article/details/121874840

版权

JAVA核心知识同时被 3 个专栏收录

64 篇文章 5 订阅

订阅专栏

小笔记

23 篇文章 0 订阅

订阅专栏

数据库

14 篇文章 0 订阅

订阅专栏

先上结论：

通过聚簇索引更新时，会在聚簇索引上加锁。
通过二级索引进行更新时，会先对二级索引加锁，然后对聚簇索引加锁。
使用聚簇索引更新二级索引时，会先对聚簇加锁，再对二级索引加锁。此结论的前提条件为结论4。
更新二级索引时，只有二级索引所在的列产生实际变化的更新，才会对二级索引加锁，否则仅会对聚簇索引加锁。
在REPEATABLE_READ级别下，对索引的加锁范围是索引所确定的范围，而不是最终结果集范围。也就是说需要回表查询才能剔除的行的聚簇索引依然会被加锁。而READ_COMMITTED级别下则不会。

验证过程

数据准备

建表与数据初始化：

CREATE TABLE MY_LOCK_TEST_TABLE(
        ID int not null,
        STATUS VARCHAR(5) not null,
        FLAG CHAR(1) not null,
        NO_INDEX_VAL CHAR(1) not null,
        PRIMARY KEY (ID),
        KEY IDX_ST (STATUS,FLAG)
) ENGINE=InnoDB;
INSERT INTO MY_LOCK_TEST_TABLE VALUES(0,'LOCK0','X','S');
INSERT INTO MY_LOCK_TEST_TABLE VALUES(1,'LOCK1','X','F');

初始数据：
在这里插入图片描述
关闭自动提交;事务隔离级别为READ_COMMITTED。

开始验证

结论1验证：通过聚簇索引更新时，会在聚簇索引上加锁。

执行SQL1-1:

UPDATE MY_LOCK_TEST_TABLE SET STATUS ='LOCKL', FLAG ='Y' WHERE ID = 0;

此时在SQL1-1不提交或回滚的情况下，再次执行SQL1-2：

UPDATE MY_LOCK_TEST_TABLE SET STATUS ='LOCKL', FLAG ='Z' WHERE ID = 0;

发现阻塞，此时查看表：information_schema.INNODB_LOCKS (可以通过此表观察当前数据库的锁等待情况)：这里因为是用的业务系统数据库做的试验，抹掉的库名，后面同理，不再说明
在这里插入图片描述
可以看到SQL1-1和SQL1-2发生了锁等待，因为ID是主键，因此lock_index显示PRIMARY，即对聚簇索引加锁。
通过information_schema.INNODB_LOCK_WAITS可以查看锁等待情况，

可以看到是450在等937。trx_id是事务ID，事务的相关信息如事务隔离级别，事务启动时间，事务锁相关等都可以在information_schema.INNODB_TRX表看到，这里也不在多描述。
由此我们可以得出结论1。
回滚SQL1-1和SQL1-2，复原测试数据.

结论2验证：通过二级索引进行更新时，会先对二级索引加锁，然后对聚簇索引加锁

执行SQL2-1:

UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'T' WHERE STATUS ='LOCK0' AND FLAG ='X';

执行SQL2-2:

UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'W' WHERE STATUS ='LOCK0' AND FLAG ='X';

查看information_schema.INNODB_LOCKS，可以看到因为都是用IDX_ST 作为WHERE条件进行检索，也就是IDX_ST 发生锁等待，在二级索引IDX_ST 上加了锁（lock_index显示IDX_ST ），这里可以证明通过二级索引检索时是先对二级索引加的锁。：
在这里插入图片描述
此时再执行SQL2-3:

UPDATE MY_LOCK_TEST_TABLE SET STATUS ='LOCKL', FLAG ='Y' WHERE ID = 0;

在这里插入图片描述
查看information_schema.INNODB_LOCKS，可以看到除了IDX_ST外，新增了两行PRIMARY，因为SQL2-3导致了聚簇索引的锁等待。以上，可以得到结论：通过二级索引进行更新时，会先对二级索引加锁，然后对聚簇索引加锁。
此时对SQL2-1做回滚操作，可以看到SQL2-3先进行执行，这说明SQL2-2是堵在二级索引锁等待上，还没对聚簇索引加锁，所以SQL2-3能够直接执行，也是证明加锁顺序的一个证据。

结论3验证：使用聚簇索引更新二级索引时，会先对聚簇加锁，再对二级索引加锁

执行SQL3-1:

UPDATE MY_LOCK_TEST_TABLE SET STATUS ='LOCKL', FLAG ='Y' WHERE ID = 0;

为了证明这个结论，不能直接使用WHERE ID = 0做条件，因为会直接堵在聚簇索引锁上，要使用二级索引作为更新条件。执行SQL3-2:

UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'W' WHERE STATUS ='LOCK0' AND FLAG ='X';

查看information_schema.INNODB_LOCKS
在这里插入图片描述
可以看到有IDX_ST的锁记录，这说明SQL3-1确实对二级索引加了锁，可以得出结论3。

结论4验证：更新二级索引时，只有二级索引所在的列产生实际变化的更新，才会对二级索引加锁，否则仅会对聚簇索引加锁

复原测试数据，执行SQL4-1:

UPDATE MY_LOCK_TEST_TABLE SET STATUS ='LOCK0', FLAG ='X' WHERE ID = 0

这个SQL中STATUS ='LOCK0', FLAG ='X'都是更新的原值，也就是二级索引列没有变化，然后执行SQL4-2:

UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'W' WHERE STATUS ='LOCK0' AND FLAG ='X';

查看information_schema.INNODB_LOCKS
在这里插入图片描述
可以看到实际加锁的是PRIMARY，这说明SQL4-1并没有对二级索引加锁，否则根据结论2，SQL4-2会在对二级索引加锁时就被阻塞掉，那么显示的锁应该是IDX_ST。因此我们可以得出结论，只有对二级索引所在的列产生实际变化的更新时，才会对二级索引加锁。对结论3进行延展再结合这个延展，就可以验证结论4。

结论5验证：在REPEATABLE_READ级别下，对索引的加锁范围是索引所确定的范围，而不是最终结果集范围。也就是说需要回表查询才能剔除的行的聚簇索引依然会被加锁。而READ_COMMITTED级别下则不会。

执行SQL5-1:

UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'W' WHERE ID < 3 AND NO_INDEX_VAL = 'S';

在此条件下，只有ID为0的列会命中，因为只有ID为0的列满足WHERE条件，此时执行SQL5-2:

 UPDATE MY_LOCK_TEST_TABLE SET NO_INDEX_VAL = 'W' WHERE ID = 1;

在READ_COMMITTED安然执行，然而如果将事务的隔离级别改为REPEATABLE_READ，重新执行SQL5-1与SQL5-2，就会发现SQL5-2阻塞。
查看information_schema.INNODB_LOCKS：
在这里插入图片描述
可以看到lock_index为PRIMARY，lock_data为1,这说明ID为1的列也被锁了，结论5得到证实，因此需要注意即使使用了索引也可能导致锁定一些不需要的数据。