Unable to Close/Dispose a File lock on SQLite database C#

Question

Hi i am currently working on an Application that generates template SQLite databases and i am having an issue with the process not releasing the file after a particular step. the Stack trace is as follows.

    {
  "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
    }
  ]
}

and the two primary C# classes are as follows.

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.");
                }
            }
        }
    }

Output from Debug:

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

I have tried a few way of closing the database but to be honest im not 100% sure its the issue here Any help would be greatly appreciated.

`using` statement is enough. You don't have to close the connection manually. Do you still get the error if you remove the explicit call to `Shutdown`? — torvin, Nov 16 '15 at 00:34
Thanks, But yes sadly i do still get the same error when i remover the Explicit call to shutdown. — James Griffis, Nov 16 '15 at 00:41
Sorry i just Realized i had the wrong stack trace, Your solution did fix the original one about the shutdown error but it was not the one i have been trying to resolve i have edited the OP and its now the correct strace. — James Griffis, Nov 16 '15 at 01:15
Again, you should wrap your `fileStream` in a `using` and don't manually call `Dispose` on it or on its `SafeFileHandle`. Also, please provide your logger's output, it could be helpful. — torvin, Nov 16 '15 at 01:21
This smells of a concurrency issue. Have you considered thread locking around the file IO access? — S. Brentson, Nov 16 '15 at 01:54
I have updated the files per torvin's suggestions (i had to add the debug output to gist it breached the body limit here by near 10K chars lol). @S. Brentson i had not though of that no i will do some research on it :) — James Griffis, Nov 16 '15 at 01:59
Maybe this is due to a bug in SQLite: http://stackoverflow.com/a/8513453/332528. Also read other answers on that question. E.g. you forgot to wrap your `cmd` in a `using`. — torvin, Nov 16 '15 at 05:05

score 8 · Accepted Answer · answered Nov 16 '15 at 12:24

Thanks so much for the suggestions and links guys it has helped a ton,

In the end i have resolved the issue by combining GC.Collect() and the cmd.Dispose() functions.

This may also be solvable by doing a using on the SQLiteCommand method but in my particular piece of code that results in an undefined error on cmd.

The entire function ends up looking like:

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.");
            }
        }

And for clarity i solved the issue with:

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

Thanks again for all the help :)

This tip helped resolve an issue with both the JET engine for MS Access and the .NET wrapper for SQLite. Note that `GC.WaitForPendingFinalizer` suspends the current thread (perhaps only for a few µs). Ideally your database manipulation would be done in a separate thread. — AlainD, May 31 '22 at 10:00

Unable to Close/Dispose a File lock on SQLite database C#

1 Answers1