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 an application attempts to start a transaction branch that is already open and active. This error is most commonly seen in systems using the XA protocol, Two-Phase Commit (2PC), or middleware like Java EE JTA implementations that manage distributed transactions across multiple resources.

Top 3 Causes

1. Duplicate XA / Global Transaction ID Reuse

The most frequent cause is reusing the same transaction branch identifier (GID) while the previous branch is still active. Every XA branch must have a globally unique ID combination. Bugs in transaction management code or misconfigured connection pools often lead to this collision.

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

-- Clean up a stuck prepared transaction
ROLLBACK PREPARED 'duplicate_branch_txn_id_001';
-- or commit if that's the correct resolution
COMMIT PREPARED 'duplicate_branch_txn_id_001';

2. Crash Recovery Without Cleaning Up PREPARED Transactions

When an application server crashes mid-way through a 2PC flow (after PREPARE TRANSACTION but before COMMIT PREPARED), the transaction remains in pg_prepared_xacts. If the restarted application doesn't recognize the leftover and tries to open the same branch again, error 25002 is triggered.

-- Identify stale PREPARED transactions older than 30 minutes
SELECT gid, prepared, owner
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '30 minutes';

-- Bulk rollback of stale prepared transactions
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT gid FROM pg_prepared_xacts
        WHERE prepared < NOW() - INTERVAL '30 minutes'
    LOOP
        EXECUTE 'ROLLBACK PREPARED ' || quote_literal(r.gid);
        RAISE NOTICE 'Cleaned up: %', r.gid;
    END LOOP;
END;
$$;

3. Connection Pool Returning Dirty Connections

Connection poolers like PgBouncer in transaction pooling mode are fundamentally incompatible with 2PC. When a connection with an unfinished XA branch is returned to the pool and reused by another request, the new request inherits the existing branch state and triggers 25002 when trying to start a fresh branch.

-- Check current transaction state before starting a new XA branch
SELECT pg_current_xact_id_if_assigned() AS active_txid;

-- Proper 2PC flow with a unique GID (use timestamp + UUID for uniqueness)
BEGIN;

INSERT INTO orders (customer_id, amount) VALUES (42, 9900);

PREPARE TRANSACTION 'svc_order_20240115T143055_f7e6d5c4';

-- Later, after coordination:
COMMIT PREPARED 'svc_order_20240115T143055_f7e6d5c4';
-- or on failure:
-- ROLLBACK PREPARED 'svc_order_20240115T143055_f7e6d5c4';

Quick Fix Solutions

Query pg_prepared_xacts immediately to find and resolve any lingering prepared transactions using COMMIT PREPARED or ROLLBACK PREPARED.
Ensure GID uniqueness by combining a timestamp, service name, and UUID — never hardcode or reuse GIDs.
Switch PgBouncer to session pooling mode if you must use 2PC. Transaction pooling and 2PC do not mix.

-- Useful diagnostic: count stale prepared transactions
SELECT COUNT(*) AS stale_count
FROM pg_prepared_xacts
WHERE prepared < NOW() - INTERVAL '10 minutes';

Prevention Tips

Monitor pg_prepared_xacts continuously. Set up alerts (via Prometheus postgres_exporter or a simple cron job) to notify your team when any prepared transaction is older than a defined threshold (e.g., 5–10 minutes). Stale prepared transactions are almost always a sign of a bug or crash in your transaction coordinator.
Consider replacing 2PC with the Saga pattern in microservice architectures. 2PC introduces tight coupling and is operationally fragile. The Saga pattern (choreography or orchestration-based) achieves distributed consistency without requiring PostgreSQL's prepared transaction mechanism, eliminating error 25002 at its root.

Related Errors

Code	Name	Brief
`25000`	`in_failed_sql_transaction`	Command issued inside an already-failed transaction block
`25001`	`active_sql_transaction`	Command not allowed within an active transaction (e.g., mid-transaction `SET TRANSACTION`)
`40001`	`serialization_failure`	Serialization conflict; can co-occur with 25002 in distributed setups

📖 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.

DEV Community