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

Last updated on JANUARY 19, 2018

Applies to:

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.

Cause