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!
I’ve checked this issue with lucee latest version 5.3.10.19-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;
</cfquery>
<cftransaction>
</cftransaction>
@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 am looking for recommendations for a commercial support provider with the appropriate experience to be able to expedite a fix here. We have an application where we need this to work and are using a relatively unreliable workaround technique to attempt to restore context that is not being preserved because we cannot turn this option on.
Does anyone have any suggestions or recommendations for what we can do here? You can connect me directly at butlerm23 at gmail.com if that would be preferable. Thanks!
To address a couple of your questions from before:
Not really. Even though it’s a behavior Adobe always did behind the scenes, I’ve rarely ever seen a situation that required it. So turning on that setting, probably puts you in a small 2% minority of users who use it.
I’ve played with it before and it never caused issues, but I have a feeling your issues are specific to Oracle. Oracle is already not widely used in general, and I rarely see Lucee users on Oracle. So Using Oracle on Lucee probably puts you in another small minority.
I hate to make assumptions, but it sounds like perhaps you’re over-using transactions Most databases auto-commit which is fine for single statements-- an insert or an update. Only when I have related modifications that need to maintain state do I use a transaction. But perhaps that’s all you mean. My experience is most devs don’t use transactions at all (not that it’s a good thing) so the fact that you’re using transactions puts you in a minority.
Again, my assumption is it works fine for SQL Server, MySQL, and Postres, which comprises 90% or more of all Lucee devs. I think your issues are specific to Oracle.
Not that I’ve aware of.
It probably is.
it is worth fixing, but if you want it fixed, you’ll likely need to lubricate the wheels of progress by paying for it. Lucee prioritizes fixes based on how many users they affect. I don’t think you’re doing anything wrong here, you’ve just managed to find an unfortunate combination that doesn’t work, but you’re probably one of the only Lucee users out there hitting this exact combination. Not a great thing to hear, of course, but it’s probably true.
I’d recommend you reach out directly to @Gert@Gert_Franz1 or @micstriit at Rasia, who offer paid consulting services to fix Lucee bugs. Otherwise, your ticket will likely sit for a long time before it gets looked at.
According to testing that has been done (read the ticket), this problem also occurs with the jTDS, MySQL, and Postgres drivers. I am pretty sure they aren’t a small minority.
As far as transactions go, I have a hard time believing that any non-toy general purpose database application manages to avoid the use of even a single database transaction in the entire system without use of a non-trivial amount of manual code to do what every normal relational database on the planet ships with out of the box. We are talking about 1979 level technology after all. ACID semantics for relational databases weren’t a need that developed yesterday.
I guess MySQL users in the 3.23 era about twenty years ago were still lacking transaction support and had to write manual workarounds, but no other commercial relational database I am aware of. If MySQL still lacked transaction support it would be rightly mocked.
And yes, I asked for recommendations for a qualified support provider for a reason, and thanks for providing one.
Wow, good to know. That may help your cause. I suppose it simply turns out no one is using the exclusive request feature then. I have a feeling it was added by request for a client who had a specific purpose and it worked for them and was never touched since.
Oh believe it brother I’ve been a CF consultant for 10 years now and I’ve seen a lot of client code in a lot of apps. Want to know what I don’t see much of? Transactions I’m not saying no one uses them, I’m just saying they’re not as common as they probably should be. Which means your special nexus of buggy-ness has an even smaller surface area of being noticed. I learned a long time ago not to say things like, “Surely I’m’ not the only person who has…” because somehow those sort of statements wind up being true more than you ever would have thought!
Good luck-- if you can get ahold of Gert and Micha, they can fix you up for sure. Micha is the lead dev who created Lucee so I’m sure he knows exactly where to put the fix.
The need to use Exclusive Connections is:
1- Use temporary tables, the data remains and can be accessed only in the same connection.
2- Obtain the curVal of a sequence, after using the nextVal, that can be accessed only in the same connection.
3- Use triggers that use the Oracle package (we use it for automatic management of auto-incremental fields, emulating the identity of sql server: the trigger obtains the nextval and saves it in the package, which can only be accessed by the same connection)
4- Migrate thousand of thousand of code lines from ACF.
Regards
I hate to say this, but one alternative here is to rewrite all your code so that all SQL statements occur inside a page level cftransaction block. Then the connection and its associated state will be preserved. That may not be very convenient, but apparently making this option work correctly is quite difficult for some reason.
Most databases auto-commit which is fine for single statements-- an insert or an update. Only when I have related modifications that need to maintain state do I use a transaction. But perhaps that’s all you mean. My experience is most devs don’t use transactions at all (not that it’s a good thing) so the fact that you’re using transactions puts you in a minority.
I’m not saying no one uses them, I’m just saying they’re not as common as they probably should be. Which means your special nexus of buggy-ness has an even smaller surface area of being noticed. I learned a long time ago not to say things like, “Surely I’m’ not the only person who has…” because somehow those sort of statements wind up being true more than you ever would have thought!