关于mysql:万答1MySQL中如何查询某个表上的IS意向共享锁

30次阅读

共计 4160 个字符，预计需要花费 11 分钟才能阅读完成。

欢送来到 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

好了，办法已有，更多的情景能够本人去玩了：）

测试环境

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 公布！

正文完
mysql
发表至： mysql
2021-12-08
 0
关于mysql:三高Mysql-搭建三高架构之复制
关于mysql:聊聊mysql中的int1
关于mysql:MySQL-流式查询的用法和坑
佛系笔记mysql命令备忘