
万答#1,MySQL中如何查询某个表上的IS(意向共享)锁
问题
问题原文是这样的:
假如在MySQL事务里,给某个表的一行加了 共享锁,理论上这个表本身会自动加上意向共享锁,那么能不能用 sql 查出这个表加了意向锁?
回答
答案是肯定的,当然可以执行SQL查询表上的IS锁加锁状态。
先声明,我们本次讨论的是MySQL里的InnoDB引擎表,下面讨论的内容都是基于这个前提。
在揭晓答案之前,多介绍点InnoDB引擎锁相关的一些知识吧。主要有以下几点
● InnoDB引擎表既支持表级锁,也支持行级锁。
● 加表级锁的方法和MyISAM表是一样的,执行 LOCK TABLE READ/WRITE 即可。
● InnoDB表的行锁是加在索引上的,因此如果没有合适的索引,是会导致表里所有记录都被加上行锁,其后果等同于表级锁,但产生的影响比表级锁可就大多了。因为锁对象数量大了很多,消耗的内存也多很多。
● 加上行锁时,同时还需要对表加上相应的意向锁。例如,想要对一行数据加上共享锁(S锁),则相应的要对表加上意向共享锁(IS锁);同样地,想要对一行数据加上排他锁(X锁),则相应的要对表加上意向排他锁(IX锁)。
● 意向锁是加在聚集索引的根节点上的,因此无论锁定多少行,只需要加一个意向锁。
● 下面是几个锁之间的兼容矩阵
好了,接下来我们来看下怎么查看表级IS锁。其实很简单,只需要查看 PFS.data_locks 表就可以了。另一个表 PFS.metadata_locks 表可以查看MDL锁的详情。
查询结果例如下面这样:
此时我们能看到t1表上共有两个锁,一个是表级IS锁,另一个是c1=1上的共享锁。
同样地,我们也可以观察IX锁或其他锁。
进一步,我们简单看下MDL锁。加共享行锁:
也看下加排他行锁:
好了,方法已有,更多的情形可以自己去玩了 :)
测试环境
Server version: 8.0.23 MySQL Community Server - GPL
上述PFS查看行锁、MDL锁的功能应该是8.0以上就开始支持了。
