database resource_error ai_generated true

psycopg2.OperationalError：错误：共享内存不足 提示：您可能需要增加 max_locks_per_transaction。

psycopg2.OperationalError: ERROR: out of shared memory HINT: You might need to increase max_locks_per_transaction.

ID: database/postgresql-too-many-snapshots

其他格式: JSON · Markdown 中文 · English
78%修复率
89%置信度
1证据数
2024-04-18首次发现

版本兼容性

版本状态引入弃用备注
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —

根因分析

PostgreSQL 用于锁管理的共享内存耗尽，通常是因为太多并发事务持有锁或快照，超过了 max_locks_per_transaction * max_connections 的限制。

English

PostgreSQL's shared memory for lock management is exhausted, typically because too many concurrent transactions hold locks or snapshots, exceeding the max_locks_per_transaction * max_connections limit.

generic

官方文档

https://www.postgresql.org/docs/16/runtime-config-resource.html#GUC-MAX-LOCKS-PER-TRANSACTION

解决方案

  1. Increase max_locks_per_transaction in postgresql.conf (e.g., to 128 or 256), then restart PostgreSQL. Verify with SHOW max_locks_per_transaction;. Also consider reducing the number of concurrent long-running transactions.
  2. Identify and terminate idle-in-transaction sessions that hold locks: SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle in transaction' AND xact_start < now() - interval '10 minutes';

无效尝试

常见但无效的做法:

  1. Restart PostgreSQL to clear the shared memory, assuming it's a temporary leak 85% 失败

    If the workload pattern (e.g., many concurrent long-running transactions) remains the same, the error will recur quickly after restart.

  2. Increase max_connections instead of max_locks_per_transaction 75% 失败

    max_connections increases the number of possible connections but does not increase the lock space per connection; the shared memory limit is still bounded by max_locks_per_transaction * max_connections.