When the Exclusive Connections for Request datasource option is turned on, Lucee does not appear to set autocommit off on entry to a cftransaction block. This causes partial transactions to be committed and for the Oracle driver to throw an exception when the final commit implicit in the end of the cftransaction block is executed.
The Oracle driver reports the error as “Could not commit with auto-commit set on”. Other than that the Exclusive Connections for Request option (which we really need unfortunately) seems to work fine. We only have a problem when using a cftransaction block when that option is enabled for the datasource.
This code works fine in ACF. There is nothing particularly special about it other than requiring exclusive connections per request as in ACF.
Oracle does provide an option to turn the “could not commit with auto-commit on” check off, but that will not solve the problem of autocommit actually being on in a cftransaction block, which rather defeats the purpose of using a transaction in the first place, as you might imagine.
We would expect that autocommit would be turned off on entry to a cftransaction under all conditions - even when the Exclusive Connections per Request datasource option is turned on, as in this case. Otherwise there is no ability to commit or rollback the transaction. Oracle will issue a similar error if you try to roll back while autocommit is on.
Our problem is we need the Exclusive Connections per Request option to be enabled to preserve Oracle session state, but we cannot turn it on because cftransaction blocks do not work when it is enabled.
There does not appear to be any other work around other than possibly leaving the option off and placing everything in cftransaction blocks, which appear to work fine and use the same connection even when the Exclusive Connections per Request option is off. We are naturally not particularly excited about putting in artificial transaction blocks for every series of read only queries in the system.
Does anyone actually use the Exclusive Connections for Request option? It would seem nearly impossible with the code in its current state.
I am not sure how one would make a serious application without working cftransaction blocks. I am a little curious why someone went to a great deal of effort to add this feature if it can’t really be used for anything.
Is it a work in progress? This seems like a simple bug, to start with it would be nice to hear some agreement from anyone that it does actually appear to be a problem worth fixing. Thanks!
can you file a bug in jira?
then if possible, could you create and file a testbox test case against this repo?
drop them in the /tests folder, you can copy the example from Lucee/LDEV0860.cfc at 6.0 · lucee/Lucee · GitHub
we don’t run our normal CI against Oracle because the oracle service image takes a ridiculous amount of time to start
I’ve checked this issue with lucee latest version 220.127.116.11-SNAPSHOT. I replicated the issue with the below scenario.
I enabled the Exclusive connections for request option in datasource and run the below code which have a cfquery and empty transaction it throws the error like Could not commit with auto-commit set on.
Also if not enabling the Exclusive connections for request option means lucee doesn’t throw the error
<cfquery name="queryresult" datasource="datasourceName">
SELECT * FROM tableName;
@butlerm have you faced the issue in the above mentioned scenario? if no means can you please share your testcode?
Yes, we have identical symptoms. Any page no matter what we are doing, the end of a cftransaction block reports a “Could not commit with auto-commit set on” error from the Oracle driver when the exclusive connections for request option is enabled.
@butlerm can you please file a bug in Jira for this issue: https://luceeserver.atlassian.net
And add your testcode if possible. Also, mention your lucee version and an oracle driver version.
I created issue LDEV-4056 in Jira for this, thanks.