MySQL如何按指定user_id限制插入行数且不锁定全表

你原有逻辑存在两个核心问题：

• 普通SELECT是快照读，不会加任何锁，并发场景下多个事务会同时读到当前关注数小于5，各自执行插入，最终总条数突破阈值
• 阈值判断逻辑有误：判断count > 5才拦截，等于允许插入第6条数据，正确的拦截条件应该是count >= 5

不需要锁全表，利用InnoDB的索引行锁+间隙锁机制，仅锁定当前操作的user_id对应的索引范围，其他用户的关注操作完全不会被阻塞。
核心修改是把普通的count查询改成加FOR UPDATE的当前读，修正后的逻辑代码如下：

function follow(self, target) {
    begin transaction;
    // 加FOR UPDATE走(user_id, friend_id)唯一索引，仅锁定当前self用户对应的索引区间
    const cnt = query(`SELECT COUNT(*) AS cnt FROM FriendRelation WHERE user_id = ? FOR UPDATE`, [self]);
    if (cnt >= 5) {
        rollback;
        return error("关注数量已达上限");
    }
    try {
        query(`INSERT INTO FriendRelation (user_id, friend_id) VALUES (?, ?)`, [self, target]);
        commit;
    } catch (e) {
        // 捕获唯一键冲突，处理重复关注场景
        rollback;
        return error("不能重复关注");
    }
}

锁范围说明 #

因为已经建了(user_id, friend_id)的联合唯一索引，相同user_id的记录在索引树上是连续存储的：

• 执行FOR UPDATE查询时，InnoDB会给该user_id下所有已存在的记录加行排他锁，同时给对应区间加间隙锁
• 事务提交前，其他针对同一个user_id的插入、FOR UPDATE查询都会被阻塞排队，从根源避免同时读到低count值的问题
• 不同user_id的操作不会命中这些锁，并发性能和普通插入几乎一致
• 该方案在InnoDB默认的REPEATABLE READ隔离级别下直接生效，不需要额外配置

如果不想在应用层维护加锁逻辑，可以把校验逻辑下沉到数据库，两种成熟实现：

实现A：计数表+原子更新（性能最好） #

新建一张用户关注计数表，利用数据库行锁的原子性做校验：

CREATE TABLE `UserFollowStat` (
  `user_id` bigint(20) unsigned NOT NULL,
  `follow_cnt` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB;

关注逻辑调整为：

function follow(self, target) {
    begin transaction;
    // 原子更新计数，只有计数小于5时才会执行成功
    const result = query(`UPDATE UserFollowStat SET follow_cnt = follow_cnt + 1 WHERE user_id = ? AND follow_cnt < 5`, [self]);
    if (result.affectedRows === 0) {
        rollback;
        return error("关注数量已达上限");
    }
    try {
        query(`INSERT INTO FriendRelation (user_id, friend_id) VALUES (?, ?)`, [self, target]);
        commit;
    } catch (e) {
        // 插入失败（重复关注/异常）时回滚计数
        query(`UPDATE UserFollowStat SET follow_cnt = follow_cnt -1 WHERE user_id = ?`, [self]);
        rollback;
        return error("关注失败");
    }
}

这个方案的锁是加在UserFollowStat表对应user_id的单行上，锁粒度更细，性能更稳定。新用户第一次关注前，只需要往UserFollowStat插入一条初始cnt=0的记录即可。

实现B：插入前触发器 #

直接在FriendRelation表上建BEFORE INSERT触发器，插入前自动校验数量，超量直接抛出错误：

DELIMITER //
CREATE TRIGGER `trg_follow_limit_check` BEFORE INSERT ON `FriendRelation`
FOR EACH ROW
BEGIN
    DECLARE current_cnt INT DEFAULT 0;
    SELECT COUNT(*) INTO current_cnt FROM FriendRelation WHERE user_id = NEW.user_id;
    IF current_cnt >= 5 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Follow count exceeds limit';
    END IF;
END //
DELIMITER ;

这个方案对应用层代码零侵入，但触发器本身会带来额外的性能开销，且高并发场景下统计count的开销比计数表方案高，适合并发量不高的场景。

• 绝对不要用全表锁实现，会阻塞所有用户的关注操作，并发性能极差
• 必须保留(user_id, friend_id)的唯一索引，即使做了数量校验，也能防止重复关注、脏数据问题
• 不要用普通SELECT加缓存计数的方式做校验，缓存和数据库的一致性问题会导致阈值校验失效

内容的提问来源于stack exchange，提问作者cr001