查看原文
其他

《叶问》31期,MySQL中如何查询某个表上的IS(意向共享)锁

叶金荣 老叶茶馆 2023-03-10

关 于 叶 问


《叶问》是知数堂新设计的互动栏目,不定期给大家提供技术知识小贴士,形式不限,或提问、或讨论均可,并在当天发布答案,让大家轻轻松松利用碎片时间就可以学到最实用的知识点。

问题

问题原文是这样的:

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

回答

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

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

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

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

ISIXSX
IS+++-
IX++--
S+-+-
X----

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

session 1session 2
begin;
-- 先加上共享行锁,此时也会对t1表加上IS锁
select * from t1 where c1=1 for share;


-- 观察到IS锁
select * from performance_schema.data_locks;

查询结果例如下面这样:

[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 :)


延伸阅读




点击文末“阅读原文”直达「叶问」专栏

您可能也对以下帖子感兴趣

文章有问题?点此查看未经处理的缓存