found that Setting once transaction isolation level, it is persistent, for this connection, till next change of it, or reboot.
"Exclusive connections for request" is set ON.
In ACF10 and ACF18 it works properly. Isolation level is set only for one request then is changing back.
Workaround would be to set isolation level back to “read committed” in every query that change connection state, and to catch errors (ex. timeout) to set back read committed state.
In this catch clause we should find, get and use only connection that was used for changing connection state.
Using SQL profiler I found that:
send before every request “SET TRANSACTION ISOLATION LEVEL READ COMMITTED“
Send after every request 2 requests “SET TRANSACTION ISOLATION LEVEL READ COMMITTED“
just send request.
Now, I don’t know how to proceed. Will it be implemented in Lucee, or I should change it in my project?
Microsoft SQL Server 2014
Windows Server 2012
we need to find an other solution for this, we will no increase the amount of request to the connection by that much. This would affect performance/network traffic for ALL connections, even for most this is a non issue.
As the description of the tickets makes it clear, ACF makes 3 “send” where we do only one! We actually just removed the “setAutocommit(true)” done by new connections, because it was useless.
There are 2 things to address:
Lucee should keep track of changes of Lucee itself does to the connection and if done reset to default values.
Lucee may should have a setting you can do in case you do changes like this in your SQL code itself and tell lucee to reset always as ACF does.
a described above we solved this in 2 pieces, first we reset to default isolation at the end of every cftransaction and we added a setting to the lucee admin and the Application.cfc to force a reset every time you get a connection.
Please apply this fix also for JTDS driver,
because now, this option exist in admin panel, but it doesn't work. (not revert isolation level to default state)
Should I create new issue?
yep, always create a new one, if the issue is already closed off