在单线程上使用事务时 sqlite 和 SubSonic 的锁定问题

Question

当尝试使用 SubSonic 和 SQLite 的事务时，我遇到锁定异常。我从单个线程使用它，并且没有其他进程访问我的数据库，所以我真的没想到会出现任何此类问题。

如果我编写如下代码，我会在循环内第二次调用 Save() 时遇到异常 - 因此第三次调用 Save() 时会出现异常。

       using (TransactionScope ts = new TransactionScope())
       {
            using (SharedDbConnectionScope sharedConnectinScope = new SharedDbConnectionScope())
            { 
                SomeDALObject x = new SomeDALObject()
                x.Property1 = "blah";
                x.Property2 = "blah blah";
                x.Save();

                foreach (KeyValuePair<string, string> attribute in attributes)
                { 
                    AnotherDALObject y = new AnotherDALObject()
                    y.Property1 = attribute.Key
                    y.Property2 = attribute.Value
               
                    y.Save();  // this is where the exception is raised, on the 2nd time through this loop
                }
            }
       }

如果我有如上所述的 using() 语句，或者如果我只有 using (TransactionScope ts = new TransactionScope()) 那么我会得到一个 System.Data.SQLite.SQLiteException 有留言

数据库文件被锁定

数据库已锁定

堆栈跟踪是：

   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   at System.Data.SQLite.SQLiteTransaction..ctor(SQLiteConnection connection, Boolean deferredLock)
   at System.Data.SQLite.SQLiteConnection.BeginDbTransaction(IsolationLevel isolationLevel)
   at System.Data.SQLite.SQLiteConnection.BeginTransaction()
   at System.Data.SQLite.SQLiteEnlistment..ctor(SQLiteConnection cnn, Transaction scope)
   at System.Data.SQLite.SQLiteConnection.EnlistTransaction(Transaction transaction)
   at System.Data.SQLite.SQLiteConnection.Open()
   at SubSonic.SQLiteDataProvider.CreateConnection(String newConnectionString)
   at SubSonic.SQLiteDataProvider.CreateConnection()
   at SubSonic.SQLiteDataProvider.ExecuteScalar(QueryCommand qry)
   at SubSonic.DataService.ExecuteScalar(QueryCommand cmd)
   at SubSonic.ActiveRecord`1.Save(String userName)
   at SubSonic.ActiveRecord`1.Save()
   at (my line of code above).

如果我以相反的方式嵌套了 using 语句，SharedDbConnectionScope 位于外部，那么我会收到一个带有消息“操作不是”的 TransactionException对交易状态有效。”堆栈跟踪是：

at System.Transactions.TransactionState.EnlistVolatile(InternalTransaction tx, IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions, Transaction atomicTransaction)
   at System.Transactions.Transaction.EnlistVolatile(IEnlistmentNotification enlistmentNotification, EnlistmentOptions enlistmentOptions)
   at System.Data.SQLite.SQLiteEnlistment..ctor(SQLiteConnection cnn, Transaction scope)
   at System.Data.SQLite.SQLiteConnection.EnlistTransaction(Transaction transaction)
   at System.Data.SQLite.SQLiteConnection.Open()
   at SubSonic.SQLiteDataProvider.CreateConnection(String newConnectionString)
   at SubSonic.SQLiteDataProvider.CreateConnection()
   at SubSonic.SQLiteDataProvider.ExecuteScalar(QueryCommand qry)
   at SubSonic.DataService.ExecuteScalar(QueryCommand cmd)
   at SubSonic.ActiveRecord`1.Save(String userName)
   at SubSonic.ActiveRecord`1.Save()
   at (my line of code above)

内部异常是“事务超时”

我生成的 DAL 类中没有任何自定义代码，或者我能想到的任何其他聪明的东西会导致此问题。

其他人遇到过这样的交易问题，或者有人可以建议我从哪里开始寻找问题？

谢谢！

更新：我注意到版本 1.0.61-65 的发行说明中提到了与事务相关的内容（例如 此处），因此更新 SubSonic 以使用最新版本的 .Net 数据提供程序可能会解决其中一些问题...

Answer 1

在为 subsonic 2.x 修改 sqlite 提供程序时，我基于现有的 subsonic sqlserver 测试创建了一整套单元测试。 （这些测试也通过修订后的代码进行了检查。）唯一失败的测试是与事务相关的测试（也许还有迁移测试）。正如您所看到的，“数据库文件已锁定”错误消息。 Subsonic 主要是为 sql server 编写的，它不像 SQLite 那样进行文件级锁定，因此有些功能不起作用；需要重写才能更好地处理这个问题。

我从来没有像你一样使用过 TransactionScope。我像这样进行亚音速 2.2 事务，到目前为止 SQLite 提供程序没有出现任何问题。我可以确认，如果处理多行，您需要使用 SQLite 事务，否则速度非常慢。

public void DeleteStuff(List<Stuff> piaRemoves)
{
    QueryCommandCollection qcc = new QueryCommandCollection();

    foreach(Stuff item in piaRemoves)
    {
        Query qry1 = new Query(Stuff.Schema);
        qry1.QueryType = QueryType.Delete;
        qry1.AddWhere(Stuff.Columns.ItemID, item.ItemID);
        qry1.AddWhere(Stuff.Columns.ColumnID, item.ColumnID);
        qry1.AddWhere(Stuff.Columns.ParentID, item.ParentID);
        QueryCommand cmd = qry1.BuildDeleteCommand();
        qcc.Add(cmd);
    }
    DataService.ExecuteTransaction(qcc);
}

Answer 2

我最终使用了 Paul 的建议，并将我的代码重写为如下所示：

    QueryCommandCollection qcc = new QueryCommandCollection();

    SomeDALObject x = new SomeDALObject()
    x.Property1 = "blah";
    x.Property2 = "blah blah";
    qcc.Add(x.GetSaveCommand());

    foreach (KeyValuePair<string, string> attribute in attributes)
    { 
        AnotherDALObject y = new AnotherDALObject()
        y.Property1 = attribute.Key
        y.Property2 = attribute.Value

        qcc.Add(y.GetSaveCommand());
    }

    DataService.ExecuteTransaction(qcc);

这实际上要好得多，因为数据库命中的所有准备工作都是在事务打开之前完成的，因此事务打开的时间会少得多。

如果您需要取回自动生成的 ID 以便对子记录运行 INSERT，则此方法效果不太好；您需要为此使用不同的方法。

然后我遇到了一些其他线程/事务问题：当我有多个线程同时执行 DataService.ExecuteTransaction() 时，我会得到 AccessViolationExceptions 和 NullReferenceExceptions，基本上有点混乱。但更改为使用 Paul 的 SubSonic 分支 以及更新的 SQLDataProvider 并更改为使用 < a href="http://sourceforge.net/projects/sqlite-dotnet2/" rel="nofollow noreferrer">System.Data.SQLite v1.0.65.0 似乎立即修复了它。万岁！

更新：实际上，我在使用 SubSonic 和 sqlite 时仍然遇到线程问题。基本上 SubSonic 中的 SQLiteDataProvider 并不是为处理多线程而编写的。更多内容即将推出...

Answer 3

我们使用 SQL lite 来测试与数据库操作相关的代码。
SQL lite 不支持嵌套事务。
我们在 NHibernate 和 .Net 事务中也遇到了类似的问题。
最终我们不得不使用 SQL Express 来测试数据库相关代码。