database resource_error ai_generated true

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

ID: database/postgresql-too-many-snapshots

Also available as: JSON · Markdown · 中文
78%Fix Rate
89%Confidence
1Evidence
2024-04-18First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
PostgreSQL 14 active — — —
PostgreSQL 15 active — — —
PostgreSQL 16 active — — —

Root Cause

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

中文

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

Official Documentation

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

Workarounds

  1. 85% success 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. 
    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. 80% success 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'; 
    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. 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';

Dead Ends

Common approaches that don't work:

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

    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% fail

    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.