System.Data.SQLite

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.

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

Ticket is pending closure waiting for a response from the originator.

Fix merged to trunk via check-in [f89efea9e9].

(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?

I'll look into it; however, throwing an exception for an unsupported isolation
level seems like a reasonable thing to do?

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;

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.