System.Data.SQLite Close() not releasing database

Question

I'm having a problem closing my database before an attempt to delete the file. The code is just

 myconnection.Close();    
 File.Delete(filename);

And the Delete throws an exception that the file is still in use. I've re-tried the Delete() in the debugger after a few minutes, so it's not a timing issue.

I have transaction code but it doesn't run at all before the Close() call. So I'm fairly sure it's not an open transaction. The sql commands between open and close are just selects.

ProcMon shows my program and my antivirus looking at the database file. It does not show my program releasing the db file after the close().

Visual Studio 2010, C#, System.Data.SQLite version 1.0.77.0, Win7

I saw a two year old bug just like this but the changelog says it's fixed.

Is there anything else I can check? Is there a way to get a list of any open commands or transactions?

---

New, working code:

 db.Close();
 GC.Collect();   // yes, really release the db

 bool worked = false;
 int tries = 1;
 while ((tries < 4) && (!worked))
 {
    try
    {
       Thread.Sleep(tries * 100);
       File.Delete(filename);
       worked = true;
    }
    catch (IOException e)   // delete only throws this on locking
    {
       tries++;
    }
 }
 if (!worked)
    throw new IOException("Unable to close file" + filename);

Answer 1

Had a similar problem. Calling Garbage Collector didn't help me. LAter I found a way to solve the problem

Author also wrote that he did SELECT queries to that database before trying to delete it. I have the same situation.

I have the following code:

SQLiteConnection bc;
string sql;
var cmd = new SQLiteCommand(sql, bc);
SQLiteDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close(); // when I added that string, the problem became solved.

Also, I don't need to close database connection and to call Garbage Collector. All I had to do is to close reader which was created while executing SELECT query

Answer 2

Use GC.WaitForPendingFinalizers()

Example:

Con.Close();  
GC.Collect();`
GC.WaitForPendingFinalizers();
File.Delete(Environment.CurrentDirectory + "\\DATABASENAME.DB");

Answer 3

I've been having the same problem with EF and System.Data.Sqlite.

For me I found SQLiteConnection.ClearAllPools() and GC.Collect() would reduce how often the file locking would happen but it would still occasionally happen (Around 1% of the time).

I've been investigating and it seems to be that some SQLiteCommands that EF creates aren't disposed and still have their Connection property set to the closed connection. I tried disposing these but Entity Framework would then throw an exception during the next DbContext read - it seems EF sometimes still uses them after connection closed.

My solution was to ensure the Connection property is set to Null when the connection closes on these SQLiteCommands. This seems to be enough to release the file lock. I've been testing the below code and not seen any file lock issues after a few thousand tests:

public static class ClearSQLiteCommandConnectionHelper
{
    private static readonly List<SQLiteCommand> OpenCommands = new List<SQLiteCommand>();

    public static void Initialise()
    {
        SQLiteConnection.Changed += SqLiteConnectionOnChanged;
    }

    private static void SqLiteConnectionOnChanged(object sender, ConnectionEventArgs connectionEventArgs)
    {
        if (connectionEventArgs.EventType == SQLiteConnectionEventType.NewCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Add((SQLiteCommand)connectionEventArgs.Command);
        }
        else if (connectionEventArgs.EventType == SQLiteConnectionEventType.DisposingCommand && connectionEventArgs.Command is SQLiteCommand)
        {
            OpenCommands.Remove((SQLiteCommand)connectionEventArgs.Command);
        }

        if (connectionEventArgs.EventType == SQLiteConnectionEventType.Closed)
        {
            var commands = OpenCommands.ToList();
            foreach (var cmd in commands)
            {
                if (cmd.Connection == null)
                {
                    OpenCommands.Remove(cmd);
                }
                else if (cmd.Connection.State == ConnectionState.Closed)
                {
                    cmd.Connection = null;
                    OpenCommands.Remove(cmd);
                }
            }
        }
    }
}

To use just call ClearSQLiteCommandConnectionHelper.Initialise(); at the start of application load. This will then keep a list of active commands and will set their Connection to Null when they point to a connection that is closed.

Answer 4

I was using SQLite 1.0.101.0 with EF6 and having trouble with the file being locked after all connections and entities disposed.

This got worse with updates from the EF keeping the database locked after they had completed. GC.Collect() was the only workaround that helped and I was beginning to despair.

In desperation, I tried Oliver Wickenden's ClearSQLiteCommandConnectionHelper (see his answer of 8 July). Fantastic. All locking problems gone! Thanks Oliver.