PostgreSQL Inappropriate Isolation Level for Branch Transaction (SQLSTATE 25004)

When a distributed transaction branch is started with an isolation level that conflicts with the coordinating transaction or the constraints of the two-phase commit protocol, PostgreSQL raises ERROR: inappropriate isolation level for branch transaction with SQLSTATE 25004 and condition name inappropriate_isolation_level_for_branch_transaction. This error belongs to SQLSTATE class 25 — Invalid Transaction State.

What This Error Means

SQLSTATE class 25 covers errors related to invalid transaction state. Code 25004 specifically pertains to distributed (branched) transactions, which are associated with the SQL standard's concept of transaction branches used in two-phase commit (2PC) scenarios. In PostgreSQL, this surfaces most commonly through the PREPARE TRANSACTION / COMMIT PREPARED mechanism or through foreign data wrapper (FDW) connections that participate in a distributed transaction.

A branch transaction is a unit of work associated with a global transaction identifier (XID) that spans multiple resource managers or database connections. The SQL standard requires that all branches of a global transaction use compatible isolation levels. PostgreSQL enforces this constraint: if a branch is started with an isolation level that cannot be reconciled with the global transaction's requirements — for example, attempting to start a READ UNCOMMITTED or READ COMMITTED branch when the coordinating transaction demands SERIALIZABLE — the engine raises SQLSTATE 25004.

In practice, PostgreSQL does not implement READ UNCOMMITTED as a truly separate level (it is silently promoted to READ COMMITTED), and the actual enforcement of branch isolation compatibility depends heavily on the transaction manager coordinating the distributed work. When PostgreSQL is acting as a resource manager inside an XA transaction (via a JDBC XA datasource or a JTA transaction manager such as Atomikos or Narayana), the middleware layer transmits isolation level requests to each branch. If a branch receives an isolation level that PostgreSQL considers inappropriate for the current transaction state, 25004 is raised.

Common Causes

1. XA/JTA transaction manager misconfiguration. A Java EE or Jakarta EE application server or a standalone JTA provider (Atomikos, Bitronix, Narayana) is configured with a global transaction isolation level that conflicts with what was already set on the PostgreSQL connection or branch. For example, the XAConnection starts a branch with TRANSACTION_SERIALIZABLE after the global coordinator has already established TRANSACTION_READ_COMMITTED on another branch.

2. Attempting to change isolation level mid-branch. A driver or ORM issues SET TRANSACTION ISOLATION LEVEL after a two-phase transaction branch has already been started with XAResource.start(), violating the rule that isolation level must be set before the first query of a transaction.

3. Foreign Data Wrapper (FDW) branch mismatch. When postgres_fdw (or another FDW) participates in a distributed transaction via PREPARE TRANSACTION, the remote connection is opened as a transaction branch. If the local transaction runs at SERIALIZABLE but the FDW connection is established at READ COMMITTED, a branch isolation mismatch can result in this error on the remote side.

4. Manual PREPARE TRANSACTION with conflicting isolation levels. A developer or script explicitly uses BEGIN ISOLATION LEVEL SERIALIZABLE; ... PREPARE TRANSACTION 'txn-id'; in one session and then tries to associate another branch at a lower isolation level with the same global transaction.

How to Fix inappropriate_isolation_level_for_branch_transaction

1. Align isolation levels across all branches. Ensure every branch of a global transaction uses the same isolation level. In a JTA application, set the isolation level once on the UserTransaction or DataSource and do not override it per-connection:

   // In a JTA/XA datasource configuration (e.g., Atomikos)
   // Set defaultIsolationLevel consistently for all XA connections
   xaDataSource.setDefaultIsolationLevel(Connection.TRANSACTION_READ_COMMITTED);
   

2. Do not issue SET TRANSACTION after a branch has started. The isolation level must be declared before any data-access statement in the transaction. In SQL, issue the SET TRANSACTION or BEGIN ... ISOLATION LEVEL statement as the very first command:

   -- Correct: isolation level declared at BEGIN
   BEGIN ISOLATION LEVEL REPEATABLE READ;
   -- ... your statements ...
   PREPARE TRANSACTION 'my-global-txn';
   

   -- Incorrect: changing isolation level after work has begun
   BEGIN;
   SELECT * FROM orders WHERE id = 1;
   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; -- raises error in this context
   

3. Configure postgres_fdw to match the local isolation level. When using postgres_fdw with PREPARE TRANSACTION, verify that fdw_startup_cost and connection options do not override isolation. Use the transaction_isolation session default or set it explicitly before the FDW queries execute:

   -- Set at session level before beginning the distributed transaction
   SET default_transaction_isolation = 'repeatable read';
   

4. Review your transaction manager's XA branch start logic. In Atomikos or Narayana, check whether the connection pool applies a default isolation level that differs from what the application sets at the resource manager level. Ensure xa.start() is called with a clean, consistently configured connection.

Additional Information

• SQLSTATE class 25 (Invalid Transaction State) includes several related codes: 25000 (generic invalid transaction state), 25001 (active_sql_transaction — e.g., SET SESSION inside a transaction), 25002 (branch_transaction_already_active), 25003 (inappropriate_access_mode_for_branch_transaction), 25006 (read_only_sql_transaction), 25007 (schema_and_data_statement_mixing_not_supported), and 25P01 / 25P02 (PostgreSQL-specific no_active_sql_transaction and in_failed_sql_transaction).

• SQLSTATE 25004 is defined by the SQL standard (SQL:1999 and later) and is not specific to PostgreSQL. However, because PostgreSQL's native transaction model is single-database and primarily single-phase, this error is rarely triggered in simple setups. It almost exclusively appears in XA/JTA-managed environments or explicit two-phase commit workflows.

• PostgreSQL has supported PREPARE TRANSACTION (two-phase commit) since version 8.0. The max_prepared_transactions GUC must be set to a non-zero value to use it; the default is 0 in many distributions, which disables 2PC entirely. If 2PC is disabled, you cannot create the branch conditions that lead to 25004.

• Most ORM frameworks (Hibernate, SQLAlchemy, ActiveRecord) do not use XA transactions directly and will not produce this error under normal usage. It is almost always triggered by middleware transaction managers or explicit low-level JDBC/ODBC XA API usage.

Frequently Asked Questions

Why does this error only appear in my application server environment and not in psql? SQLSTATE 25004 requires a distributed (multi-branch) transaction context, which is created by XA/JTA transaction managers or explicit PREPARE TRANSACTION calls. When you run queries directly in psql, you are always in a single-branch local transaction, so the branch isolation constraints never come into play.

Does PostgreSQL actually distinguish between READ UNCOMMITTED and READ COMMITTED? No. PostgreSQL treats READ UNCOMMITTED as equivalent to READ COMMITTED internally — dirty reads are never permitted. However, when an XA transaction manager explicitly requests READ UNCOMMITTED as the branch isolation level via the JDBC XAResource API, PostgreSQL may still raise 25004 if the coordination protocol considers this level incompatible with the global transaction state.

Can I see which prepared transactions are currently open on my server? Yes. Query the pg_prepared_xacts system view:

SELECT gid, owner, database, prepared, transaction
FROM pg_prepared_xacts;

This helps diagnose orphaned prepared transactions that might be holding locks or contributing to branch state issues.

Is there a way to disable isolation level enforcement for XA branches? No — this is mandated by the SQL standard and enforced by PostgreSQL's transaction state machine. The correct fix is to ensure consistent isolation levels across all branches rather than attempting to bypass the check.