System.Data.SQLite
View Ticket
Not logged in
Ticket UUID: 56b42d99c18456d55e769fd2663ef7a75129c46b
Title: Auto-enlistment in ambient transaction does not use the transaction isolation level
Status: Closed Type: Code_Defect
Severity: Important Priority: Blocker
Subsystem: Enlistment Resolution: Fixed
Last Modified: 2014-04-03 11:41:34
Version Found In: 1.0.89.0
User Comments:
anonymous added on 2013-12-18 20:59:53:
We are using TransactionScope to control transactions, and SQLite connections correctly enlists in the ambient transaction if there is one at connection opening time. However, the SQLite transaction itself is not started using the isolation level of the ambient transaction. Instead it uses the default isolation level configured for the connection. Given that it is a default, and that there is already a controlling transaction with an isolation level specified, I think it would be better if the latter was used, thereby avoiding having to control isolation level for each connection individually.

mistachkin added on 2013-12-19 02:16:52:
A prototype fix for this is checked-in [4f050c73ff] on the "tkt-56b42d99c1"
branch.  Could you please try this fix in your environment, see if it resolves
the issue, and report back?  In Fossil, to update to that branch, the following
command may be used:

fossil update tkt-56b42d99c1

mistachkin added on 2013-12-20 16:59:54:
Ticket is pending closure waiting for a response from the originator.

mistachkin added on 2013-12-20 17:03:29:
Fix merged to trunk via check-in [f89efea9e9].

anonymous added on 2014-03-28 20:02:06:
(Assuming that I'm correct in my belief that SQLite only allows for ReadUncommitted and Serializable...)


This change broke my code.  I use a Data Access Layer interface to generalize across several data access providers.  I specify Snapshot isolation in my next layer, which SQLite does not support, and now throws "isolationLevel".  I guess it was using the connection default of Serializable before, but now it's failing if the Transaction enlisted doesn't match.

Is there a more robust resolution that can avoid such leaks in my abstraction layer?

mistachkin added on 2014-03-28 22:01:25:
I'll look into it; however, throwing an exception for an unsupported isolation
level seems like a reasonable thing to do?

mistachkin added on 2014-03-30 01:51:38:
I've made some changes on the [/info/isolationLevels] branch that add a new
connection flag named "MapIsolationLevels".  When this connection flag is used,
one of the known transaction isolation levels will be used when enlisting into an
existing transaction.

Could you please test these changes in your environment and let me know if they
work for you?

In order to make use of these changes, you will need to add the following to your
connection string:

    Flags=MapIsolationLevels;

anonymous added on 2014-04-03 11:41:34:
This seems like a good resolution to me.  Thank you.  I would not be setting this flag in my environment, so the old behavior is expected and should work great.