万答#1，MySQL中如何查询某个表上的IS（意向共享）锁

最新推荐文章于 2024-10-31 15:30:58 发布
原创 最新推荐文章于 2024-10-31 15:30:58 发布 · 1k 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql

欢迎来到 GreatSQL社区分享的MySQL技术文章，如有疑问或想学习的内容，可以在下方评论区留言，看到后会进行解答

问题

问题原文是这样的：

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

回答

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

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

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

  • InnoDB引擎表既支持表级锁，也支持行级锁。

  • 加表级锁的方法和MyISAM表是一样的，执行 LOCK TABLE READ/WRITE 即可。

  • InnoDB表的行锁是加在索引上的，因此如果没有合适的索引，是会导致表里所有记录都被加上行锁，其后果等同于表级锁，但产生的影响比表级锁可就大多了。因为锁对象数量大了很多，消耗的内存也多很多。

  • 加上行锁时，同时还需要对表加上相应的意向锁。例如，想要对一行数据加上共享锁（S锁），则相应的要对表加上意向共享锁（IS锁）；同样地，想要对一行数据加上排他锁（X锁），则相应的要对表加上意向排他锁（IX锁）。

  • 意向锁是加在聚集索引的根节点上的，因此无论锁定多少行，只需要加一个意向锁。

  • 下面是几个锁之间的兼容矩阵

file

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

file

查询结果例如下面这样：

[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/66u5K7a9u8GcE2KPn4kCaA

技术分享 | 万里数据库MGR Bug修复之路 https://mp.weixin.qq.com/s/IavpeP93haOKVBt7eO8luQ

Macos系统编译percona及部分函数在Macos系统上运算差异 https://mp.weixin.qq.com/s/jAbwicbRc1nQ0f2cIa_2nQ

技术分享 | 利用systemd管理MySQL单机多实例 https://mp.weixin.qq.com/s/iJjXwd0z1a6isUJtuAAHtQ

产品 | GreatSQL，打造更好的MGR生态 https://mp.weixin.qq.com/s/ByAjPOwHIwEPFtwC5jA28Q

产品 | GreatSQL MGR优化参考 https://mp.weixin.qq.com/s/5mL_ERRIjpdOuONian8_Ow

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。

Gitee: https://gitee.com/GreatSQL/GreatSQL

GitHub: https://github.com/GreatSQL/GreatSQL

微信&QQ群：

可扫码添加GreatSQL社区助手微信好友，发送验证信息“加群”加入GreatSQL/MGR交流微信群，亦可直接扫码加入GreatSQL/MGR交流QQ群。

file

本文由博客一文多发平台 OpenWrite 发布！

GreatSQL社区
关注
第71讲：MySQL锁机制详解：表级锁、元数据锁和意向锁的全面解析与实践指南
江晓龙的博客
11-28 1万+
如下图所示，在多个并发客户端的情况下，第一个客户端开启了表共享读锁，那么DQL查询操作在多个客户端上都可以查询数据，不受影响，但是当有DDL/DML类的修改表数据的操作时，第一个客户端直接会提示表中存在锁，第二个客户端的写操作会处于阻塞状态，当第一个客户端释放锁之后，第二个客户端的写操作才能正常执行。共享读锁不会影响其他客户端的读，但是会阻塞其他客户端的写。
mysql意向锁死锁排查
wuyu6394232的专栏
08-10 562
最近遇到一个奇怪的mysql死锁： 事务1: begin; select * from bridge_tx_status where id = 2 for update; 1⃣️ update bridge_tx_status set biz_nonce="1234" where transaction_id="23"; 2⃣️ commit; 事务2: begin; u...
《叶问》31期，MySQL中如何查询某个表上的IS（意向共享）锁
老叶茶馆
05-07 360
关 于 叶 问《叶问》是知数堂新设计的互动栏目，不定期给大家提供技术知识小贴士，形式不限，或提问、或讨论均可，并在当天发布答案，让大家轻轻松松利用碎片时间就可以学到最实用的知识点。问题问题...
MySQL意向锁SQL示例：查看意向锁及其状态
学亮编程手记
12-03 608
当一个连接请求对表进行读锁或写锁时，MySQL会自动获取适当级别的意向锁来指示其意图。需要强调的是，意向锁是MySQL内部的机制，开发人员无需直接操作它们。开发人员应该关注事务隔离级别和锁定语句的正确使用，以确保数据的一致性和并发性能。这些查询可以提供有关意向锁的信息，但请注意，意向锁的具体细节和行为由MySQL内部管理，因此查询结果可能会受到MySQL版本和配置的影响。在MySQL中，意向锁是由MySQL内部自动管理的，开发人员无需显式执行意向锁的操作。因此，没有直接的SQL语句来演示意向锁的使用。
MySQL表级锁之意向锁
weixin_44948424的博客
11-18 2514
MySQL表级锁之意向锁
mysql 共享锁排它锁
高飞
05-02 368
mysql锁机制分为表级锁和行级锁，本文就和大家分享一下我对mysql中行级锁中的共享锁与排他锁进行分享交流。 共享锁又称为读锁，简称S锁，顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。 排他锁又称为写锁，简称X锁，顾名思义，排他锁就是不能与其他所并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获...
454-MySQL(意向共享锁,意向排他锁和死锁)
LINZEYU666的博客
08-21 249
InnoDB表级锁 我们在之前知道，InnoDB是行锁，但是不是每次都获取行锁，如果没有索引项的话，还是一个表锁。而且有的时候，我们希望直接去使用表锁。 在绝大部分情况下都应该使用行锁，因为事务和行锁往往是选择InnoDB的理由，但个别情况下也使用表级锁； 1）事务需要更新大部分或全部数据，表又比较大，如果使用默认的行锁，不仅这个事务执行效率低，而且可能造成其他事务长时间等待和锁冲突； 2）事务涉及多个表，比较复杂，很可能引起死锁，造成大量事务回滚。 如： LOCK TABLE user READ；读锁锁表
【MySQL进阶之路 | 高级篇】表级锁之S锁，X锁，意向锁
2301_80912559的博客
07-26 1732
MySQL中采用了自增锁的方式来实现，AUTO-INC锁是当向使用含有AUTO_INCREMENT列的表中插入数据时需要获取的一种特殊的表级锁，在执行插入语句时就在表级别加一个AUTO-INC锁，然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值，在该语句执行结束后，再把锁释放掉。也正因为如此其并发性能不高。该锁会锁定整张表，它是MySQL中最基本的锁策略，并不依赖于存储引擎（不管你是MysQL的什么存储引擎，对于表锁的策略都是一样的)，并且表锁是开销最小的策略（(因为粒度比较大)。
MySQL InnoDB锁机制深度剖析（乐观锁、悲观锁、共享锁、排它锁、行锁、表 锁、死锁）
qq_51263863的博客
08-11 1490
乐观锁适用于读多写少的场景，可以提高并发性能，但需要在应用层处理冲突。悲观锁适用于写多读少的场景，可以确保数据一致性，但可能影响并发性能。共享锁和排他锁是InnoDB实现并发控制的基础，理解它们有助于我们选择合适的查询方式。行锁提供了细粒度的并发控制，但要注意索引对行锁的影响。表锁在某些情况下是必要的，但应该谨慎使用，因为它会显著降低并发性。死锁是并发系统中的常见问题，需要通过合理的锁策略和事务设计来避免。间隙锁解决了幻读问题，但也可能导致某些操作被阻塞，需要在隔离性和并发性之间权衡。意向锁。
mysql中的锁理解
simpleGq的专栏
10-31 1499
mysql中的锁理解
【四十六、MySql】进阶篇--锁--表级锁（意向锁）
qq_49121643的博客
05-24 589
意向锁
【Mysql】 锁
Decade_Faiz的博客
02-22 877
*锁是计算机协调多个进程或线程并发访问某一资源的机制。**在数据库中，除传统的计算资源（CPU、 RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。全局锁：锁定所有的数据库中的所有表。表级锁：每次操作锁住整张表。行级锁：每次操作锁住对应的行数据。
多维度梳理 MySQL 锁
STRANGE-P 的博客
11-15 395
在数据库中，除传统的计算资源（如 CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。为保证数据的一致性，需要对并发操作进行控制，因此产生了锁。同时。锁冲突也是影响数据库并发访问性能的一个重要因素。所以锁对数据库非常重要，且复杂。并发事务访问同一记录的情况，大致分为：读-读、写-写、读-写。
mysql锁机制（共享锁(S)、排他锁(X)、意向共享锁(IS)、意向排他锁(IX)的关系，死锁，乐观锁，悲观锁...）
晴空万里
04-02 3212
1. 锁的基础与行锁的特点 1.1 概念 在开发多用户、数据库驱动的应用时，相当大的一个难点就是解决并发性的问题，目前比较常用的解决方案就是锁机制。 锁机制也是数据库系统区别于文件系统的一个关键特性。 InnoDB 存储引擎和 MyISAM 存储引擎使用的是完全不同的策略。 1.2 锁的类型 相比其他数据库而言，MySQL 的锁机制比较简单，而且不同的存储引擎支持不同的锁机制。 MyISAM 和 Memory 存储引擎使用的是表级锁,BDB 引擎使用的是页级锁，也支持表级锁。由于 BDB 引擎基
MySQL数据库锁机制（全面讲解）
程序员阿龙的博客
08-23 5418
在这个数据驱动的时代，数据库的重要性不言而喻。尤其是当我们面对成千上万的用户同时操作数据库时，如何保证数据不会乱套，这就得聊聊MySQL的锁机制了。锁，听起来好像有点神秘，但其实它就像是数据库的一道安全门，接下来，我会和大家一起探讨一下MySQL数据库锁的点点滴滴，包括它是怎么工作的，有哪些类型，以及在实际应用中怎么用。你能对数据库锁有个更清晰的了解，以后遇到并发问题也能更从容应对。那么，让我们开始吧！
SQL Server 6种锁
wyklis的专栏
09-12 515
<br />SQL Server 6种锁：共享，更新，排他，意向，模式，批量更新。<br />共享锁允许多个连接和事务同时读取所分配的共同资源，设置共享锁后其他连接或事务就不能修改数据。<br />更新锁防止出现死锁，只有一个进程可得到更新锁，通常用来替代共享锁。<br />排他锁防止任何并发事务或连接的进程对处于修改中的数据进行读、更新、删除操作。<br />意向锁防止在一个已被其他进程锁住资源上再加排他锁。<br /> 
MySQL 数据库中的表锁与解锁
MbfrDeveloper的博客
10-03 470
当多个用户同时访问同一个表时，为了保证数据的一致性和完整性，MySQL会自动对表进行锁定，以防止并发操作引发的问题。MySQL中的表锁是一种用于控制并发访问的机制，通过共享锁和排他锁来确保数据的一致性和完整性。在使用表锁时，需要注意合理地选择锁的类型，并在操作完成后及时解锁表，以避免资源泄露和死锁等问题。通过执行上述语句，当前会话将获取指定表的共享锁，其他会话可以继续获取共享锁，但无法获取排他锁。通过执行上述语句，当前会话将获取指定表的排他锁，其他会话无法获取共享锁和排他锁。在上述示例中，首先使用。
MySQL 锁——全局锁、表级锁（表锁 元数据锁MDL 意向锁）、行锁
hayhead的博客
10-08 576
MySQL 锁、全局锁、表级锁（表锁 元数据锁MDL 意向锁）
mysql 查询 is not in_MySQL遇见SELECT list is not in GROUP BY clause and contains nonaggre的问题...
weixin_29322855的博客
01-18 632
报错现象执行SQL报错如下：SELECT student.s_no,student.s_name,SUM(result.mark) FROM student,result WHERE student.s_no=result.s_no GROUP BY student.s_no> 1055 - Expression #2 of SELECT list is not in GROUP BY cl...
MySQL中的意向锁如何开启
最新发布
07-27
<think>我们正在讨论MySQL的意向锁。根据引用内容，意向锁是表级锁，用于指示事务稍后将对表中的行施加哪种类型的锁（共享或排他）。这样，当另一个事务想要锁定整个表时，它可以快速检查意向锁而无需扫描每一行。 用户的问题：如何启用意向锁？ 在MySQL中，意向锁是自动管理的，不需要显式启用。InnoDB存储引擎在需要时自动使用意向锁。具体来说： - 当您对表中的行加共享锁（S）时，InnoDB会首先在表上加一个意向共享锁（IS）。 - 当您对表中的行加排他锁（X）时，InnoDB会首先在表上加一个意向排他锁（IX）。 因此，作为用户，您不需要（也无法）显式启用意向锁。意向锁是InnoDB内部用于提高表锁检查效率的机制。 但是，您需要确保您使用的是InnoDB存储引擎，因为意向锁是InnoDB的特性。MyISAM存储引擎不支持行级锁，也不支持意向锁。 另外，当您使用诸如`LOCK TABLES ... WRITE`这样的语句时，MySQL会获取表锁。在InnoDB中，通常我们使用行级锁，但有时也会使用表锁。在获取表锁时，MySQL会检查是否有意向锁存在，从而避免与行级锁冲突。 总结：意向锁是InnoDB自动管理的，不需要用户显式启用。用户只需要确保表使用InnoDB引擎。 根据引用[1]和[2]，意向锁的作用是避免在加表锁时检查每一行是否有锁，而是通过表级的意向锁来快速判断。 因此，回答用户：MySQL中的意向锁是InnoDB存储引擎自动管理的，不需要用户显式启用。只要您的表使用InnoDB引擎，意向锁机制就会自动工作。 但是，用户可能想知道如何确保意向锁被使用？答案是：只要使用InnoDB引擎，并且事务中使用了行级锁（如SELECT ... FOR UPDATE或SELECT ... LOCK IN SHARE MODE），InnoDB就会自动在表上设置意向锁。 另外，关于意向锁的查看，用户可以通过以下命令查看当前锁的状态： `SHOW ENGINE INNODB STATUS;` 或者在MySQL 5.7及以上版本，可以通过`performance_schema`中的表来查看，如`performance_schema.data_locks`。 下面，我们按照要求生成相关问题。 §§相关问题§§ 1. 意向锁在什么情况下会被使用？ 2. 如何查看MySQL中的意向锁？ 3. 意向锁和行锁之间的关系是什么？ 4. 使用MyISAM存储引擎时，意向锁是否可用？</think>### MySQL 意向锁的启用机制 在 MySQL 中，**意向锁是 InnoDB 存储引擎自动管理的机制，不需要手动启用**。只要使用 InnoDB 引擎（MySQL 5.5 后的默认引擎），意向锁就会在以下场景自动生效： 1. **自动触发条件**： - 当事务对某行加**共享锁**（S 锁）时，InnoDB 会先在表级加**意向共享锁（IS）**。 - 当事务对某行加**排他锁**（X 锁）时，InnoDB 会先在表级加**意向排他锁（IX）**。 - 例如执行 `SELECT ... LOCK IN SHARE MODE` 或 `SELECT ... FOR UPDATE` 时[^1][^2]。 2. **核心作用**： - **优化表锁冲突检测**：当另一事务尝试加表锁（如 `LOCK TABLES ... WRITE`）时，只需检查表级意向锁（IS/IX），无需逐行扫描锁状态，大幅提升性能[^1][^2]。 - **避免死锁**：通过意向锁提前声明锁定意图，减少锁冲突概率。 3. **验证方法**： ```sql -- 步骤1：创建 InnoDB 表 CREATE TABLE t ( id INT PRIMARY KEY, data VARCHAR(20) ) ENGINE=InnoDB; -- 步骤2：事务A 加行锁（自动触发意向锁） BEGIN; SELECT * FROM t WHERE id=1 FOR UPDATE; -- 表级自动加 IX 锁 -- 步骤3：事务B 尝试加表锁（被阻塞） LOCK TABLES t WRITE; -- 因检测到 IX 锁而等待 ``` 通过 `SHOW ENGINE INNODB STATUS` 可查看锁状态，输出中包含 `TABLE LOCK ... IX` 即表示意向锁生效[^1][^4]。 --- ### 关键注意事项 1. **存储引擎依赖**：仅 **InnoDB** 支持意向锁，MyISAM 等引擎无此机制。 2. **锁升级规则**：意向锁不会阻塞除全表锁外的其他操作（如不同行的 IX 锁可共存）[^3]。 3. **与间隙锁协作**：意向锁与间隙锁（Gap Lock）协同工作，确保范围查询的隔离性[^3][^4]。 > 📌 **总结**：意向锁是 InnoDB 内置的自动化机制，只需确保使用 InnoDB 引擎并正常执行加锁操作（如 `FOR UPDATE`），系统会自动管理意向锁的申请与释放[^1][^2]。 ---
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值