SQLite Database Locked exception

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

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.

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

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

dr.Close();

after you finish using the DataReader.

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.

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.

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.

I had the same issue when loading a lot of data to different tables from multiple threads. When trying to do the inserts I was getting database locked because the program was doing too many insert too fast and SQLite didn't have time to complete each transaction before another one came.

The insert are done through threading because I didn't want the interface to be locked and wait for the insert to be done.

My solution is to use BlockingCollection with ThreadPool.QueueUserWorkItem. This allows me to free the interface while doing the inserts. All the insert are queued and executed in FIFO (First In First Out) order. Now the database is never locked while doing any SQL transaction from any thread.

public class DatabaseQueueBus
{
    private BlockingCollection<TransportBean> _dbQueueBus = new BlockingCollection<TransportBean>(new ConcurrentQueue<TransportBean>());
    private CancellationTokenSource __dbQueueBusCancelToken;

    public CancellationTokenSource _dbQueueBusCancelToken { get => __dbQueueBusCancelToken; set => __dbQueueBusCancelToken = value; }

    public DatabaseQueueBus()
    {
        _dbQueueBusCancelToken = new CancellationTokenSource();
        DatabaseQueue();

    }

    public void AddJob(TransportBean dto)
    {
        _dbQueueBus.Add(dto);
    }

    private void DatabaseQueue()
    {

        ThreadPool.QueueUserWorkItem((param) =>
        {
            try
            {
                do
                {
                    string job = "";
                    TransportBean dto = _dbQueueBus.Take(_dbQueueBusCancelToken.Token);
                    try
                    {
                        job = (string)dto.DictionaryTransBean["job"];
                        switch (job)
                        {
                            case "SaveClasse":
                                //Save to table here
                                break;
                            case "SaveRegistrant":
                                //Save Registrant here
                                break;
                          
                        }
                    }
                    catch (Exception ex)
                    {//TODO: Handle this exception or not

                    }

                } while (_dbQueueBusCancelToken.Token.IsCancellationRequested != true);
            }
            catch (OperationCanceledException)
            {

            }
            catch (Exception ex)
            {

            }
        });
    }
}

The inserts are done this way, but without the queuing I was still getting the lock issue.

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