十六把锁应该足够任何人使用了

# 十六把锁足以应付所有人 来源：https://thebuild.com/blog/sixteen-locks-ought-to-be-enough-for-anybody/ 关于 PostgreSQL 有一个事实，即使运营多年的人也会感到惊讶：对表的每次查询都会对该表上的每个索引加锁，无论查询是否使用了这些索引。 这通常无害。这些锁是`AccessShareLock`，是最弱的锁类型；它们几乎与任何锁都不冲突，且获取开销很低。但“低”不等于“零”，当索引数量和查询频率达到某种组合时，这种行为会转化为 CPU 吞噬级锁竞争问题，即便是像单行主键查找这样看似无害的查询也会受影响。 ## 规划器会锁定它查看的所有内容 当 PostgreSQL 规划查询时，它会对查询*可能*用到的每个关系加`AccessShareLock`。这意味着包括表本身及其所有索引，因为规划器必须打开每个索引来判断其是否有用。即使最终只使用其中一个索引也无妨——考虑过程就需要加锁。 因此，一个具有主键和 20 个二级索引的表，每次查询需要 22 个关系锁：表本身、主键索引，以及规划器检查后丢弃的另外 20 个索引。每次执行皆是如此。 ## 快速路径，以及脱离快速路径 获取锁通常意味着在共享锁表中创建条目，该表位于共享内存中并由轻量锁保护（它被分为 16 个分区，每个分区有自己的 LWLock）。在多核机器的高查询速率下，这 16 个 LWLock 本身就会成为竞争点。 PostgreSQL 在 9.2 版本中为此引入了优化：**快速路径锁**。每个后端进程有一个小型私有数组，可以在不接触共享锁表的情况下记录弱关系锁（`AccessShareLock`、`RowShareLock`、`RowExclusiveLock`）。直到 PostgreSQL 17，这个数组都恰好有 16 个槽位。 十六个。数数你的索引数量。 如果查询需要超过 16 个关系锁，溢出部分会通过共享锁表处理，包括 LWLock 等全部机制。一个后端进程这样做没问题。但数百个后端每秒执行数千次时，你就会在`pg_stat_activity`中看到大量的`LWLock:LockManager`等待（PostgreSQL 13 之前拼写为`lock_manager`），CPU 锁死，吞吐量下降，而查询本身却极其简单。如果你使用 RDS 或 Aurora，这正是 Performance Insights 喜欢用惊悚的棕色展示给你的`LWLock:LockManager`等待事件。 这种故障模式的恶劣特性在于它恰好集中在错误的表上。索引过多的表通常是核心表，每个查询都会访问，因此*每个*查询都要付出代价，竞争随着总查询速率而增长。分区表会更快达到这个状态，因为规划器可能需要锁定每个分区及其所有索引。 ## 观察发生过程 这很容易演示。创建一个`users`表，包含主键和二十个单列索引（我在生产环境中见过更糟的，你也见过），然后运行最无聊的查询： ``` 1BEGIN; 2SELECT * FROM users WHERE id = 42; 3 4SELECT fastpath, count(*) 5 FROM pg_locks 6 WHERE pid = pg_backend_pid() 7 AND locktype = 'relation' 8 AND relation <> 'pg_locks'::regclass 9 GROUP BY 1; 10 11 fastpath | count 12----------+------- 13 f | 6 14 t | 16 ``` 这是 PostgreSQL 16 的情况：单行主键查找使用 22 个关系锁，填满了所有 16 个快速路径槽位，并将 6 个锁推入共享锁表。每次执行，永远如此。 ## 预处理语句，意外的修复方法 这里有个不明显的事实：预处理语句能消除这个问题，而且与锁规则毫无关系。 预处理语句在前五次执行时使用自定义计划。第六次时，PostgreSQL 构建一个**通用计划**，缓存它，并（如果成本比较合适，对于主键查找来说总是如此）从此重用它。重用缓存计划会完全跳过规划器，而执行缓存计划只会锁定实际*在计划中*的关系，而不是规划器考虑过的所有内容。 ``` 1PREPARE u(bigint) AS SELECT * FROM users WHERE id = $1; 2-- 执行六次以通过自定义计划阶段，然后： 3 4BEGIN; 5EXECUTE u(42); 6 7SELECT relation::regclass AS relation, fastpath 8 FROM pg_locks 9 WHERE pid = pg_backend_pid() 10 AND locktype = 'relation' 11 AND relation <> 'pg_locks'::regclass; 12 13 relation | fastpath 14------------+---------- 15 users_pkey | t 16 users | t ``` 二十二个锁变成了两个，都舒适地位于快速路径中，共享锁管理器从此不再收到这个查询的请求。额外的好处是，你也不再为每次执行重新规划查询而付出代价，这在索引过多的表上本身也不是小的 CPU 开销。 两个注意事项。首先，通用计划有一个众所周知的失败模式：参数分布不均可能导致对特定值的计划严重错误。`plan_cache_mode`在出现这种情况时可以使用，但诚实的答案是测试你的热点查询。 其次，更恼人的是：预处理语句和连接池的关系复杂。PgBouncer (https://www.pgbouncer.org/) 在事务池模式下直到 1.21 版本才支持协议级预处理语句，通过`max_prepared_statements`；如果你使用的是旧版本，请升级。RDS Proxy (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-proxy.html) 更糟：预处理语句会将客户端会话固定到后端连接，这破坏了 RDS Proxy 存在的全部意义——多路复用。如果你的架构依赖于 RDS Proxy，这个修复方案实际上不可用，你应该特别关注下一节。 ## PostgreSQL 18 移除了这个魔术数字 在 PostgreSQL 18 中，得益于 Tomas Vondra (https://www.postgresql.org/message-id/[email protected]) 的工作，快速路径数组不再固定为 16 个槽位。它在服务器启动时根据`max_locks_per_transaction`设置，按 16 个槽位一组分配，因此默认值 64 为每个后端提供 64 个快速路径槽位。相同的表，相同的查询，无需预处理语句： ``` 1SHOW max_locks_per_transaction; 2 max_locks_per_transaction 3--------------------------- 4 64 5 6BEGIN; 7SELECT * FROM users WHERE id = 42; 8 9SELECT fastpath, count(*) 10 FROM pg_locks 11 WHERE pid = pg_backend_pid() 12 AND locktype = 'relation' 13 AND relation <> 'pg_locks'::regclass 14 GROUP BY 1; 15 16 fastpath | count 17----------+------- 18 t | 22 ``` 开箱即用，所有 22 个锁都在快速路径中。（如果你喜欢重演历史灾难，在 PostgreSQL 18 上设置`max_locks_per_transaction = 16`就能恢复旧的 16/6 分配。） 请注意没有专门的配置旋钮；快速路径容量跟随`max_locks_per_transaction`设置，其主要工作是调整共享锁表大小。如果你的 schema 是高度分区或高度索引的，将其设置为超过每次查询的最大关系数。快速路径数组本身几乎不占资源；共享锁表条目更大，但在存在此问题的任何机器上，你都有足够的内存。 ## 或者，听我说：减少索引数量 以上两种方法都只是治标。病因是拥有 21 个索引的表。 你已经知道过度索引的标准成本：每个`INSERT`和非 HOT `UPDATE`需要维护每个索引，`VACUUM`必须处理每个索引，每个索引都占用存储和缓冲区缓存。再加上这一点：通过 PostgreSQL 17，在热点表上第十五个之后的每个索引都会将针对它的每个查询推离锁快速路径，惩罚由整个系统的共享内存竞争承担，而不仅仅是执行查询的那个。 查看`pg_stat_all_indexes`中的`idx_scan`。如果在代表性时间段内为零，且该索引不是用于强制约束，就删除它。你的锁管理器会默默感谢你，每次十六把锁。