剔除无用数据的表结构
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 这个字段造成的。 但是这里边是怎么执行的,还不理解。
1
awanganddong OP 根据我个人理解
在 rc 多个索引的情况下。 会对满足各个普通索引的记录加锁 然后接着对各个普通索引对应的主键索引加锁 |
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 ... |
3
awanganddong OP |
4
qyvlik 2021-06-22 18:02:53 +08:00
MySQL 单条的 查询、更新、删除语句中,只能使用一个索引(单字段索引或者多字段索引都称为一个索引)
@awanganddong |
5
awanganddong OP @qyvlik 既然多字段索引被称为一个索引。那么我困惑的点就是,为什么 mysql 的锁由第一个字段 feature_id 触发。
毕竟 featrue_id 与 member_id 与 state 这几个多字段是定位到唯一值的。 |
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 的开销计算。 |
7
awanganddong OP @qyvlik 谢谢了
|