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 11.2.0.1 and laterPrecompilers - Version 11.2.0.1 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.
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
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
| Symptoms |
| Cause |
| Solution |
| References |