PostgreSQL transactions block unexpectedly when mixing SERIALIZABLE snapshot reads and FOR UPDATE in prepared statements

Environment

PostgreSQL 13.5 on Ubuntu 20.04, application server using libpq with prepared statements (persistent prepared transactions), workload: web app that uses a read step (SELECT ... FOR SHARE) under SERIALIZABLE isolation and later issues SELECT ... FOR UPDATE in a follow-up prepared statement in the same logical transaction.

Symptoms

Certain user operations hang indefinitely during peak load; stuck backend processes show an open transaction holding a tuple lock. Other transactions attempting to update the same rows wait and do not time out. Manual inspection shows prepared statements created by the application are executing the FOR UPDATE step but are blocked on a lock held by an earlier transaction that had previously executed a SERIALIZABLE snapshot read.

Observations

ps aux shows application backend processes waiting in 'idle in transaction' state. pg_locks reveals a tuple lock held by a session that performed a SERIALIZABLE SELECT; its xmin and snapshot_xmin indicate an old snapshot. Executing SELECT pg_prepared_statements() shows many prepared statements with identical SQL text (placeholders) and different parameter sets. Statement_timeout is set globally to 30s but the app's connection pool sets it to NULL for some connections. The blocked queries do not produce deadlock errors — they simply wait. VACUUM activity and autovacuum are normal.

Already attempted

Restarted the app to clear prepared statements (temporary improvement). Increased statement_timeout and idle_in_transaction_session_timeout in a staging environment which caused blocked transactions to fail faster. Reviewed ORM configuration and found it uses server-side prepared statements by default and sometimes reuses a connection with older snapshot state. Tried setting default_transaction_isolation to READ COMMITTED on a test replica which removed hangs for the same load pattern.

Question

How can mixing SERIALIZABLE snapshot reads and later FOR UPDATE operations in prepared statements cause persistent blocking, and what application- or server-side mitigations (configuration or code changes) will avoid long waits without altering transaction semantics? What diagnostic steps isolate whether prepared-statement plan caching or snapshot lifetime is the primary cause?

Post timeline

COMMUNITY RESPONSES

Verified community troubleshooting

0 replies

No answers yet..

Showing 0 of 0 community responses

AI DIAGNOSTICS

Incident analysis

Updated 1w ago• Trouble ID

Most likely 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%

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.

Suggested Fixes

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.

Diagnostic step 2

Enable pg_stat_statements temporarily to identify frequently prepared statements that are executed within long-lived transactions.

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.