There are a number of timeouts used within XA implementations to mainly avoid locking issues between the prepare and commit phases. E.g. if a TM disappears after the prepare stage, the RM needs to be able to release locks for other transactions that may not be managed by the failing TM instance. The rule of thumb for these timeouts is that those used by the RM must be greater than those used by the TM, such that the TM always remains in control of the distributed transaction.
For instance, if the RM’s timeouts are less than the TM’s timeouts, then the RM may discard a transaction without reference to the controlling TM. A common result in this case is a response of “ORA-24756: Transaction does not exist” from the Oracle database.
Within the Oracle XA implementation there are a number of timeouts that are utilized to determine failures within a distributed transaction. Two of these timeouts are passed by the TM to Oracle when initially opening a connection using the xa_open api, these are:
- SesTm (session timeout) specifies the maximum length of time a transaction can be inactive before it is automatically aborted by the system. For example, if the TM uses a remote procedure call between the client and the server, then SesTM applies to the time between the completion of one RPC and the initiation of the next RPC, or the commit / rollback;
- SesWt (session wait) specifies the maximum timeout limit when waiting for a transaction branch that is being used by another session. The default value is 60 seconds.
Both the SesTM and SesWt timeouts are set in TM specific configuration files via the database connection string. SesTM is mandatory, whilst SesWt is often left to its default value. An example connection string is as follows:
ORACLE_XA+SqlNet=SID3+ACC=P/user/pwd +SesTM=10+LogDir=/usr/local/xalog
The SesTM and SesWt timeouts do not apply to XA transactions managed through a JDBC connection, in these cases the setTransactionTimeout method is used instead.
Similarly, for PL/SQL (from Oracle Database 11gR1), the XA_SETTIMEOUT function within the DBMS_XA package can be used to set the transaction timeout, the default value is 60 seconds.
Additionally there are the following timeouts that need to be considered, these are:
- The TM’s global transaction timeout (for Java EE based Application Servers this is the JTA timeout);
- The Oracle database initialization parameter DISTRIBUTED_LOCK_TIMEOUT which limits how long distributed transactions will wait for a lock;
- The CONNECT_TIME & IDLE_TIME parameters within an Oracle database users PROFILE (these are both usually defaulted to UNLIMITED for the DEFAULT profile)
Generally, these timeouts should be set in the following order:
1. TM’s global transaction timeout (or the JTA timeout) <
2. SesTm and SesWt (Transaction Timeout for JDBC or PL/SQL) <
3. DISTRIBUTED_LOCK_TIMEOUT <
4. Either of CONNECT_TIME or IDLE_TIME
Note that if a session times out within Oracle, then the Oracle database’s Process Monitor (PMON) will delete the transaction branch if not prepared or cause the transaction to become in-doubt if prepared. As Process Monitor (PMON) activates every 60 seconds if not already active, there can be a delay of up to 60 seconds before the session is tidied up after the session timeout expiring. Therefore the time between the last action taking place e.g. “prepare” and the transaction subsequently being
rolled back or appearing as an “in-doubt” transaction is the SesTm timeout plus up to 60 seconds.
If a transaction times out and becomes “in-doubt”, it will appear in the DBA_2PC_PENDING and DBA_PENDING_TRANSACTIONS views. The in “in-doubt” transaction can then cause any subsequent transactions that wish to access this data to fail with “ORA-01591 lock held by in-doubt distributed transaction <tran id >”. Within the majority of cases, the in-doubt transaction is automatically recovered once the TM can communicate to the RM. Manual resolution should only be
required when the in-doubt transaction has locks on critical data, resources or the cause of the machine, network, or software failure cannot be repaired quickly. However, manual resolution of a transaction may result in Heuristic completion of the distributed transaction, which itself may need resolving later on.
Alternatively, if the DISTRIBUTED_LOCK_TIMEOUT is set to low, then transactions may timeout waiting for other sessions to complete. This can result in “Error - ORA-02049 timeout: distributed transaction waiting for lock”.