logo标签列表

无法关闭/释放C#中SQLite数据库文件锁

c#sqlite
3

你好，我目前正在开发一个应用程序，可以生成SQLite数据库的模板，但是在某个步骤后，该过程无法释放文件。堆栈跟踪如下：

    {
  "ClassName": "System.AggregateException",
  "Message": "One or more errors occurred.",
  "Data": null,
  "InnerException": {
    "ClassName": "System.IO.IOException",
    "Message": "The process cannot access the file 'C:\Users\James\AppData\Local\Temp\AutoGeneratedDBTemplate-16-11-15-7f3bbbde-6513-4860-bf43-6847a61bb25c.db' because it is being used by another process.",
    "Data": null,
    "InnerException": null,
    "HelpURL": null,
    "StackTraceString": "   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)\r\n   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)\r\n   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AccessCondition accessCondition, BlobRequestOptions options, OperationContext operationContext, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 903\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 884\r\n   at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.TaskFromVoidApm[T1,T2](Func`5 beginMethod, Action`1 endMethod, T1 arg1, T2 arg2, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Core\Util\AsyncExtensions.cs:line 192\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.UploadFromFileAsync(String path, FileMode mode, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 988\r\n   at VMApi.Controllers.SqliteController.<UploadBlobedTemplate>d__4.MoveNext() in C:\path\to\project\VMApi\VMApi\Controllers\SqliteController.cs:line 87",
    "RemoteStackTraceString": null,
    "RemoteStackIndex": 0,
    "ExceptionMethod": "8\nWinIOError\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.IO.__Error\nVoid WinIOError(Int32, System.String)",
    "HResult": -2147024864,
    "Source": "mscorlib",
    "WatsonBuckets": null
  },
  "HelpURL": null,
  "StackTraceString": "   at System.Threading.Tasks.Task.ThrowIfExceptional(Boolean includeTaskCanceledExceptions)\r\n   at System.Threading.Tasks.Task`1.GetResultCore(Boolean waitCompletionNotification)\r\n   at System.Threading.Tasks.Task`1.get_Result()\r\n   at VMApi.Controllers.ExportDBController.<>c__DisplayClass0_0.<Get>b__0(IAsyncResult r) in C:\path\to\project\VMApi\VMApi\Controllers\ExportDBController.cs:line 49",
  "RemoteStackTraceString": null,
  "RemoteStackIndex": 0,
  "ExceptionMethod": "8\nThrowIfExceptional\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.Threading.Tasks.Task\nVoid ThrowIfExceptional(Boolean)",
  "HResult": -2146233088,
  "Source": "mscorlib",
  "WatsonBuckets": null,
  "InnerExceptions": [
    {
      "ClassName": "System.IO.IOException",
      "Message": "The process cannot access the file 'C:\Users\James\AppData\Local\Temp\AutoGeneratedDBTemplate-16-11-15-7f3bbbde-6513-4860-bf43-6847a61bb25c.db' because it is being used by another process.",
      "Data": null,
      "InnerException": null,
      "HelpURL": null,
      "StackTraceString": "   at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)\r\n   at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)\r\n   at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access)\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AccessCondition accessCondition, BlobRequestOptions options, OperationContext operationContext, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 903\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.BeginUploadFromFile(String path, FileMode mode, AsyncCallback callback, Object state) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 884\r\n   at Microsoft.WindowsAzure.Storage.Core.Util.AsyncExtensions.TaskFromVoidApm[T1,T2](Func`5 beginMethod, Action`1 endMethod, T1 arg1, T2 arg2, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Core\Util\AsyncExtensions.cs:line 192\r\n   at Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob.UploadFromFileAsync(String path, FileMode mode, CancellationToken cancellationToken) in c:\Program Files (x86)\Jenkins\workspace\release_dotnet_master\Lib\ClassLibraryCommon\Blob\CloudBlockBlob.cs:line 988\r\n   at VMApi.Controllers.SqliteController.<UploadBlobedTemplate>d__4.MoveNext() in C:\path\to\project\VMApi\VMApi\Controllers\SqliteController.cs:line 87",
      "RemoteStackTraceString": null,
      "RemoteStackIndex": 0,
      "ExceptionMethod": "8\nWinIOError\nmscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\nSystem.IO.__Error\nVoid WinIOError(Int32, System.String)",
      "HResult": -2147024864,
      "Source": "mscorlib",
      "WatsonBuckets": null
    }
  ]
}

两个主要的C#类如下所示。

SqliteController.cs

using System.Web.Http;
using Microsoft.WindowsAzure.Storage.Blob;
using System;
using System.IO;
using System.Threading.Tasks;
using Microsoft.WindowsAzure.Storage;
using System.Collections;
using System.Diagnostics;
using System.Collections.Generic;
using System.Threading;


namespace VMApi.Controllers
{
    public class SqliteController : ApiController
    {
        public static string tempFile()
        {
            return Path.GetTempPath();
        }

        public static async Task<bool> TemplateSql()
        {
            bool Complete = false;
            CloudBlobContainer container = ConnectionProvider.GetUpviseBlobContainer();
            CloudBlockBlob blockBlob = container.GetBlockBlobReference("Tables.sql");
            using (FileStream fileStream = File.OpenWrite(tempFile() + "Tables.sql"))
            {

                IAsyncResult asyncResult = blockBlob.BeginDownloadToStream(fileStream, null, null);
                await Task.Factory.FromAsync(asyncResult, (r) =>
                {
                    try
                    {
                        blockBlob.EndDownloadToStream(r);
                        Complete = true;
                    }
                    catch (StorageException q)
                    {
                        Trace.TraceError("Download Error" + q.ToString());
                        Complete = false;
                    }
                });
                Trace.TraceInformation("Blob Download Status: " + Complete + " " + fileStream.SafeFileHandle.IsClosed);
            }
            return Complete;
        }

        public static string CreateFileName()
        {
            DateTime Date = DateTime.Now;
            String dateF = string.Format("{0:dd-MM-yy}", Date);
            string Filename = string.Format("{0}\\AutoGeneratedDBTemplate-{1}-{2}.db", tempFile(), dateF, Guid.NewGuid().ToString());

            return Filename;
        }

        public static async Task<string> UploadBlobedTemplate()
        {
            string FileName = CreateFileName();
            await DBBUildController.BuildDB(FileName);

            string Complete = "Error.";
            string F = FileName;
            CloudBlobContainer container = ConnectionProvider.GetUpviseBlobContainer();
            CloudBlockBlob blockBlob = container.GetBlockBlobReference(F);

            CancellationToken ca = new CancellationToken();
            Task ado = blockBlob.UploadFromFileAsync(F, FileMode.Open, ca);
            await ado.ContinueWith(t =>
            {
                Complete = "All is well in the Database World";
            });
            return Complete;
        }
    }
}

DBBUild.Controller.cs

using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Data.SQLite;
    using System.Threading.Tasks;
    using System.Collections;
    using System.Diagnostics;

    namespace VMApi.Controllers
    {
        class DBBUildController
        {
            public static async Task BuildDB(string ParseFileName)
            {
                string templateSql = SqliteController.tempFile() + "Tables.sql";
                IEnumerable Lines;
                List<string> LineUnformatted = new List<string>();
                Task<bool> returnedTaskTResult = SqliteController.TemplateSql();
                bool result = await returnedTaskTResult;

                if (result)
                {
                    Lines = File.ReadLines(templateSql);
                    foreach (string v in Lines)
                    {
                        LineUnformatted.Add(v);
                    }
                }

                List<string> LineResults = new List<string>();
                foreach (string q in LineUnformatted)
                {
                    LineResults.Add(q);
                }
                string FileName = ParseFileName;

                SQLiteConnection.CreateFile(FileName);
                using (SQLiteConnection con = new SQLiteConnection("data source=" + FileName + ";Version=3;Pooling=False;"))
                {
                    await con.OpenAsync();

                    SQLiteCommand cmd = new SQLiteCommand(con);

                    using (var transaction = con.BeginTransaction())
                    {

                        Trace.TraceInformation("Starting the SQLCMD Builder");

                        foreach (string liner in LineResults)
                        {
                            cmd.CommandText = liner;
                            Trace.TraceInformation(liner);

                            try
                            {
                                await cmd.ExecuteNonQueryAsync();
                            }
                            catch (SQLiteException)
                            {
                                Trace.TraceError("An Error Occoured Executing the Query");
                            }
                        }
                        Trace.TraceInformation("Transaction Commited");
                        transaction.Commit();
                        transaction.Dispose();
                    }
                    Trace.TraceInformation("DB Connection Closed.");

                    Trace.TraceInformation("Sqlite Data Connection Close File Generation Complete.");
                }
            }
        }
    }

从调试输出：

https://gist.github.com/anonymous/b0f74be1057b8e834e16

我已经试过几种关闭数据库的方法，但说实话我不确定这是否是问题所在。非常感谢任何帮助。

使用 using 语句就足够了。您不必手动关闭连接。如果删除对 Shutdown 的显式调用，仍会出现错误吗？ - torvin
谢谢，但是当我删除显式调用关闭时，遗憾的是我仍然会得到相同的错误。 - James Griffis
抱歉，我刚意识到我提供的是错误的堆栈跟踪。你的解决方案确实修复了关于关闭错误的原始问题，但并不是我一直在尝试解决的那个问题。我已经编辑了原始帖子，现在提供的是正确的堆栈跟踪。 - James Griffis
再次强调，您应该将fileStream放在using语句块中，并且不要手动调用其Dispose方法或其SafeFileHandle的Dispose方法。此外，请提供您的日志记录器的输出，这可能会有所帮助。 - torvin
也许这是由于SQLite中的一个错误导致的：https://dev59.com/LGoy5IYBdhLWcg3wfeMR#8513453。还要阅读该问题的其他答案。例如，您忘记在“using”中包装您的“cmd”。 - torvin
显示剩余2条评论
1个回答
8

感谢大家提供的建议和链接，对我帮助非常大。

最终我通过结合GC.Collect()和cmd.Dispose()函数解决了问题。

这也可以通过在SQLiteCommand方法上使用using来解决，但在我的特定代码片段中，这会导致cmd出现未定义错误。

整个函数的代码如下：

public static async Task BuildDB(string ParseFileName)
        {
            string templateSql = SqliteController.tempFile() + "Tables.sql";
            IEnumerable Lines;
            List<string> LineUnformatted = new List<string>();
            Task<bool> returnedTaskTResult = SqliteController.TemplateSql();
            bool result = await returnedTaskTResult;

            if (result)
            {
                Lines = File.ReadLines(templateSql);
                foreach (string v in Lines)
                {
                    LineUnformatted.Add(v);
                }
            }

            List<string> LineResults = new List<string>();
            foreach (string q in LineUnformatted)
            {
                LineResults.Add(q);
            }
            string FileName = ParseFileName;

            SQLiteConnection.CreateFile(FileName);
            using (SQLiteConnection con = new SQLiteConnection("data source=" + FileName + ";Version=3;Pooling=False;"))
            {
                await con.OpenAsync();

                SQLiteCommand cmd = new SQLiteCommand(con);

                using (var transaction = con.BeginTransaction())
                {

                    Trace.TraceInformation("Starting the SQLCMD Builder");

                    foreach (string liner in LineResults)
                    {
                        cmd.CommandText = liner;

                        try
                        {
                            await cmd.ExecuteNonQueryAsync();
                        }
                        catch (SQLiteException)
                        {
                            Trace.TraceError("An Error Occoured Executing the Query");
                        }
                    }
                    Trace.TraceInformation("Transaction Commited");
                    transaction.Commit();
                    cmd.Dispose();
                    transaction.Dispose();
                }

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

                Trace.TraceInformation("Sqlite Data Connection Close File Generation Complete.");
            }
        }

为了更清晰明了，我解决了以下问题：

            transaction.Commit();
            cmd.Dispose();
            transaction.Dispose();
            GC.Collect();
            GC.WaitForPendingFinalizers();

非常感谢你们的帮助 :)

这个技巧有助于解决MS Access的JET引擎和SQLite的.NET封装器的问题。请注意，GC.WaitForPendingFinalizer会暂停当前线程（可能只有几微秒）。理想情况下，您的数据库操作应该在单独的线程中完成。 - AlainD
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接