SQLite Database Locked exception

Question

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

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.

This works fine:

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

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");

Below are my functions:

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();
    }
}

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

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

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:\Projects\C# Applications\Completed Projects\TimeSheet6\TimeSheet6\DbOp.cs:line 103

Answer 1

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())
        {
            ...
        }
    }
}

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.

Answer 2

You can use 'using' statement as below, that will make sure connection & command disposed correctly even in exception

private static void ExecuteNonQuery(string queryString)
{
    using (var connection = new SQLiteConnection(
               ConnectionString))
    {
        using (var command = new SQLiteCommand(queryString, connection))
        {
            command.Connection.Open();
            command.ExecuteNonQuery();
        }
    }
}

Answer 3

You should close your DataReader before attempting to write any data to the database. Use:

dr.Close();

after you finish using the DataReader.

Answer 4

In my case it was very stupid of me, I was making changes in SQLite browser and did not click on write changes, which locked the DB to be modified by the services. After I clicked the Write changes button, all the post request worked as expected.

Answer 5

I was also getting the same error here:

if (new basics.HindiMessageBox(HMsg, HTitle).ShowDialog()==true)
{
    SQLiteConnection m_dbConnection = new SQLiteConnection(MainWindow.con);
    m_dbConnection.Open();
    sql = "DELETE FROM `users`  WHERE `id`=" + SelectedUser.Id;
    command = new SQLiteCommand(sql, m_dbConnection);
    command.ExecuteNonQuery();
    m_dbConnection.Close();
    LoadUserDG();
}

but when I just changed SQLiteConnection declaration location

public partial class User : Window
{
    SQLiteCommand command;
    string sql;
    AddUser AddUserObj;
    List<basics.users> usersList;
    basics.users SelectedUser;
    SQLiteConnection m_dbConnection;

    // ...

    private void DeleteBtn_Click(object sender, RoutedEventArgs e)
    {
        // ...
        if (new basics.HindiMessageBox(HMsg, HTitle).ShowDialog()==true)
        {
            m_dbConnection = new SQLiteConnection(MainWindow.con);
            m_dbConnection.Open();
            sql = "DELETE FROM `users`  WHERE `id`=" + SelectedUser.Id;
            command = new SQLiteCommand(sql, m_dbConnection);
            command.ExecuteNonQuery();
            m_dbConnection.Close();
            LoadUserDG();
        }
}

Everything is fine now. I hope this may work for you, too. If someone can say how this happened, I would like to know the details to improve my knowledge, please.

Answer 6

A lot of helpful posts here for folks that may have forgotten to clean up a dangling connection, but there is another way this can happen: SQLite does not support concurrent INSERTs; if you issue two INSERTs at the same time the will be processed in serial. When the INSERTs are quick this is fine, but if an INSERT takes longer than the timeout the second INSERT can fail with this message.

I had this happen when I used a long running transaction to accumulate a bunch of INSERTs into one big commit. Basically I locked the database from any other activity during the transaction. Switching to journal_mode=WAL will allow concurrent writes and reads, but not concurrent writes.

I got rid of the long running transaction and let each INSERT autocommit, and that solved my problem.