Oracle: Distributed transaction timeouts
Last update: 6 February 2019 (cf)
Summary
When using an Oracle database in conjunction with the Fabasoft Folio Distributed Transaction Manager (DTM), while having incorrectly configured distributed transaction timeouts, there may be database errors on commit or other actions concerning transactions.
Information
When using the Fabasoft Folio DTM with an Oracle database, there are rare conditions that may lead to a number of pending transactions on your database. Due to this commit and rollback may be impaired, leading to errors on usage of the affected domain.
Example
Data Access Error: Database system error: ORA-02089: COMMIT is not allowed in a subordinate session
These errors are potentially caused by incorrectly configured distributed transaction timeouts:
- DISTRIBUTED_LOCK_TIMEOUT - configured on the Oracle database
This value determines the timeout for distributed transactions in the Oracle Database. - SesTm - configured in the Windows registry
This value determines the timeout for database sessions in the Fabasoft DTM. - FSCDTM_TXLOG_TIMEOUT - configured in the Windows registry
This value determines the timeout for distributed transactions in the Fabasoft DTM.
Solution
Warning: Please consider that both solutions provided below require a complete domain restart!
Verifying distributed transaction timeout settings
In order to verify the currently active timeout settings for your domain please execute the following steps on your backend servers :
- Check the values of these Fabasoft Folio DTM registry-keys. If no value is defined the default values will be used.
- HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\Domain x.y\Service z\Datasources\Default\SesTm (default: 99)
- HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\Domain x.y\Service z\Datasources\Default\FSCDTM_TXLOG_TIMEOUT (default: 90)
- (Domain x.y is your domain id, Service z are all entries of the COO Services)
- Check the value of the DISTRIBUTED_LOCK_TIMEOUT with the following query:
select value from v$parameter where upper(name) = 'DISTRIBUTED_LOCK_TIMEOUT'
Configuring the distributed transaction timeouts
In order to change the distributed transaction timeout settings for your domain please execute the following steps:
- Define values for all settings according to the formula:
"FSCDTM_TXLOG_TIMEOUT " (default: 90) < " SesTm " (default: 99) < " DISTRIBUTED_LOCK_TIMEOUT " (recommended: 300). - Stop all kernel instances
- Stop all COO-services of the affected domain.
Note: Before stopping the COO-services ensure that all kernel instances have been shut down. - Check and if necessary change the value of the " DISTRIBUTED_LOCK_TIMEOUT " setting on the Oracle database.
- Check and if necessary change the value of the " SesTm " and " FSCDTM_TXLOG_TIMEOUT " setting in the registry of each connected service
Note: When using Linux the registry path is mapped to a directory structure in /etc/fabasoft/settings. For more information about managing registry keys under Linux consult the white paper „Fabasoft Folio Environment Variables“.
HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\ Domain x.y\Service z \Datasources\Default\SesTm
HKEY_LOCAL_MACHINE\SOFTWARE\Fabasoft\Fabasoft Components Server\ Domain x.y\Service z \Datasources\Default\FSCDTM_TXLOG_TIMEOUT
- Start all stopped COO-services
- Start the stopped kernel instances and thoroughly test the domain
Clearing all domain transactions
After ensuring that the settings described above are correctly configured
- Stop all COO-services of the affected domain.
Note: Before stopping the COO-services ensure that all kernel instances have been stopped. - Start all COO-services into recovery mode in order to clear all DTM-logs .
- Once again stop all COO-services
- Verify the registry settings of all COO-services
- Check if there are any pending transactions left on the Oracle database.
select * from sys.dba_pending_transactions
select * from dba_2pc_pending
References
For further information regarding the use of the Fabasoft Folio Distributed Transaction Manager please refer to White Paper - Fabasoft Folio Distributed Transaction Manager
Applies to
- Fabasoft Folio (all versions)
- Fabasoft eGov-suite (all versions)
while using an Oracle database