V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
rqxiao
V2EX  ›  MySQL

mysql 间隙锁

  •  
  •   rqxiao · 2023-02-10 10:31:50 +08:00 · 1875 次点击
    这是一个创建于 412 天前的主题,其中的信息可能已经有所发展或是发生改变。

    mysql 版本 5.7.2

    隔离级别 rr

    mysql 间隙锁 !不是锁定行,也不是锁定某个列,是锁定对应的索引。测试表 tx_test ,age 字段加了索引了。


    -- Table structure for tx_test


    DROP TABLE IF EXISTS `tx_test`;
    CREATE TABLE `tx_test` (
      `id` int(11) NOT NULL,
      `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
      `age` int(10) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `age` (`age`),
      KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
    

    -- Records of tx_test


    BEGIN;
    INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (1, '123', 3);
    INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (2, '44', 4);
    INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (3, '55', 5);
    INSERT INTO `tx_test` (`id`, `name`, `age`) VALUES (4, '41', 50);
    COMMIT;
    

    #session1

    begin;

    SELECT * from tx_test;

    update tx_test set name='aaaaa' where age >1 and age <20;

    SELECT * from tx_test;

    COMMIT;

    #session2

    begin;

    SELECT * from tx_test;

    INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77);

    SELECT * from tx_test;

    COMMIT;

    为什么 session2 的 INSERT INTO tx_test (id, name, age) VALUES (88, '41', 77); 会阻塞呢 。session1 的查询不是应该 锁定了 -无穷到 50 吗

    10 条回复    2023-02-10 17:17:01 +08:00
    movq
        1
    movq  
       2023-02-10 10:48:33 +08:00
    第一个事务后面有 SELECT * from tx_test;,所以要全锁住,不然不是 repeatable read
    rqxiao
        2
    rqxiao  
    OP
       2023-02-10 11:00:47 +08:00
    @movq session1 改成 update tx_test set name='aaaaa' where age >1 and age <5;。session2 就可以插入
    Inf1nity
        3
    Inf1nity  
       2023-02-10 11:08:35 +08:00
    我这边在 MySQL 8.0 下面测试的 session1 的查询执行(未 commit )后,给 age = 3, age = 4, age = 5 的辅助索引加了 next key lock ,同时给对应的主键 id = 1, id = 2, id = 3 加了 record lock ,之后执行 session2 并没有被阻塞。
    楼主可以试试用下面这个查询查看加锁情况:

    SELECT
    ENGINE,
    EVENT_ID,
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME,
    LOCK_TYPE,
    LOCK_MODE,
    LOCK_STATUS,
    LOCK_DATA
    FROM performance_schema.data_locks;
    wps353
        4
    wps353  
       2023-02-10 11:54:03 +08:00   ❤️ 2
    @rqxiao
    楼主,试试 update tx_test force index(age) set name='aaaaa' where age >1 and age <20;
    看看 session2 能不能插入成功?
    rqxiao
        5
    rqxiao  
    OP
       2023-02-10 13:48:10 +08:00
    @wps353 可以插入成功,这个 update 操作还会区分数据分部情况来判断锁不锁索引吗。。
    Chaox
        6
    Chaox  
       2023-02-10 14:13:51 +08:00
    我猜测是你这个数据量太少了,mysql 优化器就没走索引,直接锁表了。所以 4 楼加上 force index(age)走了索引就可以了
    网上的资料:
    即使完全符合索引生效的场景,考虑到实际数据量等原因,最终是否使用索引还要看 MySQL 优化器的判断。当然你也可以在 sql 语句中写明强制走某个索引。
    lookStupiToForce
        7
    lookStupiToForce  
       2023-02-10 14:19:26 +08:00   ❤️ 1
    顺道提一嘴
    mysql 从 5.6 开始就支持 explain update 了
    https://dev.mysql.com/doc/refman/5.6/en/explain.html
    wangxin3
        8
    wangxin3  
       2023-02-10 16:40:30 +08:00
    8.0.29 亲测无这个问题
    ivanMeng
        9
    ivanMeng  
       2023-02-10 17:06:36 +08:00
    经过我不断实验和分析 。。。。
    explain 可以看到 age<4 和 age <20 走了不通的命中 key
    二级索引太少了 还没有 All 快 索引走了 Primarykey 所以锁全表了。。。。
    让我也想了多半天 都怀疑间隙锁了。。。。
    而且 8 的版本 没毛病。。。
    koloonps
        10
    koloonps  
       2023-02-10 17:17:01 +08:00
    @ivanMeng @wangxin3 为什么我的 8.0.29 有这个问题........
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1081 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 22:45 · PVG 06:45 · LAX 15:45 · JFK 18:45
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.