C# 即使在连接关闭后，SQLite 也会保持数据库锁定

Question

提问by Valerio Santinelli

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.

我在 ASP.NET 应用程序（框架 4.0）中使用 System.Data.SQLite 提供程序。我遇到的问题是，当我在 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."

尝试访问该文件时，错误是：“进程无法访问文件 'catalog.sqlite'，因为它正被另一个进程使用。”

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.

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

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.

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

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

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

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.

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

Answer 1

采纳答案by edymtt

I had the same problem using the datasets/tableadapters generated with the designer shipped with System.Data.Sqlite.dllversion 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.

我在使用 1.0.82.0System.Data.Sqlite.dll版附带的设计器生成的数据集/表适配器时遇到了同样的问题——关闭连接后，我们无法使用System.IO.FileStream. 我正确处理了连接和表适配器，并且没有使用连接池。

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

根据我的第一次搜索（例如this和this thread），这似乎是库本身存在的问题——对象没有正确释放和/或池问题（我不使用）。

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 ticketfor System.Data.SQLite, in which a developer explains that "allSQLiteCommand and SQLiteDataReader objects associated with the connection [should be] properly disposed".

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

I then turned back on the generated TableAdapters and I saw that there was no implementation of the Disposemethod -- 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.

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

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.

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

//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 UTCgorogm's answerhighlights two important things:

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

according to the changelogon 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 Disposecall 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).

根据System.Data.SQLite 官方网站上的变更日志，从 1.0.84.0 版开始，不需要上面的代码，因为库会处理这个问题。我没有测试过这个，但在最坏的情况下你只需要这个片段：
```
//In Table Adapter    
protected override void Dispose(bool disposing)
{
  base.Dispose(disposing);

  this.Adapter.Dispose();
}
```
关于Dispose调用的实现TableAdapter：最好将其放在部分类中，以便数据集重新生成不会影响此代码（以及您可能需要添加的任何其他代码）。

Answer 2

回答by klaydze

I have the same problem. My scenario was after getting the data inside SQLite Database file I want to delete that file but it always throw an error "...using by other process". Even I dispose the SqliteConnection or SqliteCommand the error still occur. I've fixed the error by calling GC.Collect().

我也有同样的问题。我的场景是在获取 SQLite 数据库文件中的数据后，我想删除该文件，但它总是抛出一个错误“ ...正在被其他进程使用”。即使我处理 SqliteConnection 或 SqliteCommand 错误仍然发生。我已经通过调用修复了错误GC.Collect()。

Code snippet

代码片段

public void DisposeSQLite()
{
    SQLiteConnection.Dispose();
    SQLiteCommand.Dispose();

    GC.Collect();
}

Hope this help.

希望这有帮助。

Answer 3

回答by Nate

In my case I was creating SQLiteCommandobjects without explicitly disposing them.

在我的情况下，我在SQLiteCommand没有明确处理它们的情况下创建对象。

var command = connection.CreateCommand();
command.CommandText = commandText;
value = command.ExecuteScalar();

I wrapped my command in a usingstatement and it fixed my issue.

我将我的命令包装在一个using语句中，它解决了我的问题。

static public class SqliteExtensions
{
    public static object ExecuteScalar(this SQLiteConnection connection, string commandText)
    {
        // Added using
        using (var command = connection.CreateCommand())
        {
            command.CommandText = commandText;
            return command.ExecuteScalar();
        }
    }
}

Then you can use it like this

然后你可以像这样使用它

connection.ExecuteScalar(commandText);

Answer 4

回答by gorogm

I found edymtt's answer right about blaming TableAdapters / Datasets, but instead of modifying the every time re-generated TableAdapter codefile, I found an other solution: to manually call .Dispose on the TableAdapter's child elements. (In .NET 4.5, latest SQLite 1.0.86)

我发现 edymtt 关于指责 TableAdapters/Datasets 的答案是正确的，但我没有修改每次重新生成的 TableAdapter 代码文件，而是找到了另一个解决方案：在 TableAdapter 的子元素上手动调用 .Dispose 。（在 .NET 4.5 中，最新的 SQLite 1.0.86）

using (var db = new testDataSet())
{
    using (testDataSetTableAdapters.UsersTableAdapter t = new testDataSetTableAdapters.UsersTableAdapter())
    {
        t.Fill(db.Users);
        //One of the following two is enough
        t.Connection.Dispose(); //THIS OR
        t.Adapter.Dispose();    //THIS LINE MAKES THE DB FREE
    }
    Console.WriteLine((from x in db.Users select x.Username).Count());
}

Answer 5

回答by desw

In most cases the problem will arise if you don't dispose your readers and commands properly. There is a scenario in which commands and readers will not dispose properly.

在大多数情况下，如果您没有正确处理读者和命令，就会出现问题。存在命令和读取器无法正确处理的情况。

Scenario 1:In case you are running a booleanfunction. before a result is reached the code in the finally block will not excecute. This is a big problem if you are going to be evaluating the results of function isDataExists while executing code if it suits the result i.e

场景 1：如果您正在运行布尔函数。在到达结果之前，finally 块中的代码不会执行。如果你要在执行代码时评估函数 isDataExists 的结果，如果它适合结果，这是一个大问题

    if(isDataExists){
        // execute some code
    }

The function being evaluated

被评估的函数

    public bool isDataExists(string sql)
    {
        try
        {
            OpenConnection();
            SQLiteCommand cmd = new SQLiteCommand(sql, connection);
            reader = cmd.ExecuteReader();
            if (reader != null && reader.Read())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception expMsg)
        {
            //Exception
        }
        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            CloseConnection();
        }
        return true;
    }

Solution:Dispose your reader and command inside the try block as follows

解决方案：将您的阅读器和命令放在 try 块中，如下所示

            OpenConnection();
            SQLiteCommand cmd = new SQLiteCommand(sql, connection);
            reader = cmd.ExecuteReader();
            if (reader != null && reader.Read())
            {
                cmd.Dispose();
                CloseConnection();
                return true;
            }
            else
            {
                cmd.Dispose();
                CloseConnection();
                return false;
            }

Finallydispose the reader and command just in case some thing went wrong

最后处理 reader 和 command 以防万一出现问题

        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            CloseConnection();
        }

Answer 6

回答by relic120

This was one of the top google results I had found when I ran into this error. However, none of the responses helped me so after more searching around and googling I came up with this code that works from some of the code from http://www.tsjensen.com/blog/post/2012/11/10/SQLite-on-Visual-Studio-with-NuGet-and-Easy-Instructions.aspx

这是我遇到此错误时发现的最重要的谷歌搜索结果之一。然而，没有任何回应帮助我，所以经过更多的搜索和谷歌搜索后，我想出了这段代码，该代码适用于http://www.tsjensen.com/blog/post/2012/11/10/SQLite 的一些代码-on-Visual-Studio-with-NuGet-and-Easy-Instructions.aspx

However, I did not have to use the NuGet at all. What my program does is downloads a db file from a server every time it is opened. Then if a user updates that db, it will be uploaded for everyone to get the next time they open the same program. I was getting the error that the file was in use after updating the local file and trying to upload it to our SharePoint. Now it works fine.

但是，我根本不必使用 NuGet。我的程序所做的是每次打开时从服务器下载一个 db 文件。然后，如果用户更新该数据库，它将被上传给每个人，以便他们下次打开同一程序时获取。在更新本地文件并尝试将其上传到我们的 SharePoint 后，我收到文件正在使用的错误。现在它工作正常。

Public Function sqLiteGetDataTable(sql As String) As DataTable
    Dim dt As New DataTable()
    Using cnn = New SQLiteConnection(dbConnection)
        cnn.Open()
        Using cmd As SQLiteCommand = cnn.CreateCommand()
            cmd.CommandText = sql
            Using reader As System.Data.SQLite.SQLiteDataReader = cmd.ExecuteReader()
                dt.Load(reader)
                reader.Dispose()
            End Using
            cmd.Dispose()
        End Using
        If cnn.State <> System.Data.ConnectionState.Closed Then
            cnn.Close()
        End If
        cnn.Dispose()
    End Using
    Return dt
End Function

Answer 7

回答by Arvin

The following worked for me: MySQLiteConnection.Close(); SQLite.SQLiteConnection.ClearAllPools()

以下对我有用： MySQLiteConnection.Close(); SQLite.SQLiteConnection.ClearAllPools()

Answer 8

回答by pengu1n

Ensuring that any IDisposable (e.g., SQLiteConnection, SQLiteCommand, etc) is properly disposed of solves this problem. I should re-iterate that one must be using "using" as a habit to ensure proper disposing of disposable resources.

确保正确处理任何 IDisposable（例如 SQLiteConnection、SQLiteCommand 等）可以解决此问题。我应该重申，人们必须将“使用”作为一种习惯，以确保妥善处置一次性资源。

Answer 9

回答by Alexander Vasilyev

As said earlier SQLite objects must be destroyed. However, there is a strange behavior: connection must be open during a call Dispose on commands. For example:

如前所述，必须销毁 SQLite 对象。但是，有一个奇怪的行为：在调用 Dispose 命令期间必须打开连接。例如：

using(var connection = new SqliteConnection("source.db"))
{
    connection.Open();
    using(var command = connection.CreateCommand("select..."))
    {
        command.Execute...
    }
}

works fine, but:

工作正常，但是：

using(var connection = new SqliteConnection("source.db"))
{
    connection.Open();
    using(var command = connection.CreateCommand("select..."))
    {
        command.Execute...
        connection.Close();
    }
}

gives the same file lock

给出相同的文件锁

Answer 10

回答by Dominic Jonas

I had the same problem and it was only fixed by disposing the DbCommandin the usingstatement, but with Pooling = truemy problem was fixed!!

我遇到了同样的问题，只能通过DbCommand在using语句中处理来解决，但是Pooling = true我的问题已经解决了！！

                SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
                {
                    Pooling = true
                };

C# 即使在连接关闭后，SQLite 也会保持数据库锁定

提问by Valerio Santinelli

采纳答案by edymtt

回答by klaydze

回答by Nate

回答by gorogm

回答by desw

回答by relic120

回答by Arvin

回答by pengu1n

回答by Alexander Vasilyev

回答by Dominic Jonas

相关推荐

最近更新

标签

C# 即使在连接关闭后，SQLite 也会保持数据库锁定

提问by Valerio Santinelli

采纳答案by edymtt

回答by klaydze

回答by Nate

回答by gorogm

回答by desw

回答by relic120

回答by Arvin

回答by pengu1n

回答by Alexander Vasilyev

回答by Dominic Jonas

相关推荐

C# 显示“请稍候”消息框

C# ssl时如何忽略证书检查

C# 处理 Datarow DBNull 的最佳方法

C# - 使用 Tectia 将文件从 Windows 传输到 unix 时如何删除 ctrl M 字符？

相关推荐

最近更新

标签