Microsoft.Data.Sqlite: Support System.Transactions

[divega]

Progress is being made on adding the ambient transaction functionality of System.Transactions in the .NET Core on the 1.2 timeframe, e.g. https://github.com/dotnet/corefx/issues/2949 is already fixed for 1.2 and https://github.com/dotnet/corefx/issues/12534, which will result in SqlClient supporting it in .NET Core. In the meanwhile other ADO.NET providers that work with .NET Framework already have support.

I am creating this issue so that we can make an explicit decision for our SQLite provider. As far as I can see, there are several options we can choose from:

1. Nothing: ambient transactions will be ignored by our ADO.NET provider.
2. Throw: we could implement only enough to throw in the presence of ambient transactions so that customers are more aware that they are not supported.
3. Add support (not sure how complex it will be)

[bricelam]

Marking for re-triage. Given the decision on #319, should this be closed as "wait for feedback" too?

[flensrocker]

Any news on this?

Our usecase: the DbContext is added to dependency injection with a lifetime of transient, so we can get rid of all tracked changes on a ConcurrencyException and start over with reloading the needed entities.

Because of the transient DbContext the UserManager gets its own. If we want to change some data of an user or his claims and some of our own entities, we need to use a TransactionScope. This works well with SQL Server.

In our unit-tests we use SQLite because of simpler setup, but this kind of transaction-control does not work with this provider.

I have two choices:

• learn how to disable the transaction related exceptions of the SQLite provider (and let the unit-tests ignore transaction specific remains in the database of a not working rollback - which will be ok, our unit tests shall not test, if a transaction works or not, and the database is dropped before every test-run)
• learn how to get rid off all changes on a DbContext without disposing and creating a new one

Any hints on this? Thanks!

[flensrocker]

After being forced to think about my "transient problem", it seems that I can solve it, by creating always a new scope with an IServiceScopeFactory and get my DbContext and UserManager from the new scope.

Then I can call BeginTransaction on my DbContext, make multiple changes with the UserManager and can rollback all changes if there's a problem.

Is this the way it should be?

[tidusjar]

Is there any news on this?

[dazinator]

I'd also like to know if there is any news on this. Currently I am using the SQLite provider "in memory" to simplify testing. I have hit a scenario where code that works in production (where SQL server is used) fails at test time, because SQL server provider supports ambient transactions, where as the SQLite provider throws an error to say ambient transactions aren't supported.

[ajcvickers]

@dazinator This issue is in the Backlog milestone. This means that it is not going to happen for the 3.0 release. We will re-assess the backlog following the 3.0 release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[dazinator]

@ajcvickers Ok thanks, I'll find a workaround.

[mdonoughe]

This seems to have regressed from 2.0 to 2.1. It used to be that you would get an ignorable warning about there being an unsupported ambient transaction, but since 2.1 you get the same warning followed by a NotSupportedException. Shouldn't there be a return after the warning?

[ajcvickers]

@mdonoughe Can you please file a new issue for this and include a small, runnable project or complete code listing that demonstrates what you are seeing.

[vitidev]

@dazinator You can inherit SqliteConnection and add TransactionScope support...if you can pass own connection class

[EarthDragon88]

@dazinator Could you share if you found a workaround? I'm having the same issue.

[natalie-o-perret]

Anything new about that support?

[natalie-o-perret]

There is a support for that in the System.Data.SQLite, not sure if its implementation can actually match the Microsoft.Data.Sqlite implementation:

• https://github.com/haf/System.Data.SQLite/blob/master/System.Data.SQLite/SQLiteConnection.cs#L2011
• https://github.com/haf/System.Data.SQLite/blob/master/System.Data.SQLite/SQLiteEnlistment.cs#L186
• https://github.com/haf/System.Data.SQLite/blob/master/System.Data.SQLite/SQLiteTransaction.cs#L121

[AugustZellmer]

If you (or anyone else reading this thread) decides to use System.Data.SQLite instead of Microsoft.Data.Sqlite, keep in mind this bug in the Close() function which fails to release the DB file.

[roji]

Note: this needs to happen after (or along with) pooling: if a connection is closed while the TransactionScope is still active, the connection should be placed in a special "pool", from which it would be taken out if another connection is opened within the same scope. This "optimization" allows us to not need distributed transactions as long as you never have more than one SqliteConnection open at the same time.

[elfalem]

Just wondering if there are any updates on this in the past couple of years?

@dazinator You can inherit SqliteConnection and add TransactionScope support...if you can pass own connection class

I was attempting to prototype an implementation along similar lines. That is, inherit from SqliteConnection and create an implementation of IEnlistmentNotification. However, it looks like I would also need to inherit from SqliteTransaction. That class has an internal constructor that I'm not able to extend. Any possible workarounds for that?