TransactionScope和SQLite数据库被锁定 [英] TransactionScope and SQLite database gets locked

问题描述

我试图使用SQLite使用Entity Framework 6，并在尝试使用 TransactionScope 时遇到数据库锁定问题。这是我的代码：

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();
}

第一行 var book = ctx.Books.First （x => x.Id == 2）; 执行ok。但是一旦我转到下一个我收到一个例外，说我的数据库被锁定。这是我的应用配置：

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>

我需要使用 TransactionScope 要执行数据库操作，我还必须执行一个文件系统操作，我计划添加到同一个事务中（目前不存在）。

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).

推荐答案

我遇到类似的问题。为了清楚起见，第二个查询中出现的错误是打开的底层提供程序失败（但是打开失败的原因是数据库被锁定）。

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).

显然问题与MSDTC（ TransactionScope 紧密耦合到MSDTC）有关。

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

我发现<一个href =https://msdn.microsoft.com/en-us/library/bb738523(v=vs.100).aspx =nofollow> MSDN页面的社区，其中转到参考文献此博文

...其中指出，如果连接被关闭并重新打开，交易将被升级到MSDTC交易。哪个EF默认情况下。通常这是一件好事 - 你不希望数据库句柄永远在一起 - 但在这种情况下，行为就会阻碍。

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 ...

或者，如果所有的 CalibreContext 对象是短暂的，您可以想象地在 CalibreContext 构造函数中打开连接。

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.

这篇关于TransactionScope和SQLite数据库被锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！