TransactionScope and SQLite database gets locked

entity-framework-6 sqlite transactionscope

Question

I am trying to use Entity Framework 6 with SQLite and running into a database locked issue when trying to use TransactionScope. Here is my code:

using (var txn = new TransactionScope())
{
    using (var ctx = new CalibreContext())
    {
        var book = ctx.Books.First(x => x.Id == 2);
        var author = ctx.Authors.First(x => x.Id == 3);
        book.Authors.Add(author);
        ctx.SaveChanges();
    }
    txn.Complete();
}

First line var book = ctx.Books.First(x => x.Id == 2); executes ok. but then once I move on to the next one I get an exception saying that my database is locked. Here is my app config:

<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0" />
  </startup>
  <connectionStrings>
    <add name="CalibreContext" connectionString="Data Source=metadata.db" providerName="System.Data.SQLite.EF6" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite" />
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.98.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6, Version=1.0.98.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <defaultConnectionFactory type="Calibre.Dal.Ef.SQLiteConnectionFactory, Calibre.Dal.Ef" />
    <providers>
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.98.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6, Version=1.0.98.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139" />
    </providers>
  </entityFramework>
</configuration>

I need to use TransactionScope because in addition to performing a DB operation, I also have to perform a file system operation that I plan on adding to the same transaction (currently not there).

1
6
9/22/2015 3:46:11 PM

Popular Answer

I was experiencing a similar issue. Just to be clear, the error I got on the second query was "the underlying provider failed on Open" (but the reason for the Open failure was that the database was locked).

Apparently the issue is related to MSDTC (TransactionScope is tightly coupled to MSDTC).

I found a community addition to an MSDN page which in turn references this blog post
... which states that transactions are "promoted" to MSDTC transactions if a connection is closed and reopened. Which EF does by default. Normally this is a good thing -- you don't want database handles hanging around forever -- but in this case that behavior gets in the way.

The solution is to explicitly open the database connection:

using (var txn = new TransactionScope())
{
    using (var ctx = new CalibreContext())
    {
        ctx.Connection.Open();
        // ... remainder as before ...

Alternatively, if all your CalibreContext objects are short-lived, you could conceivably open the connection in the CalibreContext constructor.

This seems to have fixed my issue. I'll post an update if I have anything else to report.

4
6/24/2016 12:15:30 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow