我收到试图用交易亚音速和SQLite时锁定异常。我用这从一个单独的线程，并有访问我的数据库没有其他进程，所以我真的没有想到任何这样的问题。

I'm getting locking exceptions when trying to use transactions with SubSonic and SQLite. I'm using this from a single thread and there are no other processes accessing my db, so I really didn't expect any such problems.

如果我写code这样的下面，我得到了第二次调用异常保存（）内环路 - 所以第三个调用保存（）对所有

If I write code like this below, I get an exception on the second call to Save() within the loop - so the third call to Save() over all.

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

如果我有使用（）语句如上，如果我只是有使用（TransactionScope的TS =新的TransactionScope（））然后我得到一个 System.Data.SQLite.SQLiteException 有消息

If I have the using() statements as above, or if I just have using (TransactionScope ts = new TransactionScope()) then I get a System.Data.SQLite.SQLiteException with message

该数据库文件被锁定

数据库被锁定

堆栈跟踪是：

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

如果我有使用statments嵌套的其他方式，与SharedDbConnectionScope在外面，然后我得到一个 TransactionException 与消息的操作是无效的状态该交易。堆栈跟踪：

If I have the using statments nested the other way around, with SharedDbConnectionScope on the outside, then I get a TransactionException with message "The operation is not valid for the state of the transaction." Stack trace is:

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)

和内部异常是交易超时

我没有任何自定义code在我的生成DAL类，或其他任何聪明的，我能想到的，将导致此。

I don't have any custom code in my generated DAL classes, or anything else clever that I can think of that would be causing this.

这样的或其他任何人遇到的问题，交易可有人建议在那里我开始寻找这个问题？

Anyone else encountered transaction problems like this or can someone suggest where I start looking for the problem?

谢谢！

更新：我注意到提到交易相关的东西的版本1.0.61-65发行说明（如的），所以也许更新亚音速与.NET数据提供程序的最新版本的工作将解决其中的一些问题...

UPDATE: I notice mention of transaction-related things in the release notes for versions 1.0.61-65 (e.g. here), so perhaps updating SubSonic to work with the latest version of the .Net Data Provider would solve some of these issues...

推荐答案

在做了修改后的源码供应商亚音速2.x的我创建了一整套基于现有的亚音速SQLSERVER测试单元测试。 （这些测试还检查与修改后的code）失败的唯一测试（也可能迁移的）与交易相关的那些人。 该数据库文件被锁定的错误信息，因为你所看到的。亚音速写主要是为SQL服务器没有做文件级锁定像SQLite的呢，所以有些事情不工作;那就需要重新编写，以处理这更好的。

In doing the revised sqlite provider for subsonic 2.x I created a full set of unit tests based on the existing subsonic sqlserver tests. (These tests were also checked in with the revised code.) The only tests that failed were the ones related to transactions (maybe the migration ones too). "The database file is locked" error message, as you have seen. Subsonic was written mainly for sql server that doesn't do file level locking like SQLIte does, so some things don't work; it would need to be rewritten to handle this better.

我从来没有使用过的TransactionScope的你有。我做我的亚音速2.2的交易是这样，到目前为止，没有问题的SQLite的供应商。我可以确认，你需要的，如果处理多行使用的SQLite交易或它的很慢。

I have never used the TransactionScope as you have. I do my subsonic 2.2 transactions like this, and so far no problems with the SQLite provider. I can confirm that you need to use transactions with SQLite if dealing with multiple rows or it's really slow.

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