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
awanganddong
V2EX  ›  MySQL

mysql 在 rc 模式下的锁机制

  •  2
     
  •   awanganddong · 2021-06-20 10:23:13 +08:00 · 1768 次点击
    这是一个创建于 1241 天前的主题,其中的信息可能已经有所发展或是发生改变。
    剔除无用数据的表结构
    CREATE TABLE `pyjy_vd_member_feature_content` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `member_id` int(11) NOT NULL DEFAULT '0',
      `feature_id` int(11) NOT NULL DEFAULT '0',
      `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 尚未提交审核 2 正在审核 3 审核通过 4 审核拒绝',
      `content` varchar(300) CHARACTER SET utf8 NOT NULL DEFAULT '',
      `main_id` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `member_id_index` (`member_id`),
      KEY `feature_id_index` (`feature_id`),
      KEY `member_feature_content_state_idx` (`state`),
      KEY `member_feature_content_main_id_idx` (`main_id`),
      KEY `index_0` (`type`,`show_state`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=228734 DEFAULT CHARSET=utf8mb4 COMMENT='用户特质内容';
    
    发生死锁的两条 sql  
    UPDATE `pyjy_vd_member_feature_content` SET `view_count` = `view_count` + 85 WHERE `feature_id` = '95' AND `member_id` = 549872 AND `main_id` = '140359' AND `state` = 3
    
    UPDATE `pyjy_vd_member_feature_content` SET `view_count` = `view_count` + 88 WHERE `feature_id` = '95' AND `member_id` = 363520 AND `main_id` = '118167' AND `state` = 3
    

    在 rc 模式下,不可重复读,所以加的锁是记录锁

    这个是事务 1 的日志
    
    RECORD LOCKS space id 377 page no 1530 n bits 1272 index feature_id_index of table `vdsns`.`pyjy_vd_member_feature_content` trx id 204978888 lock_mode X locks rec but not gap waiting
    Record lock, heap no 264 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;   //普通索引=》 feature_id
     1: len 4; hex 8002e0ff; asc     ;;   //主键 ID 
     
    这个是事务二的日志
    
    *** (2) HOLDS THE LOCK(S):
    RECORD LOCKS space id 377 page no 1530 n bits 1272 index feature_id_index of table `vdsns`.`pyjy_vd_member_feature_content` trx id 204978887 lock_mode X locks rec but not gap
    Record lock, heap no 264 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e0ff; asc     ;;
    
    Record lock, heap no 265 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e103; asc     ;;
    
    Record lock, heap no 267 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e108; asc     ;;
    
    Record lock, heap no 268 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e109; asc     ;;
    
    Record lock, heap no 286 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e165; asc    e;;
    
    Record lock, heap no 287 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002e166; asc    f;;
    
    Record lock, heap no 515 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 8002ebf3; asc     ;;
    
    Record lock, heap no 1084 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
     0: len 4; hex 8000005f; asc    _;;
     1: len 4; hex 80031e5e; asc    ^;;
    

    现在知道死锁是 feature_id 这个字段造成的。 但是这里边是怎么执行的,还不理解。

    7 条回复    2021-06-27 17:12:09 +08:00
    awanganddong
        1
    awanganddong  
    OP
       2021-06-21 19:55:59 +08:00
    根据我个人理解

    在 rc 多个索引的情况下。

    会对满足各个普通索引的记录加锁
    然后接着对各个普通索引对应的主键索引加锁
    qyvlik
        2
    qyvlik  
       2021-06-22 10:31:01 +08:00
    1. member_id_index,feature_id_index,member_feature_content_state_idx,member_feature_content_main_id_idx 这些索引无法再一次 Query 中使用。

    2. UPDATE `pyjy_vd_member_feature_content` SET `view_count` = `view_count` + 85 WHERE `feature_id` = ? AND `member_id` =? AND `main_id` = ? AND `state` = ? 这个语句只会挑选一个索引。

    3. 现在你知道死锁是由于 feature_id 导致,所以 UPDATE 的 WHERE 需要有 ID
    4. SQL 语句修改方式如下:

    SELECT id FROM `pyjy_vd_member_feature_content` WHERE `feature_id` = ? AND `member_id` =? AND `main_id` = ? AND `state` = ?
    UPDATE SET `view_count` = `view_count` + 85 WHERE id = ? AND ...
    awanganddong
        3
    awanganddong  
    OP
       2021-06-22 17:51:40 +08:00 via Android
    @qyvlik
    第一点不能在一次 query 中使用,这个不太理解。
    第二点,为什么更新的时候,只会挑选一个索引。而不是组合索引。
    qyvlik
        4
    qyvlik  
       2021-06-22 18:02:53 +08:00
    MySQL 单条的 查询、更新、删除语句中,只能使用一个索引(单字段索引或者多字段索引都称为一个索引)
    @awanganddong
    awanganddong
        5
    awanganddong  
    OP
       2021-06-22 20:59:05 +08:00
    @qyvlik 既然多字段索引被称为一个索引。那么我困惑的点就是,为什么 mysql 的锁由第一个字段 feature_id 触发。
    毕竟 featrue_id 与 member_id 与 state 这几个多字段是定位到唯一值的。
    qyvlik
        6
    qyvlik  
       2021-06-23 09:17:24 +08:00
    CREATE TABLE `pyjy_vd_member_feature_content` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `member_id` int(11) NOT NULL DEFAULT '0',
    `feature_id` int(11) NOT NULL DEFAULT '0',
    `state` tinyint(2) NOT NULL DEFAULT '1' COMMENT '1 尚未提交审核 2 正在审核 3 审核通过 4 审核拒绝',
    `content` varchar(300) CHARACTER SET utf8 NOT NULL DEFAULT '',
    `main_id` int(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`),
    UNIQUE KEY `featrueid_memberid_state` (`featrue_id `,`member_id `,`state`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户特质内容';

    SQL 建表语句 `UNIQUE KEY` 才是声明唯一键的语法,推荐使用数据库控制唯一性,而不是使用程序控制。

    多字段索引(联合索引)是指 `KEY index_name(field_1, field_2, field_3)`,你的原表是多个单列索引,参考 [多个单列索引和联合索引的区别详解]( https://blog.csdn.net/Abysscarry/article/details/80792876)。

    由于你的原表是多个单列索引,所以在更新、删除、查询的时候,只能使用其中一个单列索引。至于为什么使用 feature_id 这个字段,取决于 MySQL 的开销计算。
    awanganddong
        7
    awanganddong  
    OP
       2021-06-27 17:12:09 +08:00
    @qyvlik 谢谢了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3811 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 00:54 · PVG 08:54 · LAX 16:54 · JFK 19:54
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.