My Oracle Support Banner

Commit Of Read Only Transactions Takes Delay of a Second in XA (Doc ID 2347070.1)

Last updated on FEBRUARY 19, 2019

Applies to:

Oracle Database - Enterprise Edition - Version and later
Precompilers - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.


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.



To view full details, sign in with your My Oracle Support account.

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

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.