欢迎来到 GreatSQL社区分享的MySQL技术文章,如有疑问或想学习的内容,可以在下方评论区留言,看到后会进行解答问题
问题原文是这样的:
假如在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锁的详情。
文章图片
查询结果例如下面这样:
[root@yejr.run] [(none)]>select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140701134495048:1350:140701396637648
ENGINE_TRANSACTION_ID: 422176111205704
THREAD_ID: 87
EVENT_ID: 95
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701396637648
LOCK_TYPE: TABLE
LOCK_MODE: IS
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140701134495048:267:4:9:140701409130528
ENGINE_TRANSACTION_ID: 422176111205704
THREAD_ID: 87
EVENT_ID: 95
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140701409130528
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
此时我们能看到t1表上共有两个锁,一个是表级IS锁,另一个是c1=1上的共享锁。
同样地,我们也可以观察IX锁或其他锁。
- session1执行下面的SQL
[root@yejr.run] [yejr]>begin;
update t1 set c4=rand()*1024 where c1=1;
- session2查询PFS.data_locks
[root@yejr.run] [(none)]>select * from performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140701134495888:1350:140701396639728
ENGINE_TRANSACTION_ID: 104536
THREAD_ID: 89
EVENT_ID: 43
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701396639728
LOCK_TYPE: TABLE
LOCK_MODE: IX<-- 这个就是IX锁了
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 140701134495888:267:4:9:140701409135136
ENGINE_TRANSACTION_ID: 104536
THREAD_ID: 89
EVENT_ID: 43
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140701409135136
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
进一步,我们简单看下MDL锁。加共享行锁:
- session1加一个共享行锁
[root@yejr.run] [yejr]>begin;
select * from t1 where c1=1 for share;
- session2查询表上有哪些MDL锁
[root@yejr.run] [(none)]>select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701215694512
LOCK_TYPE: SHARED_READ<- 共享读锁,可以同时加多个共享行锁
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5761
OWNER_THREAD_ID: 87
OWNER_EVENT_ID: 100
也看下加排他行锁:
- session1加一个排他行锁
[root@yejr.run] [yejr]>begin;
update t1 set c4=rand()*1024 where c1=1;
- session2查询表上有哪些MDL锁
[root@yejr.run] [(none)]>select * from performance_schema.metadata_locks\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: yejr
OBJECT_NAME: t1
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140701215694640
LOCK_TYPE: SHARED_WRITE<- 共享写锁,可以同时加多个排他行锁(不同数据行)
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:5761
OWNER_THREAD_ID: 89
OWNER_EVENT_ID: 43
好了,方法已有,更多的情形可以自己去玩了 :)
【万答#1,MySQL中如何查询某个表上的IS(意向共享)锁】测试环境
Server version: 8.0.23 MySQL Community Server - GPL
上述PFS查看行锁、MDL锁的功能应该是8.0以上就开始支持了。
Enjoy MySQL :)
文章推荐: 技术分享 | MGR最佳实践(MGR Best Practice)
https://mp.weixin.qq.com/s/66...
技术分享 | 万里数据库MGR Bug修复之路
https://mp.weixin.qq.com/s/Ia...
Macos系统编译percona及部分函数在Macos系统上运算差异
https://mp.weixin.qq.com/s/jA...
技术分享 | 利用systemd管理MySQL单机多实例
https://mp.weixin.qq.com/s/iJ...
产品 | GreatSQL,打造更好的MGR生态
https://mp.weixin.qq.com/s/By...
产品 | GreatSQL MGR优化参考
https://mp.weixin.qq.com/s/5m...
关于 GreatSQL GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。
Gitee:
https://gitee.com/GreatSQL/Gr...
GitHub:
https://github.com/GreatSQL/G...
微信&QQ群:
可扫码添加GreatSQL社区助手微信好友,发送验证信息“加群”加入GreatSQL/MGR交流微信群,亦可直接扫码加入GreatSQL/MGR交流QQ群。
文章图片
本文由博客一文多发平台 OpenWrite 发布!
推荐阅读
- 只要9.9元!零基础学习MySQL
- 万答#21,如何查看 MySQL 数据库一段时间内的连接情况
- 技术分享|闪回在MySQL中的实现和改进
- 万答#20,索引下推如何进行数据过滤
- linux 磁盘io利用率高,分析的正确姿势
- 技术分享 | Prometheus+Grafana监控MySQL浅析
- 万答#19,MySQL可以禁用MyISAM引擎吗()
- 技术分享|sysbench 压测工具用法浅析
- MySQL金融应用场景下跨数据中心的MGR架构方案(1)
- MySQL金融应用场景下跨数据中心的MGR架构方案(2)