万答1,MySQL中如何查询某个表上的 [复制链接]

问题

问题原文是这样的：

假如在MySQL事务里，给某个表的一行加了共享锁，理论上这个表本身会自动加上意向共享锁，那么能不能用sql查出这个表加了意向锁？

回答

答案是肯定的，当然可以执行SQL查询表上的IS锁加锁状态。

先声明，我们本次讨论的是MySQL里的InnoDB引擎表，下面讨论的内容都是基于这个前提。

在揭晓答案之前，多介绍点InnoDB引擎锁相关的一些知识吧。主要有以下几点

InnoDB引擎表既支持表级锁，也支持行级锁。加表级锁的方法和MyISAM表是一样的，执行LOCKTABLEREAD/WRITE即可。InnoDB表的行锁是加在索引上的，因此如果没有合适的索引，是会导致表里所有记录都被加上行锁，其后果等同于表级锁，但产生的影响比表级锁可就大多了。因为锁对象数量大了很多，消耗的内存也多很多。加上行锁时，同时还需要对表加上相应的意向锁。例如，想要对一行数据加上共享锁（S锁），则相应的要对表加上意向共享锁（IS锁）；同样地，想要对一行数据加上排他锁（X锁），则相应的要对表加上意向排他锁（IX锁）。意向锁是加在聚集索引的根节点上的，因此无论锁定多少行，只需要加一个意向锁。下面是几个锁之间的兼容矩阵ISIXSXIS+++-IX++--S+-+-X----

好了，接下来我们来看下怎么查看表级IS锁。其实很简单，只需要查看PFS.data_locks表就可以了。另一个表PFS.metadata_locks表可以查看MDL锁的详情。

session1session2begin;--先加上共享行锁，此时也会对t1表加上IS锁select*fromt1wherec1=1forshare;--观察到IS锁select*fromperformance_schema.data_locks;

查询结果例如下面这样：

[root

yejr.run][(none)]select*fromperformance_schema.data_locks\G***************************1.row***************************ENGINE:INNODBENGINE_LOCK_ID:::ENGINE_TRANSACTION_IDHREAD_ID7EVENT_ID:95OBJECT_SCHEMA:yejrOBJECT_NAME:t1PARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:NULLOBJECT_INSTANCE_BEGINOCK_TYPEABLELOCK_MODE:ISLOCK_STATUS:GRANTEDLOCK_DATA:NULL***************************2.row***************************ENGINE:INNODBENGINE_LOCK_ID:::4:9:ENGINE_TRANSACTION_IDHREAD_ID7EVENT_ID:95OBJECT_SCHEMA:yejrOBJECT_NAME:t1PARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAMERIMARYOBJECT_INSTANCE_BEGINOCK_TYPE:RECORDLOCK_MODE:S,REC_NOT_GAPLOCK_STATUS:GRANTEDLOCK_DATA:1

此时我们能看到t1表上共有两个锁，一个是表级IS锁，另一个是c1=1上的共享锁。

同样地，我们也可以观察IX锁或其他锁。

-session1执行下面的SQL[root

yejr.run][yejr]begin;updatet1setc4=rand()*wherec1=1;-session2查询PFS.data_locks[root

yejr.run][(none)]select*fromperformance_schema.data_locks\G***************************1.row***************************ENGINE:INNODBENGINE_LOCK_ID:::ENGINE_TRANSACTION_IDHREAD_ID9EVENT_ID:43OBJECT_SCHEMA:yejrOBJECT_NAME:t1PARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAME:NULLOBJECT_INSTANCE_BEGINOCK_TYPEABLELOCK_MODE:IX--这个就是IX锁了LOCK_STATUS:GRANTEDLOCK_DATA:NULL***************************2.row***************************ENGINE:INNODBENGINE_LOCK_ID:::4:9:ENGINE_TRANSACTION_ID:THREAD_ID9EVENT_ID:43OBJECT_SCHEMA:yejrOBJECT_NAME:t1PARTITION_NAME:NULLSUBPARTITION_NAME:NULLINDEX_NAMERIMARYOBJECT_INSTANCE_BEGINOCK_TYPE:RECORDLOCK_MODE,REC_NOT_GAPLOCK_STATUS:GRANTEDLOCK_DATA:1

进一步，我们简单看下MDL锁。加共享行锁：

-session1加一个共享行锁[root

yejr.run][yejr]begin;select*fromt1wherec1=1forshare;-session2查询表上有哪些MDL锁[root

yejr.run][(none)]select*fromperformance_schema.metadata_locks\G***************************1.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:yejrOBJECT_NAME:t1COLUMN_NAME:NULLOBJECT_INSTANCE_BEGINOCK_TYPE:SHARED_READ-共享读锁，可以同时加多个共享行锁LOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:sql_parse.cc:OWNER_THREAD_ID7OWNER_EVENT_IDp>也看下加排他行锁：

-session1加一个排他行锁[root

yejr.run][yejr]begin;updatet1setc4=rand()*wherec1=1;-session2查询表上有哪些MDL锁[root

yejr.run][(none)]select*fromperformance_schema.metadata_locks\G***************************1.row***************************OBJECT_TYPE:TABLEOBJECT_SCHEMA:yejrOBJECT_NAME:t1COLUMN_NAME:NULLOBJECT_INSTANCE_BEGIN:LOCK_TYPE:SHARED_WRITE-共享写锁，可以同时加多个排他行锁（不同数据行）LOCK_DURATION:TRANSACTIONLOCK_STATUS:GRANTEDSOURCE:sql_parse.cc:OWNER_THREAD_ID:89OWNER_EVENT_ID:43

好了，方法已有，更多的情形可以自己去玩了：）

测试环境

Serverversion:8.0.23MySQLCommunityServer-GPL

上述PFS查看行锁、MDL锁的功能应该是8.0以上就开始支持了。

EnjoyMySQL：）

文章推荐：

MySQLcaching_sha2_password认证异常问题分析

故障案例

慢SQL引发MySQL高可用切换排查全过程

技术分享

万里数据库MGRBug修复之路

技术分享

在MySQL对于批量更新操作的一种优化方式

技术分享

MySQLMGR看着很美，却又为什么不敢用？

产品

GreatSQL，打造更好的MGR生态

扫码添加GreatSQL社区助手