c# - 数据库文件在 SQLite 提交期间被莫名其妙地锁定

Question

我正在对 SQLite 数据库执行大量插入操作。我只使用一个线程。我批处理写入以提高性能并在发生崩溃时有一点安全性。基本上我在内存中缓存了一堆数据，然后当我认为合适的时候，我遍历所有这些数据并执行 INSERTS。代码如下所示:

    public void Commit()
    {
        using (SQLiteConnection conn = new SQLiteConnection(this.connString))
        {
            conn.Open();
            using (SQLiteTransaction trans = conn.BeginTransaction())
            {
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";

                    command.Parameters.Add(this.col1Param);
                    command.Parameters.Add(this.col2Param);

                    foreach (Data o in this.dataTemp)
                    {
                        this.col1Param.Value = o.Col1Prop;
                        this. col2Param.Value = o.Col2Prop;

                        command.ExecuteNonQuery();
                    }
                }
                this.TryHandleCommit(trans);
            }
            conn.Close();
        }
    }

我现在采用以下技巧来使它最终起作用:

    private void TryHandleCommit(SQLiteTransaction trans)
    {
        try
        {
            trans.Commit();
        }
        catch (Exception e)
        {
            Console.WriteLine("Trying again...");
            this.TryHandleCommit(trans);
        }
    }

我这样创建我的数据库:

    public DataBase(String path)
    {
        //build connection string
        SQLiteConnectionStringBuilder connString = new SQLiteConnectionStringBuilder();
        connString.DataSource = path;
        connString.Version = 3;
        connString.DefaultTimeout = 5;
        connString.JournalMode = SQLiteJournalModeEnum.Persist;
        connString.UseUTF16Encoding = true;

        using (connection = new SQLiteConnection(connString.ToString()))
        {
            //check for existence of db
            FileInfo f = new FileInfo(path);

            if (!f.Exists)  //build new blank db
            {
                SQLiteConnection.CreateFile(path);
                connection.Open();

                using (SQLiteTransaction trans = connection.BeginTransaction())
                {
                    using (SQLiteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = DataBase.CREATE_MATCHES;
                        command.ExecuteNonQuery();

                        command.CommandText = DataBase.CREATE_STRING_DATA;
                        command.ExecuteNonQuery();
                        //TODO add logging
                    }
                    trans.Commit();
                }
                connection.Close();
            }
        }            
    }

然后我导出连接字符串并使用它在程序的不同部分获取新连接。

我收到未处理的 SQLiteException:数据库文件已锁定，看似随机的时间间隔，但速度太快以至于无法忽略或以其他方式解决此问题。当我尝试提交事务时会发生这种情况。在此之前似乎没有发生任何错误。这不会总是发生。有时整件事运行顺利。

• 在提交完成之前没有对这些文件执行任何读取。
• 我有最新的 SQLite 二进制文件。
• 我正在为 .NET 2.0 进行编译。
• 我正在使用 VS 2008。
• 数据库是一个本地文件。
• 所有这些事件都封装在一个线程/进程中。
• 病毒防护已关闭(尽管我认为这仅在您通过网络连接时才有意义？)。
• 根据 Scotsman 的帖子，我实现了以下更改:
• 日志模式设置为持续
• 通过 System.Windows.Forms.Application.AppData windows 调用存储在 C:\Docs + Settings\ApplicationData 中的数据库文件
• 没有内部异常
• 见证了两台不同的机器(尽管硬件和软件非常相似)
• 一直在运行 Process Monitor - 没有无关的进程将自身附加到 DB 文件 - 问题肯定出在我的代码中...

有人知道这里发生了什么吗？

我知道我刚刚遗漏了一大堆代码，但我已经尝试解决这个问题太久了。感谢所有完成此问题的人!

布莱恩

更新:

感谢到目前为止的建议!我已经实现了许多建议的更改。我觉得我们离答案越来越近了……然而……

上面的代码在技术上是可行的，但它是不确定的!除了永远在空档旋转之外，不能保证做任何事情。在实践中，它似乎在第 1 次和第 10 次迭代之间起作用。如果我以合理的间隔批处理我的提交，将会减轻损害，但我真的不想让事情处于这种状态......

欢迎更多建议!

Answer 1

看起来您未能将命令与您创建的交易相关联。而不是:

using (SQLiteCommand command = conn.CreateCommand())

你应该使用:

using (SQLiteCommand command = new SQLiteCommand("<INSERT statement here>", conn, trans))

或者您可以在构造后设置它的 Transaction 属性。

虽然我们正在处理 - 您对故障的处理不正确:

命令的 ExecuteNonQuery 方法也可能失败，您并没有真正受到保护。您应该将代码更改为:

   public void Commit()
    {
        using (SQLiteConnection conn = new SQLiteConnection(this.connString))
        {
            conn.Open();
            SQLiteTransaction trans = conn.BeginTransaction();
            try
            {
                using (SQLiteCommand command = conn.CreateCommand())
                {
                    command.Transaction = trans; // Now the command is linked to the transaction and don't try to create a new one (which is probably why your database gets locked)
                    command.CommandText = "INSERT OR IGNORE INTO [MY_TABLE] (col1, col2) VALUES (?,?)";

                    command.Parameters.Add(this.col1Param);
                    command.Parameters.Add(this.col2Param);

                    foreach (Data o in this.dataTemp)
                    {
                        this.col1Param.Value = o.Col1Prop;
                        this. col2Param.Value = o.Col2Prop;

                        command.ExecuteNonQuery();
                    }
                }

                trans.Commit();
            }
            catch (SQLiteException ex)
            {
                // You need to rollback in case something wrong happened in command.ExecuteNonQuery() ...
                trans.Rollback();
                throw;
            }
        }
    }

另一件事是你不需要在内存中缓存任何东西。您可以依靠 SQLite 日志机制来存储不完整的事务状态。