Sqlite Keeps the Database Locked Even After the Connection Is Closed

SQLite keeps the database locked even after the connection is closed

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.

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

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".

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.

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

Update 2013-07-05 17:36 UTC gorogm's answer highlights two important things:

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

• 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 locking file even though connection is closed

I'm late to the party, but I had to:

GC.WaitForPendingFinalizers();
GC.Collect();

Prior to calling File.Delete().

SQLite database locked after showing a DataGrid

Fundamentally, your code leaves several other things undisposed: the DbCommand, DataAdapter and most importantly, an odd DataReader is created at the end which remains open and alive. Anything which implements the Dispose method ought to be created and used in a Using block to be sure it is disposed and resources released.

This is especially important with SQLite since the DB is just a file.

---

For basic add/change/delete apps your can create a DataAdapter which will live for the life of your app/form and be able to perform all the DB operations for you:

' form/class level objects
Private daLite As SQLiteDataAdapter
Private dtLite As DataTable

Initializing the DB provider objects:

' spell out your SQL
Dim SQL = "SELECT Id, Name, Fish, Bird, Color, Price FROM Sample"

' persistent datatadapter
daLite = New SQLiteDataAdapter(SQL, LiteConnStr)
dtLite = New DataTable

' teach the DA how to do everything
Dim cb As New SQLiteCommandBuilder(daLite)
daLite.InsertCommand = cb.GetInsertCommand
daLite.UpdateCommand = cb.GetUpdateCommand
daLite.DeleteCommand = cb.GetDeleteCommand

' load schema, table
daLite.FillSchema(dtLite, SchemaType.Source)
daLite.Fill(dtLite)

dgv1.DataSource = dtLite

Passing the connection string rather than a connection object, lets the DataAdapter create its own connection which it will open and close as needed. At the end, it knows how to add, insert and delete from the database because it "holds onto" those command objects. You may not need any other SQL in the app.

Note that each row in the DataTable has a RowState which indicates if it is added, deleted or changed so you can accumulate several changes before updating the database:

' dataadapter will open its connection, then close 
Dim rows = daLite.Update(dtLite)

All the new rows are added, changed ones are updated, deleted ones are deleted. rows will be the total number of them.

Another benefit is that you will be able to "refresh" the rows. If changes are being made outside the app (such as by you in a UI DB browser). Performing daLite.Fill(dtLite) again later, picks up just those changes.

Notes

• In this case you would not dispose of the DBCommand, DBConnection etc in the procedure (or anywhere) because the plan is to use it over and over.
• You should spell out your SQL to control the order of the DGV column display.
  
  
  • For the above to work, the Primary Key needs to be part of the SQL.
• Incidentally, changing the DGV HeaderText doesnt change the column name, just the displayed text.
• When using something like this for bulk imports, a Transaction will speed it up.

This only works on simple one table queries as yours appears to be.

OperationalError: database is locked

From django doc:

SQLite is meant to be a lightweight
database, and thus can't support a
high level of concurrency.
OperationalError: database is locked
errors indicate that your application
is experiencing more concurrency than
sqlite can handle in default
configuration. This error means that
one thread or process has an exclusive
lock on the database connection and
another thread timed out waiting for
the lock the be released.

Python's SQLite wrapper has a default
timeout value that determines how long
the second thread is allowed to wait
on the lock before it times out and
raises the OperationalError: database
is locked error.

If you're getting this error, you can
solve it by:

• Switching to another database backend. At a certain point SQLite becomes too "lite" for real-world applications, and these sorts of concurrency errors indicate you've reached that point.
• Rewriting your code to reduce concurrency and ensure that database transactions are short-lived.
• Increase the default timeout value by setting the timeout database option

http://docs.djangoproject.com/en/dev/ref/databases/#database-is-locked-errorsoption

How do I unlock a SQLite database?

In windows you can try this program http://www.nirsoft.net/utils/opened_files_view.html to find out the process is handling db file. Try closed that program for unlock database

In Linux and macOS you can do something similar, for example, if your locked file is development.db:

$ fuser development.db

This command will show what process is locking the file:

> development.db: 5430

Just kill the process...

kill -9 5430

...And your database will be unlocked.

Python SQLite: database is locked

Turned out the problem happened because the path to the db file was actually a samba mounted dir. I moved it and that started working.

---

Related Topics