BUG Exclusive connections for request. Persistent MSSQL Isolation Level.

Description

Hi,
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:

  1. ACF

    1. send before every request “SET TRANSACTION ISOLATION LEVEL READ COMMITTED“

    2. send request

    3. Send after every request 2 requests “SET TRANSACTION ISOLATION LEVEL READ COMMITTED“

  2. Lucee

    1. 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?

Environment

Microsoft SQL Server 2014
Windows Server 2012

Activity

Show:
Michael Offner
March 2, 2020, 10:24 AM

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:

  1. Lucee should keep track of changes of Lucee itself does to the connection and if done reset to default values.

  2. 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.

 

Michael Offner
March 2, 2020, 3:50 PM
Edited

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.

Lucee Admin

Application.cfc

 

Guardian
December 14, 2020, 10:09 AM
Edited

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)

Guardian
December 14, 2020, 10:20 AM

Should I create new issue?

Zac Spitzer
December 14, 2020, 11:55 AM
Edited

yep, always create a new one, if the issue is already closed off

Fixed

Assignee

Michael Offner

Reporter

Guardian

Priority

Blocker

Labels

Fix versions

Sprint

None

Affects versions

Configure