1
Diagnostic step 1
Collect concurrent activity traces (pg_stat_activity, pg_locks) during a hang, noting transaction start times, xmin, and query texts; do this non-invasively and avoid killing sessions until analysis is complete.
FULL AI ANALYSIS
PostgreSQL transactions block unexpectedly when mixing SERIALIZABLE snapshot reads and FOR UPDATE in prepared statements
Generated Oct 3, 2026, 12:35:03 AM
Analysis summary
In SERIALIZABLE mode, a transaction acquires a snapshot that can conflict with later updates; using prepared statements can lead to plan/parameter reuse across logical operations and may prolong snapshot visibility if the application leaves transactions open. Reusing a connection with an old snapshot and then trying to take FOR UPDATE locks can generate wait chains because the snapshot's xmin prevents vacuum cleanup and can create conflicts with concurrent updaters. Additionally, server-side prepared statements can cache plans that do not reflect current row visibility, exacerbating waits. The temporary improvement after switching to READ COMMITTED indicates isolation level and snapshot duration is central to the issue.
Possible causes
Application reuse of connections with lingering prepared statements and old snapshots leading to unexpected blocking behavior when combined with FOR UPDATE.
95%Long-lived SERIALIZABLE snapshots (transactions left open) preventing lock acquisition by later transactions that attempt FOR UPDATE on the same rows.
90%Missing or too-large timeouts (statement_timeout/idle_in_transaction_session_timeout) allowing transactions to remain open and accumulate blocking impact.
85%Server-side prepared statement plan caching producing plans that defer locking behavior until execution, interacting poorly with snapshot visibility.
20%Suggested fixes
2
Diagnostic step 2
Enable pg_stat_statements temporarily to identify frequently prepared statements that are executed within long-lived transactions.
3
Diagnostic step 3
Introduce or tighten idle_in_transaction_session_timeout and statement_timeout for suspicious connection pools to fail stuck operations quickly while monitoring impact.
4
Diagnostic step 4
Audit application logic to ensure transactions in SERIALIZABLE mode do not remain open across user think-time; prefer short transactions or switch to READ COMMITTED for operations that will later acquire FOR UPDATE.
5
Diagnostic step 5
If prepared-statement plan caching is suspected, test disabling server-side prepared statements in the client library or use PREPARE/EXECUTE with new statements per transaction in a staging environment; escalate to DBA/architect if behavior persists.