解决System.Data.SQLite数据库关闭后，数据文件仍被占用

http://www.itkeyword.com/doc/9440474319203481x111/system-data-sqlite-close-not-releasing-database-file
转载
解决System.Data.SQLite Close() not releasing database file
itPublisher 分享于 2017-03-17
推荐：Mono.Data.Sqlite.dll unity3d导入此dll报错 System.Runtime.InteropServices.GuidAttribute

unity 3d 导入dll时候报错： Internal compiler error. See the console log for more information. output was:Unhandled Exception: System.TypeLoadExceptio

2019阿里云全部产品优惠券(新购或升级都可以使用，强烈推荐)
领取地址：https://promotion.aliyun.com/ntms/yunparter/invite.html

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);
sqlite system.data.sqlite
  |
  this question edited Dec 15 '11 at 15:32 asked Dec 14 '11 at 21:28 Tom Cerul 663 1 6 21 Did you try: myconnection.Close(); myconnection.Dispose(); ? – UGEEN Jun 19 '13 at 12:54 When using sqlite-net, you can use SQLiteAsyncConnection.ResetPool(), see this issue for details. – Uwe Keim Nov 7 '16 at 20:58
|
12 Answers
12
解决方法
Encountered the same problem a while ago while writing a DB abstraction layer for C# and I never actually got around to finding out what the issue was. I just ended up throwing an exception when you attempted to delete a SQLite DB using my library.

Anyway, this afternoon I was looking through it all again and figured I would try and find out why it was doing that once and for all, so here is what I’ve found so far.

What happens when you call SQLiteConnection.Close() is that (along with a number of checks and other things) the SQLiteConnectionHandle that points to the SQLite database instance is disposed. This is done through a call to SQLiteConnectionHandle.Dispose(), however this doesn’t actually release the pointer until the CLR’s Garbage Collector performs some garbage collection. Since SQLiteConnectionHandle overrides the CriticalHandle.ReleaseHandle() function to call sqlite3_close_interop() (through another function) this does not close the database.

From my point of view this is a very bad way to do things since the programmer is not actually certain when the database gets closed, but that is the way it has been done so I guess we have to live with it for now, or commit a few changes to System.Data.SQLite. Any volunteers are welcome to do so, unfortunately I am out of time to do so before next year.

TL;DR The solution is to force a GC after your call to SQLiteConnection.Close() and before your call to File.Delete().

Here is the sample code:

string filename = “testFile.db”;
SQLiteConnection connection = new SQLiteConnection(“Data Source=” + filename + “;Version=3;”);
connection.Close();
GC.Collect();
File.Delete(filename);
Good luck with it, and I hope it helps

|
  this answer answered Dec 14 '11 at 23:58 Benjamin Pannell 676 6 8 1 Yes! Thank you! It looks like the GC might need a little bit to get its work done. – Tom Cerul Dec 15 '11 at 15:28 1 You might also want to look at C#SQLite, I’ve just moved all my code over to using it. Of course, if you are running something performance critical then C is probably faster than C#, but I am a fan of managed code… – Benjamin Pannell Jan 1 '12 at 8:14 1 I know this is old, but thanks for saving me some pain. This bug also affects the Windows Mobile / Compact Framework build of SQLite. – StrayPointer Nov 11 '13 at 16:28 1 Great work! Solved my problem immediately. In 11 years of C# development I never had the need to use GC.Collect: Now this is the first example I’m forced to do so. – Pilsator Oct 17 '14 at 13:22 6 GC.Collect(); works, but System.Data.SQLite.SQLiteConnection.ClearAllPools(); deals with the issue using the library’s API. – Aaron Hudon Aug 6 '15 at 20:58
|
Just GC.Collect() didn’t work for me.

I had to add GC.WaitForPendingFinalizers() after GC.Collect() in order to proceed with the file deletion.

|
  this answer edited Jul 1 '14 at 1:31 djikay 6,126 6 27 38 answered Jul 1 '14 at 1:09 Batiati 331 3 2 2 This is not that surprising, GC.Collect() just starts a garbage collection which is asynchronous so to make sure all has been cleaned up you have to wait for it explicitly. – ChrisWue Jul 24 '16 at 22:59 1 I experienced the same, had to add the GC.WaitForPendingFinalizers(). This was in 1.0.103 – Vort3x Sep 19 '16 at 17:47
|
In my case I was creating SQLiteCommand objects without explicitly disposing them.

var command = connection.CreateCommand();
command.CommandText = commandText;
value = command.ExecuteScalar();
I wrapped my command in a using statement and it fixed my issue.

static public class SqliteExtensions
{
public static object ExecuteScalar(this SQLiteConnection connection, string commandText)
{
using (var command = connection.CreateCommand())
{
command.CommandText = commandText;
return command.ExecuteScalar();
}
}
}
The using statement ensures that Dispose is called even if an exception occurs.

Then it’s a lot easier to execute commands as well.

value = connection.ExecuteScalar(commandText)
// Command object created and disposed

|
  this answer edited Sep 2 '14 at 13:56 answered Apr 4 '13 at 20:25 Nate 5,872 2 32 49 4 I very much recommend against swallowing exceptions like this – Tom McKearney Aug 29 '13 at 17:19 I agree. Removed. – Nate Aug 29 '13 at 17:22
|
I was having a similar problem, I’ve tried the solution with GC.Collect but, as noted, it can take a long time before the file becomes not locked.

I’ve found an alternative solution that involves the disposal of the underlying SQLiteCommands in the TableAdapters, see this answer for additional information.

推荐：Win7 64bit系统下未能加载文件或程序集“System.Data.SQLite”的解决办法

在http://www.cnblogs.com/downcom/archive/2009/10/26/1590120.html中我曾给出一种解决方法，但这种方法在本地用64位系统开发程序，但服务器却是使用的32位的系

|
  this answer edited Oct 1 '12 at 19:52 answered Oct 1 '12 at 19:43 edymtt 1,405 1 16 31 you were right! In some cases simple ‘GC.Collect’ worked for me, In others i had to dispose any SqliteCommands associated with the connection before calling GC.Collect or else it won’t work! – Eitan H.S. Dec 13 '12 at 16:12 1 Calling Dispose on the SQLiteCommand worked for me. As an aside comment - if you are calling GC.Collect you are doing something wrong. – Nathan Adams Jan 6 '13 at 22:28 @NathanAdams when working with EntityFramework there is not a single command object you ever can dispose. So either the EntityFramework itself or the SQLite for EF wrapper is doing somethign wrong, too. – springy76 Jul 22 '14 at 8:06
|
The following worked for me:

MySQLiteConnection.Close();
SQLite.SQLiteConnection.ClearAllPools()
More info: Connections are pooled by SQLite in order to
  performance.It means when you call Close method on a connection object, connection to database may still be alive (in the background) so that next Open method become faster.When you known that you don’t want a new connection anymore, calling ClearAllPools closes all the connections which are alive in the background and file handle(s?) to the db file get released.Then db file may get removed, deleted or used by another process.

|
  this answer edited Feb 12 '15 at 11:00 Lorenzo Polidori 5,142 6 28 45 answered Jul 4 '14 at 8:57 Arvin 629 2 8 11 1 Could you please add explanation to why this is good solution to the problem. – Matas Vaitkevicius Jul 4 '14 at 9:43 You can also use SQLiteConnectionPool.Shared.Reset(). This will close all open the connections. In particular, this is a solution if you use SQLiteAsyncConnection that does not have a Close() method. – Lorenzo Polidori Feb 12 '15 at 10:58
|
Had a similar issue, though the garbage collector solution didn’t fix it.

Found disposing of SQLiteCommand and SQLiteDataReader objects after use saved me using the garbage collector at all.

SQLiteCommand command = new SQLiteCommand(sql, db);
command.ExecuteNonQuery();
command.Dispose();

|
  this answer answered Jan 27 '15 at 14:49 themullet 163 2 7
|
I believe the call to SQLite.SQLiteConnection.ClearAllPools() is the cleanest solution. As far as I know it is not proper to manually call GC.Collect() in the WPF environment. Although, I did not notice the problem until I have upgraded to System.Data.SQLite 1.0.99.0 in 3/2016

|
  this answer edited Mar 19 '16 at 18:20 Peter O. 17.2k 8 49 66 answered Mar 19 '16 at 18:09 Jona Varque 11 1
|
I was struggling with the similar problem. Shame on me… I finally realized that Reader was not closed. For some reason I was thinking that the Reader will be closed when corresponding connection is closed. Obviously, GC.Collect() didn’t work for me.
Wrapping the Reader with "using: statement is also a good idea. Here is a quick test code.

static void Main(string[] args)
{
try
{
var dbPath = “myTestDb.db”;
ExecuteTestCommand(dbPath);
File.Delete(dbPath);
Console.WriteLine(“DB removed”);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
Console.Read();
}

private static void ExecuteTestCommand(string dbPath)
{
using (var connection = new SQLiteConnection(“Data Source=” + dbPath + “;”))
{
using (var command = connection.CreateCommand())
{
command.CommandText = “PRAGMA integrity_check”;
connection.Open();
var reader = command.ExecuteReader();
if (reader.Read())
Console.WriteLine(reader.GetString(0));

        //without next line database file will remain locked
        reader.Close();
    }
}   

}

|
  this answer answered Aug 11 '16 at 17:48 Mike Znaet 46 10
|
Try this… this one tries all the above codes… worked for me

Reader.Close()
connection.Close()
GC.Collect()
GC.WaitForPendingFinalizers()
command.Dispose()
SQLite.SQLiteConnection.ClearAllPools()

Hope that helps

|
  this answer answered Aug 19 '16 at 10:14 Bishnu Dev 25 7 WaitForPendingFinalizers made all the difference for me – Todd Oct 24 '16 at 1:05
|
Maybe you don’t need to deal with GC at all. Please, check if all sqlite3_prepare is finalized.

For each sqlite3_prepare, you need a correspondent sqlite3_finalize.

If you don’t finalize correctly, sqlite3_close will not close the connection.

|
  this answer answered Sep 20 '16 at 3:00 João Monteiro 11 1
|
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 OpenCommands = new List();

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.

|
  this answer answered Jul 8 '16 at 13:41 Hallupa 26 7
|
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.