权限系统水平扩展中的行锁问题及优化咨询

需求说明 #

我当前正在构建一套权限系统，核心需求之一是支持水平扩展。

现有实现方案 #

我们设计了一张“编译后资源权限”表，结构示例如下：

该表结构表示：

• 用户1拥有资源1和2的访问权限
• 用户2仅拥有资源1的访问权限

reason列是按位存储的数值，每一位对应权限授予原因：

• 二进制位1表示用户为管理员
• 二进制位2表示用户创建了该资源

例如：用户1因管理员身份拥有资源1的权限；拥有资源2的权限则是因为既是管理员又是资源创建者。若其不再是管理员，仍可访问资源2，但无法访问资源1。

我们通过“patcher”类维护该表：遍历传入的用户与资源，查询所需数据库表，计算需新增或删除的行数据。

扩展尝试及问题 #

为实现水平扩展，我们将逻辑拆分为任务块，交由异步队列中的多个“worker”处理，但该方式扩展能力有限，达到一定程度后性能不再提升，甚至因行锁导致性能下降。

我们的疑问：

• 是否存在特定行锁类型可支持无限水平扩展？
• 我们的实现思路是否有误？我们有大量权限“原因”及复杂逻辑，需快速重新编译权限表

并发执行的SQL查询示例 #

新增权限原因时的查询 #

INSERT INTO `compiled_permissions` (`user_id`, `resource_id`, `reason`) VALUES ((1,1,1), (1,2,3), (2,1,2)) ON DUPLICATE KEY UPDATE `reason` = `reason` | VALUES(`reason`);

移除权限原因时的查询 #

UPDATE `compiled_permissions` SET `reason` = `reason` & ~ (CASE WHEN (user_id = 1 AND resource_id = 1) THEN 2 ... /* CASE FOR EVERY "REASON REMOVAL" */ ELSE `reason` END) WHERE (`user_id`, `resource_id`) IN ((1,1),(1,2) .. ETC )

针对问题的解答 #

1. 关于行锁与无限水平扩展的可能性 #

首先得明确：不存在能支持“无限”水平扩展的行锁类型——数据库锁的核心作用是保证数据一致性，当并发操作集中在相同的(user_id, resource_id)行时，行锁必然会成为瓶颈。你的场景里，如果worker处理任务时经常命中同一行，就会出现锁等待，甚至死锁，最终导致性能不升反降。

2. 现有实现思路的潜在问题 #

你的“编译后权限表”设计虽然直观，但有几个明显的扩展性短板：

• 按位存储的reason列扩展性差：当权限原因增多时，reason的数值会越来越大，位运算逻辑也会变得极度复杂，后续新增权限类型不仅要改代码，维护成本会急剧上升。
• patcher类的遍历计算效率低：遍历用户与资源逐行计算的方式，在数据量增大后会产生大量数据库查询和更新操作，很容易触发锁竞争。
• 并发更新的冲突风险高：多个worker同时更新同一行时，ON DUPLICATE KEY UPDATE和复杂的CASE语句会拉长锁持有时间，甚至因为锁顺序问题引发死锁。

3. 优化方向建议 #

（1）重构权限表结构，拆分reason列

把按位存储的reason拆成单独的关联表，比如permission_grants：

CREATE TABLE permission_grants (
    user_id INT,
    resource_id INT,
    grant_type ENUM('admin', 'creator', ...), -- 明确的权限授予类型
    PRIMARY KEY (user_id, resource_id, grant_type)
);

这样做的好处：

• 彻底避免位运算的复杂性，新增权限类型只需添加枚举值即可
• 单个权限类型的增删操作更简单，不会影响其他权限类型的状态
• 并发更新时锁粒度更细（针对(user_id, resource_id, grant_type)），大幅减少锁竞争

（2）批量处理与任务分片优化

• 分片策略优化：不要随机拆分任务块，而是按照user_id或resource_id的哈希值分片，确保同一用户/资源的权限操作由同一个worker处理，从根源上避免同一行被多个worker同时操作。
• 批量操作优化：尽量合并多个增删操作成批量SQL，减少数据库交互次数。比如批量插入权限授予记录时，用INSERT ... ON DUPLICATE KEY IGNORE代替逐行插入。

（3）采用最终一致性模型

如果业务允许短暂的权限不一致，可以考虑：

• 用消息队列缓冲权限变更事件，异步批量更新权限表
• 引入缓存层（如Redis）存储用户的权限集合，实时查询走缓存，后台异步同步到数据库
• 定期全量同步权限数据，修正缓存和数据库的不一致

（4）数据库层面的优化

• 确保compiled_permissions表的主键是(user_id, resource_id)，或者创建联合索引，减少查询和更新时的锁范围
• 对于MySQL，可以尝试使用READ COMMITTED隔离级别，缩短锁的持有时间
• 考虑分库分表：当数据量极大时，按照user_id哈希分库分表，将不同用户的权限数据分散到不同节点，从根本上减少单节点的锁竞争

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