《叶问》31期，MySQL中如何查询某个表上的IS（意向共享）锁

ゝ一纸荒年。 2023-01-18 15:29 25阅读 0赞

**关 于 叶 问**

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

## 问题 ##

问题原文是这样的：

> 假如在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锁的详情。

<table> 
 <thead> 
  <tr> 
   <th>session 1</th> 
   <th>session 2</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>begin;<br>-- 先加上共享行锁，此时也会对t1表加上IS锁<br>select * from t1 where c1=1 for share;</td> 
   <td><br></td> 
  </tr> 
  <tr> 
   <td><br></td> 
   <td>-- 观察到IS锁<br>select * from performance_schema.data_locks;</td> 
  </tr> 
 </tbody> 
</table>

查询结果例如下面这样：

[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锁的功能应该是5.7以上就开始支持了。

Enjoy MySQL ：）