即使在连接关闭后，SQLite 也会保持数据库锁定 [英] SQLite keeps the database locked even after the connection is closed

问题描述

我在 ASP.NET 应用程序(框架 4.0)中使用 System.Data.SQLite 提供程序.我遇到的问题是，当我在 SQLite 数据库的表中插入某些内容时，数据库被锁定并且即使在连接被释放后也不会释放锁定.

尝试访问文件时，错误是:进程无法访问文件‘catalog.sqlite’，因为它正被另一个进程使用."

我的代码非常简单，我打开连接，从 SQLServer 数据库读取一些数据，将该数据插入 SQLite(通过 SQLiteDataAdapter)，然后关闭连接并处理所有内容，只是为了安全起见.但是，当我在用数据填充文件后尝试压缩文件时，我仍然收到该错误.

我已经阅读了 StackOverflow 上的所有建议，但都没有帮助解决问题(关闭防病毒软件，更改事务模型，在压缩文件之前等待几秒钟，将所有插入调用包装到交易等.但都没有帮助解决这个问题.

也许 ASP.NET 有一些特定的东西(多线程是问题?即使我在开发机器上测试它，那里只有一个对该函数的调用并且没有并发?)

作为旁注，我尝试避免使用 DataTable 和 SQLiteDataAdapter 并仅直接使用 SQLiteCommand，这样它就很有魅力.当然，我可以继续将我的查询构建为字符串，而不是使用数据适配器，但是当有一个框架可以做到这一点时，我觉得有点尴尬.

解决方案

我在使用 System.Data.Sqlite.dll 1.0 版附带的设计器生成的数据集/表适配器时遇到了同样的问题.82.0 -- 关闭连接后，我们无法使用 System.IO.FileStream 读取数据库文件.我正确地处理了连接和表适配器，但我没有使用连接池.

根据我的第一次搜索(例如 this 和 this thread) 这在库本身——对象未正确释放和/或池化问题(我不使用).

阅读您的问题后，我尝试仅使用 SQLiteCommand 对象来复制问题，我发现当您不处理它们时就会出现问题.更新 2012-11-27 19:37 UTC:这张票，其中一位开发人员解释说所有与连接相关联的 SQLiteCommand 和 SQLiteDataReader 对象[应该] 正确处置".>

然后我重新打开生成的 TableAdapter，我看到没有实现 Dispose 方法——所以实际上创建的命令没有被释放.我实现了它，负责处理所有命令，我没有问题.

这是 C# 中的代码，希望对您有所帮助.请注意，该代码是从 Visual Basic 中的原始代码 转换而来的，因此可能会出现一些转换错误.

//表内适配器受保护的覆盖无效处置(布尔处置){base.Dispose(处置)；Common.DisposeTableAdapter(disposing, _adapter, _commandCollection);}公共静态类 Common{///<总结>///处理SQLite Designer生成的TableAdapter///</总结>///<param name="disposing"></param>///<param name="适配器"></param>///<param name="commandCollection"></param>///<remarks>你必须处理所有的命令，///否则文件保持锁定状态，无法访问///(例如，用于读取或删除)</remarks>public static void DisposeTableAdapter(布尔处理，System.Data.SQLite.SQLiteDataAdapter 适配器，IEnumerable命令集合){如果(处置){DisposeSQLiteTableAdapter(适配器)；foreach(commandCollection 中的对象 currentCommand_loopVariable){currentCommand = currentCommand_loopVariable;currentCommand.Dispose();}}}public static void DisposeSQLiteTableAdapter(System.Data.SQLite.SQLiteDataAdapter 适配器){如果(适配器！= null){DisposeSQLiteTableAdapterCommands(适配器);适配器处理()；}}public static void DisposeSQLiteTableAdapterCommands(System.Data.SQLite.SQLiteDataAdapter 适配器){foreach (对象 currentCommand_loopVariable 在 {适配器.更新命令，适配器.插入命令，适配器.删除命令，适配器.SelectCommand}){currentCommand = currentCommand_loopVariable;如果(当前命令！= null){currentCommand.Dispose();}}}}

更新 2013-07-05 17:36 UTC gorogm 的回答 强调了两个重要的事情:

• 根据更改日志 在 System.Data.SQLite 的官方网站上，从 1.0.84.0 版本开始，不需要上面的代码，因为库会处理这个问题.我没有测试过这个，但在最坏的情况下你只需要这个片段:

  //表内适配器受保护的覆盖无效处置(布尔处置){base.Dispose(处置)；this.Adapter.Dispose();}

• 关于TableAdapter的Dispose调用的实现:最好把它放在一个partial类中，这样数据集再生就不会影响了此代码(以及您可能需要添加的任何其他代码).

I'm using System.Data.SQLite provider in an ASP.NET application (framework 4.0). The issue I'm running into is that when I INSERT something in a table in the SQLite database, the database gets locked and the lock isn't being released even after the connection is disposed.

When trying to access the file, the error is: "The process cannot access the file 'catalog.sqlite' because it is being used by another process."

My code is pretty straightforward, I open the connection, read some data from a SQLServer database, insert that data into SQLite (through SQLiteDataAdapter) and then close the connection and dispose everything just to be on the safe side. But still, I get that error when I try to zip the file after it's being populated with the data.

I've read all kind of suggestions here on StackOverflow but none of them has helped solving the problem (turning off the antivirus, changing the transaction model, waiting a few seconds before zipping up the file, wrapping all the insert calls into a transaction, etc.. but none has helped solving this issue.

Maybe there's something specific to ASP.NET (multithreading being the issue? Even though I'm testing it on a development machine where there's only one call to that function and no concurrency?)

As a side note, I tried avoiding DataTable and SQLiteDataAdapter and using only SQLiteCommand directly and that way it works a charm. Of course I can keep building my queries as strings instead of using the data adapters, but I kind of find it a bit awkward when there's a framework built to do that.

解决方案

I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dll version 1.0.82.0 -- after closing the connection we were unable to read the database file using System.IO.FileStream. I was disposing correctly both connection and tableadapters and I was not using connection pooling.

According to my first searches (for example this and this thread) that seemed a problem in the library itself -- either objects not correctly released and/or pooling issues (which I don't use).

After reading your question I tried to replicate the problem using only SQLiteCommand objects and I found that the problem arises when you don't dispose them. Update 2012-11-27 19:37 UTC: this is further confirmed by this ticket for System.Data.SQLite, in which a developer explains that "all SQLiteCommand and SQLiteDataReader objects associated with the connection [should be] properly disposed".

I then turned back on the generated TableAdapters and I saw that there was no implementation of the Dispose method -- so in fact the created commands were not disposed. I implemented it, taking care of disposing all the commands, and I have got no problem.

Here's the code in C#, hope this helps. Please note that the code is converted from the original in Visual Basic, so expect some conversion errors.

//In Table Adapter    
protected override void Dispose(bool disposing)
{
   base.Dispose(disposing);

    Common.DisposeTableAdapter(disposing, _adapter, _commandCollection);
}

public static class Common
{
    /// <summary>
    /// Disposes a TableAdapter generated by SQLite Designer
    /// </summary>
    /// <param name="disposing"></param>
    /// <param name="adapter"></param>
    /// <param name="commandCollection"></param>
    /// <remarks>You must dispose all the command,
    /// otherwise the file remains locked and cannot be accessed
    /// (for example, for reading or deletion)</remarks>
    public static void DisposeTableAdapter(
        bool disposing,
        System.Data.SQLite.SQLiteDataAdapter adapter,
        IEnumerable<System.Data.SQLite.SQLiteCommand> commandCollection)
    {
        if (disposing) {
            DisposeSQLiteTableAdapter(adapter);

            foreach (object currentCommand_loopVariable in commandCollection)
            {
                currentCommand = currentCommand_loopVariable;
                currentCommand.Dispose();
            }
        }
    }

    public static void DisposeSQLiteTableAdapter(
            System.Data.SQLite.SQLiteDataAdapter adapter)
    {
        if (adapter != null) {
            DisposeSQLiteTableAdapterCommands(adapter);

            adapter.Dispose();
        }
    }

    public static void DisposeSQLiteTableAdapterCommands(
            System.Data.SQLite.SQLiteDataAdapter adapter)
    {
        foreach (object currentCommand_loopVariable in {
            adapter.UpdateCommand,
            adapter.InsertCommand,
            adapter.DeleteCommand,
            adapter.SelectCommand})
        {
            currentCommand = currentCommand_loopVariable;
            if (currentCommand != null) {
                currentCommand.Dispose();
            }
        }
    }
}

Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:

• according to the changelog on the official site of System.Data.SQLite, starting from version 1.0.84.0 the above code should not be needed, since the library takes care of this. I haven't tested this, but in the worst case you only need this snippet:

  //In Table Adapter    
  protected override void Dispose(bool disposing)
  {
    base.Dispose(disposing);
  
    this.Adapter.Dispose();
  }
  

• about the implementation of the Dispose call of the TableAdapter: it is is better to put this in a partial class, so that a dataset regeneration does not affected this code (and any additional code you may need to add).

这篇关于即使在连接关闭后，SQLite 也会保持数据库锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！