Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

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

Attachments

4
  • 02 Mar 2020, 03:52 pm
  • 02 Mar 2020, 03:52 pm
  • 27 Nov 2019, 10:16 am
  • 27 Nov 2019, 10:16 am

Activity

Show:

Zac Spitzer 14 December 2020 at 11:55
Edited

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

Guardian 14 December 2020 at 10:20

Should I create new issue?

Guardian 14 December 2020 at 10:09
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)

Michael Offner 2 March 2020 at 15:50
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

 

https://github.com/lucee/Lucee/commit/dd082093ab59ad05513ab355a52ffdaa12f55e47

Michael Offner 2 March 2020 at 10:24

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.

 

Fixed

Details

Assignee

Reporter

Priority

Labels

Fix versions

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Sprint

Affects versions

Created 27 November 2019 at 08:07
Updated 26 March 2021 at 13:56
Resolved 2 March 2020 at 16:12

Flag notifications