mysql 在 rc 模式下的锁机制

剔除无用数据的表结构
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

2021-06-21 19:55:59 +08:00

根据我个人理解

在 rc 多个索引的情况下。

会对满足各个普通索引的记录加锁
然后接着对各个普通索引对应的主键索引加锁

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

2021-06-22 17:51:40 +08:00 via Android

@qyvlik
第一点不能在一次 query 中使用，这个不太理解。
第二点，为什么更新的时候，只会挑选一个索引。而不是组合索引。

qyvlik

2021-06-22 18:02:53 +08:00

MySQL 单条的查询、更新、删除语句中，只能使用一个索引（单字段索引或者多字段索引都称为一个索引）
@awanganddong

awanganddong

2021-06-22 20:59:05 +08:00

@qyvlik 既然多字段索引被称为一个索引。那么我困惑的点就是，为什么 mysql 的锁由第一个字段 feature_id 触发。
毕竟 featrue_id 与 member_id 与 state 这几个多字段是定位到唯一值的。

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

2021-06-27 17:12:09 +08:00

@qyvlik 谢谢了