Commit Of Read Only Transactions Takes Delay of a Second in XA

(Doc ID 2347070.1)

Last updated on JANUARY 19, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Precompilers - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

An application using the compiler directive CLOSE_ON_COMMIT to make sure the cursors opened by the programs are close on transaction commit. Connection to Oracle DB is using XA. When a transaction is committed which did update the DB, the commit is immediate (few milliseconds), but when committing a read only transaction, the execution of xa_commit on Oracle server takes always 1 second.

Sample result of the test execution is below, where it can be seen clearly that commit of read only transaction (first execution) takes 1 second, while commit of transactions which has performed any table update is immediate:


Test COMMIT of read only transaction
-------------------------------------
 1513066471:977512 -- xa_open ret= 00
 1513066471:978428 -- xa_start ret= 00
  *** No SQL update performed
 1513066471:978861 -- xa_end ret= 00
 1513066471:979411 -- xa_prepare ret= 03
 1513066471:979423 -- BEFORE xa_commit ret= 03
 1513066472:981396 -- AFTER xa_commit ret= -04
 1513066472:982610 -- xa_close ret= 00
Commit takes 1 second
Note: first column (for example, 1513066471:979423) is the local time in seconds (1513066471) and microseconds (979423.


Test COMMIT with table update
-----------------------------
 1513066473:328982 -- xa_open ret= 00
 1513066473:329704 -- xa_start ret= 00
  *** Perform update
 1513066473:331721 -- xa_end ret= 00
 1513066473:337199 -- xa_prepare ret= 00
 1513066473:337214 -- BEFORE xa_commit ret= 00
 1513066473:338622 -- AFTER xa_commit ret= 00
 1513066473:340080 -- xa_close ret= 00
Commit just takes 1,4 millisecond

An ORA-24756 reported and 1 second delays observed on the client side in relation to commits for read-only transactions.

$ oerr ora 24756
24756, 00000, "transaction does not exist"
// *Cause: An invalid transaction identifier or context was used or the
// transaction has completed.
// *Action: Supply a valid identifier if the transaction has not completed
// and retry the call.

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms