DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 25002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 25002: branch transaction already active

PostgreSQL error code 25002 (branch transaction already active) occurs in distributed transaction environments when a transaction branch with a specific XID (Transaction Identifier) is already active and a duplicate start attempt is made on the same branch. This error is most commonly encountered in Java EE/Jakarta EE application servers using the XA protocol (eXtended Architecture), middleware platforms, or any system implementing Two-Phase Commit (2PC). PostgreSQL rejects the duplicate operation to protect transaction integrity and throws this error immediately.

Top 3 Causes

1. Duplicate XA Transaction Branch Start

The most frequent cause is calling XA START (or its equivalent) with the same XID while that branch is already open in the session. This often happens when a transaction manager loses track of branch state or when connection pools reuse connections without properly cleaning up active branches.

-- Check all currently prepared transactions
SELECT gid, prepared, owner, database
FROM pg_prepared_xacts
ORDER BY prepared ASC;

-- Roll back an orphaned prepared transaction
ROLLBACK PREPARED 'your_duplicate_xid_here';

-- Or commit it if appropriate
COMMIT PREPARED 'your_duplicate_xid_here';
Enter fullscreen mode Exit fullscreen mode

2. Connection Pool Returning Dirty Connections

When using connection poolers like PgBouncer, HikariCP, or c3p0, a connection that holds an unfinished XA branch can be returned to the pool and reassigned to a new request. The new request then tries to start a branch with the same XID, triggering the error. Always validate connection state before reuse.

-- Identify sessions stuck in idle-in-transaction state
SELECT pid, usename, state, query_start, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < NOW() - INTERVAL '5 minutes';

-- Terminate stale sessions holding open transactions
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < NOW() - INTERVAL '10 minutes';

-- Reset connection state before returning to pool
DISCARD ALL;
Enter fullscreen mode Exit fullscreen mode

3. Flawed Retry Logic in Custom 2PC Implementation

When implementing Two-Phase Commit manually with PREPARE TRANSACTION, a naive retry mechanism can attempt to re-prepare an already-prepared transaction after a timeout or transient network error. Without checking existing prepared transactions first, the retry loop fires PREPARE TRANSACTION on an active branch and hits error 25002.

-- Always check before preparing to avoid duplicates
DO $$
DECLARE
    v_gid TEXT := 'txn_order_20240101_001';
BEGIN
    -- Guard: clean up if already exists
    IF EXISTS (
        SELECT 1 FROM pg_prepared_xacts WHERE gid = v_gid
    ) THEN
        RAISE WARNING 'Transaction % already prepared. Rolling back.', v_gid;
        EXECUTE 'ROLLBACK PREPARED ' || quote_literal(v_gid);
    END IF;

    -- Now safely start and prepare
    -- (Actual DML would happen between BEGIN and PREPARE)
END;
$$;

BEGIN;
INSERT INTO orders(id, status) VALUES (9999, 'pending');
PREPARE TRANSACTION 'txn_order_20240101_001';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Identify orphaned prepared transactions older than 30 minutes
SELECT gid, prepared, owner, database,
       NOW() - prepared AS age
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '30 minutes'
ORDER BY prepared;

-- Step 2: Check max_prepared_transactions setting
SHOW max_prepared_transactions;

-- Step 3: Clean up specific orphaned transaction
ROLLBACK PREPARED 'problematic_gid';

-- Step 4: Monitor active backend transactions
SELECT pid, backend_xid, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_xid IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Monitor pg_prepared_xacts proactively.
Set up an automated job (cron or Prometheus alert) to detect prepared transactions older than a defined threshold (e.g., 30 minutes). Any long-lived prepared transaction is a sign of a stuck distributed transaction that will eventually cause 25002 or resource exhaustion.

-- Alert query: prepared transactions older than 30 minutes
SELECT COUNT(*) AS stale_prepared_count
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '30 minutes';
Enter fullscreen mode Exit fullscreen mode

2. Configure connection pools correctly for XA workloads.
For PgBouncer, always set server_reset_query = DISCARD ALL and prefer session pooling mode over transaction pooling mode when XA transactions are in use. For HikariCP, set connectionInitSql to reset state and tune idleTimeout to recycle stale connections automatically. Also ensure max_prepared_transactions in postgresql.conf is set to at least 1.5× the maximum expected concurrent distributed transactions to prevent slot exhaustion cascades.

Related Errors

Code Name Relation
25000 invalid_transaction_state Parent class of 25002
25001 active_sql_transaction Active transaction conflict
25003 inappropriate_access_mode_for_branch_transaction Wrong access mode on branch
25004 inappropriate_isolation_level_for_branch_transaction Invalid isolation level on branch
40001 serialization_failure Often co-occurs in 2PC scenarios

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)