PostgreSQL Inappropriate Access Mode for Branch Transaction (SQLSTATE 25003)

PostgreSQL raises ERROR: inappropriate access mode for branch transaction with SQLSTATE 25003 and condition name inappropriate_access_mode_for_branch_transaction when a branch of a distributed (multi-database) transaction attempts to use an access mode — such as READ WRITE — that conflicts with the access mode established for the global transaction. This error belongs to SQLSTATE class 25, which covers invalid transaction state errors.

What This Error Means

SQLSTATE class 25 groups errors that arise when a SQL statement is issued while the current transaction is in a state that does not permit that operation. The specific code 25003 targets branch transactions: a scenario defined in the SQL standard where a global transaction is coordinated across multiple database connections or resource managers, each participating as a "branch."

In a distributed transaction, the global coordinator establishes properties for the entire transaction — including its access mode (READ ONLY or READ WRITE). Each branch must respect those global properties. If a branch attempts to operate with an access mode that contradicts what the global transaction allows — for example, a branch trying to write data inside a globally READ ONLY transaction — PostgreSQL raises SQLSTATE 25003.

PostgreSQL itself does not natively implement the full SQL-standard distributed branch transaction protocol in its core engine. However, this error code is defined in PostgreSQL's error taxonomy (see errcodes.h) and can be raised by extensions, foreign data wrappers, or external transaction managers (such as XA-protocol drivers) that coordinate multi-branch transactions. After this error fires, the current transaction is aborted and must be rolled back before any further SQL can execute.

Common Causes

1. XA transaction branch mismatch: A Java application using JDBC with XA transactions (JTA/JCA) starts a global XA transaction with READ ONLY isolation but then attempts to enlist a branch that performs INSERT, UPDATE, or DELETE operations, triggering the access mode conflict.

2. Middleware or ORM misconfiguration: A connection pooler or ORM layer (e.g., a Java EE application server) begins a distributed transaction with one set of properties and later opens a subordinate branch connection with incompatible SET TRANSACTION parameters.

3. Manual SET TRANSACTION inside a branch: Explicitly issuing SET TRANSACTION READ WRITE after a branch has already been associated with a READ ONLY global transaction, or vice versa.

4. Foreign data wrapper or extension: A PostgreSQL extension acting as a distributed transaction participant enforces access mode consistency and raises 25003 when a write operation is attempted against a read-only global transaction.

How to Fix inappropriate_access_mode_for_branch_transaction

1. Align branch access modes with the global transaction. Ensure all branches are started with access modes consistent with the global transaction. If the global transaction is READ ONLY, every branch must also be READ ONLY:

   -- Global coordinator sets READ ONLY
   START TRANSACTION READ ONLY;
   
   -- All branches must also be READ ONLY
   -- Do NOT issue writes or SET TRANSACTION READ WRITE within any branch
   

2. Audit XA transaction configuration in your driver or application server. In JDBC XA, check how you begin the global transaction. If any branch needs write access, start the global transaction as READ WRITE:

   // Java XA example: ensure the global TX allows writes if any branch writes
   XAResource xaRes = xaConnection.getXAResource();
   Xid xid = new MyXid(formatId, globalTxId, branchQualifier);
   xaRes.start(xid, XAResource.TMNOFLAGS); // inherits global TX properties
   

3. Remove conflicting SET TRANSACTION calls inside branch sessions. If a branch session issues SET TRANSACTION after the branch has been enlisted, remove or correct those statements. Access mode for a branch must be set before any work is performed:

   -- WRONG: changing access mode after branch starts
   SET TRANSACTION READ WRITE; -- raises 25003 if global TX is READ ONLY
   
   -- CORRECT: don't override or ensure it matches the global TX
   

4. Roll back and restart the transaction. Once 25003 is raised, the transaction is aborted. Roll back fully before retrying:

   ROLLBACK;
   -- Then re-open the transaction with consistent access modes across all branches
   

5. Check extension or FDW documentation. If this error appears in a context involving postgres_fdw, a custom extension, or a third-party transaction manager, consult that component's documentation for how it enforces access mode consistency across branches.

Additional Information

• SQLSTATE 25003 is defined in the SQL standard (ISO/IEC 9075) as part of class 25 — invalid transaction state. PostgreSQL declares it in src/include/errcodes.h but it is not raised by PostgreSQL's core engine in typical single-server usage; it surfaces through XA-compliant drivers or extensions.
• Related SQLSTATE codes in class 25:
  • 25000 — invalid_transaction_state (generic)
  • 25001 — active_sql_transaction (command not allowed in an active transaction)
  • 25002 — branch_transaction_already_active
  • 25004 — inappropriate_isolation_level_for_branch_transaction
  • 25005 — no_active_sql_transaction_for_branch_transaction
  • 25006 — read_only_sql_transaction
  • 25007 — schema_and_data_statement_mixing_not_supported
  • 25P01 — no_active_sql_transaction
  • 25P02 — in_failed_sql_transaction
• Driver behavior: JDBC drivers surfacing XA errors will typically wrap this as an XAException with error code XAER_RMERR or map it to a PSQLException with SQLSTATE 25003. Check your driver's exception hierarchy to catch it correctly.
• Operational implication: This error always aborts the current transaction. Any work performed in the branch before the error is discarded. Ensure your application handles the rollback and retry logic correctly to avoid partial distributed transaction states.

Frequently Asked Questions

Why do I see SQLSTATE 25003 in a single-server PostgreSQL setup? In a standard single-server setup, PostgreSQL's core engine does not raise 25003 directly. If you encounter it, it is almost certainly being raised by a JDBC XA driver, an application server transaction manager (JBoss, WebLogic, GlassFish, etc.), or a PostgreSQL extension that participates in distributed transactions. Check your middleware and driver stack.

What is a "branch transaction" in PostgreSQL terms? A branch transaction is a concept from the SQL standard and the XA (eXtended Architecture) protocol. When a distributed transaction spans multiple databases or resource managers, each participating resource runs a "branch" of the global transaction. The global transaction coordinator assigns each branch a unique branch qualifier and enforces that all branches operate under compatible properties (isolation level, access mode).

How does SQLSTATE 25003 differ from 25006 (read_only_sql_transaction)? 25006 is raised when you attempt a write operation in a session-level READ ONLY transaction (e.g., SET default_transaction_read_only = on). 25003 specifically concerns the access mode mismatch between a distributed transaction branch and its global transaction context — it is about coordination between branches, not a simple session-level restriction.

Can I catch this error and retry in my application? Yes, but only after a full rollback. When 25003 is raised, PostgreSQL aborts the transaction. You must roll back all branches, then restart the distributed transaction from the beginning with consistent access modes. Partial retries without a full rollback will fail with 25P02 (in_failed_sql_transaction).