SQLite的保持甚至锁定数据库的连接关闭后 [英] SQLite keeps the database locked even after the connection is closed
问题描述
我使用System.Data.SQLite提供者在ASP.NET应用程序（框架4.0）。
我遇到的问题是，当我在SQLite数据库表中插入一些东西，数据库被锁定，连接配置即使在锁没有被释放。
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.
当试图访问该文件，错误是：该进程无法访问该文件'catalog.sqlite'，因为它正被另一个进程使用。
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."
我的code为pretty简单，我打开连接，从一个SQLServer数据库中读取一些数据，这些数据插入到SQLite的（通过SQLiteDataAdapter），然后关闭连接和处理一切都只是要上安全方面。但是，我得到这个错误，当我尝试压缩文件它正在与填充数据后。
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.
也许有一些具体的事情到ASP.NET（多线程是问题？即使我在开发机器那里只有一个针对该功能没有并发通话测试它？）
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?)
作为一个方面说明，我试图避免的DataTable和SQLiteDataAdapter只使用SQLiteCommand直接和它的工作方式一个魅力。当然，我可以保持我的建设作为查询字符串，而不是使用数据适配器，但我有点觉得有点尴尬时，有内置做到这一点的框架。
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.
推荐答案
我不得不使用带有附带设计器生成数据集/ TableAdapter的同样的问题 System.Data.Sqlite.dll 1.0.82.0版本 - 关闭我们无法读取使用 System.IO.FileStream 数据库文件的连接之后。我是正确处理两者的连接和TableAdapter的，我并没有使用连接池。
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.
据我的第一个搜索（例如<一个href=\"http://stackoverflow.com/questions/5994141/checking-if-table-exists-keeping-connection-open-in-sqlite\">this和<一个href=\"http://stackoverflow.com/questions/8511901/system-data-sqlite-close-not-releasing-database-file\">this这似乎在图书馆本身的问题线程） - 无论是对象不正确地释放和/或池的问题（我不使用）
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).
阅读您的问题后，我试图复制使用问题只SQLiteCommand对象，我发现，问题就出现了，当你不处理它们。 更新2012年11月27日19:37 UTC ：这是由
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".
我再重新打开生成的TableAdapter的，我看到，没有了的Dispose 方法的实现 - 所以其实创建的命令并未处理。我实现它，以处理所有的命令照顾，和我有没有问题。
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.
下面是在C＃中code，希望这有助于。请注意，code都在Visual Basic中中的原来的转换，所以我们期待一些转换错误。
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();
}
}
}
}
更新2013年7月5日17:36 UTC gorogm的回答突出了两个重要的事情
Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:
-
根据的changelog 在官方网站System.Data.SQLite，从版本1.0.84.0上述code首发应该没有必要，因为库会处理这件事。我没有测试过这一点，但在最坏的情况下，你只需要这个片段：
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();
}
有关的的TableAdapter 的的Dispose 调用的实现：它是更好地把这个在部分类，让数据恢复不影响本code（以及任何额外的code，你可能需要添加）。
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屋！
