SQLite 数据库锁定异常 [英] SQLite Database Locked exception

问题描述

我仅针对某些查询从 SQLite 收到 数据库被锁定 异常.

I am getting Database is locked exception from SQLite for some queries only.

下面是我的代码:当我执行任何选择语句时，它工作正常.
当我在 Jobs 表上执行任何写入语句时，它也能正常工作.

Below is my code: When I execute any select statement it works fine.
When I am executing any write statement on Jobs Table it also works fine.

这很好用:

ExecuteNonQuery("DELETE FROM Jobs WHERE id=1");

但同样的，如果我对 Employees 表执行查询，它会抛出 数据库被锁定的异常.
这会引发异常:

But the same way if I am executing queries for Employees table it is throwing an exception that database is locked.
This throws Exception:

ExecuteNonQuery("DELETE FROM Employees WHERE id=1");

以下是我的功能:

public bool OpenConnection()
{
    if (Con == null)
    {
        Con = new SQLiteConnection(ConnectionString);
    }
    if (Con.State == ConnectionState.Closed)
    {
        Con.Open();
        //Cmd = new SQLiteCommand("PRAGMA FOREIGN_KEYS=ON", Con);
        //Cmd.ExecuteNonQuery();
        //Cmd.Dispose();
        //Cmd=null;
        return true;
    }
    if (IsConnectionBusy())
    {
        Msg.Log(new Exception("Connection busy"));
    }
    return false;
}

public Boolean CloseConnection()
{
    if (Con != null && Con.State == ConnectionState.Open)
    {
        if (Cmd != null) Cmd.Dispose();
        Cmd = null;
        Con.Close();
        return true;
    }

    return false;
}

public Boolean ExecuteNonQuery(string sql)
{
    if (sql == null) return false;
    try
    {
        if (!OpenConnection())
            return false;
        else
        {
            //Tx = Con.BeginTransaction(IsolationLevel.ReadCommitted);
            Cmd = new SQLiteCommand(sql, Con);
            Cmd.ExecuteNonQuery();
            //Tx.Commit();
            return true;
        }
    }
    catch (Exception exception)
    {
        //Tx.Rollback();
        Msg.Log(exception);
        return false;
    }
    finally
    {
        CloseConnection();
    }
}

这是例外:在第 103 行:Cmd.ExecuteNonQuery();

This is the Exception: At line 103 : Cmd.ExecuteNonQuery();

发现异常:类型:System.Data.SQLite.SQLiteException消息:数据库被锁定数据库被锁定来源:System.Data.SQLite

Exception Found: Type: System.Data.SQLite.SQLiteException Message: database is locked database is locked Source: System.Data.SQLite

堆栈跟踪:在 System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)在 System.Data.SQLite.SQLiteDataReader.NextResult()在 System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior 行为)在 System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior 行为)在 System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()在 d:ProjectsC# ApplicationsCompleted ProjectsTimeSheet6TimeSheet6DbOp.cs:line 103

Stacktrace: 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 TimeSheet6.DbOp.ExecuteNonQuery(String sql) in d:ProjectsC# ApplicationsCompleted ProjectsTimeSheet6TimeSheet6DbOp.cs:line 103

推荐答案

连接过程中的某个地方一直处于打开状态.去掉 OpenConnection 和 CloseConnection 并将 ExecuteNonQuery 改为:

Somewhere along the way a connection is getting left open. Get rid of OpenConnection and CloseConnection and change ExecuteNonQuery to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        cmd.ExecuteNonQuery();
    }
}

此外，将您读取数据的方式更改为:

Further, change the way you read data to this:

using (SQLiteConnection c = new SQLiteConnection(ConnectionString))
{
    c.Open();
    using (SQLiteCommand cmd = new SQLiteCommand(sql, c))
    {
        using (SQLiteDataReader rdr = cmd.ExecuteReader())
        {
            ...
        }
    }
}

请勿尝试像您在这里一样自行管理连接池.首先，它比您编写的代码复杂得多，但其次，它已经在 SQLiteConnection 对象中处理了.最后，如果您没有利用using，您就没有没有正确处理这些对象，最终会遇到类似您现在所看到的问题.

Do not attempt, to manage connection pooling on your own like you are here. First, it's much more complex than what you have coded, but second, it's handled already inside the SQLiteConnection object. Finally, if you're not leveraging using, you're not disposing these objects properly and you end up with issues like what you're seeing now.

这篇关于SQLite 数据库锁定异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！