SQLite keeps the database locked even after the connection is closed

Question

I'm using System.Data.SQLite provider in an ASP.NET application (framework 4.0). The issue I'm running into is that when I INSERT something in a table in the SQLite database, the database gets locked and the lock isn't being released even after the connection is disposed.

When trying to access the file, the error is: "The process cannot access the file 'catalog.sqlite' because it is being used by another process."

My code is pretty straightforward, I open the connection, read some data from a SQLServer database, insert that data into SQLite (through SQLiteDataAdapter) and then close the connection and dispose everything just to be on the safe side. But still, I get that error when I try to zip the file after it's being populated with the data.

I've read all kind of suggestions here on StackOverflow but none of them has helped solving the problem (turning off the antivirus, changing the transaction model, waiting a few seconds before zipping up the file, wrapping all the insert calls into a transaction, etc.. but none has helped solving this issue.

Maybe there's something specific to ASP.NET (multithreading being the issue? Even though I'm testing it on a development machine where there's only one call to that function and no concurrency?)

As a side note, I tried avoiding DataTable and SQLiteDataAdapter and using only SQLiteCommand directly and that way it works a charm. Of course I can keep building my queries as strings instead of using the data adapters, but I kind of find it a bit awkward when there's a framework built to do that.

Swarm

Are you wrapping the commands and connections in using statements? – Kanter

Is this lock by your application, i.e., does the lock vanish when it exits? – Paw

@Kanter I'm not wrapping the connection with using. But I'm not wrapping it even with the version that works without locking (aka the one using commands instead of data adapters). – Swarm

@Paw Yes, the lock is due to my application. If I stop the development web server, the file gets unlocked. – Swarm

Please show the source, at least how you create and close/dispose all objects. – Paw

Answer 1

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

Flamen

I am seeing very large memory leaks using Entity framework and the latest 1.82.0 sqlite. Do you think this is the problem? – Anastrophe

Probably, since (I think, I have scarce experience in EF) there might be unmanaged resources related to SQLiteCommands waiting to be released. This SO thread and this ticket seems to corroborate your hypothesis. To avoid the leaks you could try to disable "multiple active result sets" in EF or you could try to use a managed library like C#-SQLite. Hope this helps. – Flamen

Answer 2

I have the same problem. My scenario was after getting the data inside SQLite Database file I want to delete that file but it always throw an error "...using by other process". Even I dispose the SqliteConnection or SqliteCommand the error still occur. I've fixed the error by calling GC.Collect().

Code snippet

public void DisposeSQLite()
{
    SQLiteConnection.Dispose();
    SQLiteCommand.Dispose();

    GC.Collect();
}

Hope this help.

Scleroprotein

I have had same problem too and I had to use GC.Collect(); refer to: system.data.sqlite.org/index.html/tktview/… Hopefully the next Sqlite version will solve this issue. – Savitt

@Scleroprotein @Assassinbeast a cleaner way of dealing with the issue you mention is to call System.Data.SQLite.SQLiteConnection.ClearAllPools(); instead of the two dispose calls + GC.Collect – Wedlock

In some cases is necesary to wait for the GC task finalize to use the sqlite file with GC.WaitForPendingFinalizers() – Julietajulietta

Answer 3

The following worked for me:

MySQLiteConnection.Close();
SQLite.SQLiteConnection.ClearAllPools()

Anatolio

Do note that calling ClearAllPools... clears... all pools. This may cause (performance)issues in multi-threaded situations. See https://mcmap.net/q/175278/-sqlite-keeps-the-database-locked-even-after-the-connection-is-closed for clearing the one, specific pool. – Underlet

Answer 4

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)
    {
        // Added using
        using (var command = connection.CreateCommand())
        {
            command.CommandText = commandText;
            return command.ExecuteScalar();
        }
    }
}

Then you can use it like this

connection.ExecuteScalar(commandText);

Light

I highly recommend against swallowing exceptions like this! – Humpback

How exactly does this swallow exceptions? When I use this I get an exception throw at the end of the using statement if the command failed. – Interleave

I removed the code that swallowed exceptions so it no longer does this. If you really want to swallow the exceptions you can add a Try-Catch block to the code around the using. – Light

Answer 5

In most cases the problem will arise if you don't dispose your readers and commands properly. There is a scenario in which commands and readers will not dispose properly.

Scenario 1: In case you are running a boolean function. before a result is reached the code in the finally block will not excecute. This is a big problem if you are going to be evaluating the results of function isDataExists while executing code if it suits the result i.e

    if(isDataExists){
        // execute some code
    }

The function being evaluated

    public bool isDataExists(string sql)
    {
        try
        {
            OpenConnection();
            SQLiteCommand cmd = new SQLiteCommand(sql, connection);
            reader = cmd.ExecuteReader();
            if (reader != null && reader.Read())
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception expMsg)
        {
            //Exception
        }
        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            CloseConnection();
        }
        return true;
    }

Solution: Dispose your reader and command inside the try block as follows

            OpenConnection();
            SQLiteCommand cmd = new SQLiteCommand(sql, connection);
            reader = cmd.ExecuteReader();
            if (reader != null && reader.Read())
            {
                cmd.Dispose();
                CloseConnection();
                return true;
            }
            else
            {
                cmd.Dispose();
                CloseConnection();
                return false;
            }

Finally dispose the reader and command just in case some thing went wrong

        finally
        {
            if (reader != null)
            {
                reader.Dispose();
            }
            CloseConnection();
        }

Enedina

If you dispose the command in the try block, an exception that occurs during ExecuteReader() will cause the command to not be disposed. You should use using blocks, or if you prefer writing them out yourself, you could nest multiple try/finally blocks. – Yalu

Answer 6

As said earlier SQLite objects must be destroyed. However, there is a strange behavior: connection must be open during a call Dispose on commands. For example:

using(var connection = new SqliteConnection("source.db"))
{
    connection.Open();
    using(var command = connection.CreateCommand("select..."))
    {
        command.Execute...
    }
}

works fine, but:

using(var connection = new SqliteConnection("source.db"))
{
    connection.Open();
    using(var command = connection.CreateCommand("select..."))
    {
        command.Execute...
        connection.Close();
    }
}

gives the same file lock

Countermand

Answer 7

I found edymtt's answer right about blaming TableAdapters / Datasets, but instead of modifying the every time re-generated TableAdapter codefile, I found an other solution: to manually call .Dispose on the TableAdapter's child elements. (In .NET 4.5, latest SQLite 1.0.86)

using (var db = new testDataSet())
{
    using (testDataSetTableAdapters.UsersTableAdapter t = new testDataSetTableAdapters.UsersTableAdapter())
    {
        t.Fill(db.Users);
        //One of the following two is enough
        t.Connection.Dispose(); //THIS OR
        t.Adapter.Dispose();    //THIS LINE MAKES THE DB FREE
    }
    Console.WriteLine((from x in db.Users select x.Username).Count());
}

Virile

I don't have to modify TableAdapters when I change the dataset in the designer -- I have implemented Dispose using partial classes. Thanks for pointing out that in the latest versions of System.Data.SQLite my code is no longer necessary (see changelog for release 1.0.84 . – Flamen

Answer 8

I had the same problem and it was only fixed by disposing the DbCommand in the using statement, but with Pooling = true my problem was fixed!!

                SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
                {
                    Pooling = true
                };

Coaly

Answer 9

Open windows task manager and close SQl instance and project to manually close connection.

In code add System.Data.SQLite.SQLiteConnection.ClearAllPools();

to close connections after youve done all your operations.

Should be able to run smoothly now

Gelignite

Answer 10

Old question, but here's a sample with EntityFrameworkCore in .NET 7. The solution was indeed to clear the connection pool using SqliteConnection.ClearPool(...).

I have a web service where a user uploads an SQLite DB file, it downloads it, reads a table, then deletes the downloaded database file immediately.

var dbFilePath = "Database.db";
var connectionString = $"Data Source={dbFilePath};";

var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
optionsBuilder.UseSqlite(connectionString);
await using var context = new MyDbContext(optionsBuilder.Options);

var retrievedData = await context.MyTable.ToListAsync();
SqliteConnection.ClearPool((SqliteConnection) context.Database.GetDbConnection());
await context.DisposeAsync();

// File.Delete(dbFilePath); // <- works immediately, file is not locked

This is a simple in & out example. It loads the SQLite DB, queries a table, then clears the (specific) connection from the connection pool. Contrary to some other answers that call ClearAllPools which may cause issues in multi-threaded situations.

Cartulary

Answer 11

Must release every thing (SQLiteConnection, SQLiteDataAdapter, SQLiteCommand, ...), this is what is worked for me:

            SQLiteConnection con = null;
            SQLiteDataAdapter adp = null;
            SQLiteCommand s = null;
            try
            {
               ....
                 con.Close();
                if (s != null)
                {
                    try
                    {
                        s.Dispose();
                        s = null;
                    }
                    catch { }
                }

                if (adp != null)
                {
                    try
                    {
                        adp.Dispose();
                        adp = null;
                    }
                    catch { }
                }

                ReleaseSQLiteDatabaseConnection.Release(con);
                try
                {
                    con.Dispose();
                }
                catch { }
                con = null;
                
            }
            catch (Exception ex)
            {
                if (con != null)
                {
                    con.Close();
                    if (s != null)
                    {
                        try
                        {
                            s.Dispose();
                            s = null;
                        }
                        catch { }
                    }

                    if (adp != null)
                    {
                        try
                        {
                            adp.Dispose();
                            adp = null;
                        }
                        catch { }
                    }
                    ReleaseSQLiteDatabaseConnection.Release(con);
                    try
                    {
                        con.Dispose();
                    }

                    catch { }
                    con = null;
                }
                
              
                throw ex;
            } 
           .....
        public class ReleaseSQLiteDatabaseConnection
        {
        public static void Release(SQLiteConnection con)
        {
            try
            {
                SQLiteConnection.ClearPool(con);
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch 
            {
                
            }
        }
    }

Pinch

Answer 12

This was one of the top google results I had found when I ran into this error. However, none of the responses helped me so after more searching around and googling I came up with this code that works from some of the code from http://www.tsjensen.com/blog/post/2012/11/10/SQLite-on-Visual-Studio-with-NuGet-and-Easy-Instructions.aspx

However, I did not have to use the NuGet at all. What my program does is downloads a db file from a server every time it is opened. Then if a user updates that db, it will be uploaded for everyone to get the next time they open the same program. I was getting the error that the file was in use after updating the local file and trying to upload it to our SharePoint. Now it works fine.

Public Function sqLiteGetDataTable(sql As String) As DataTable
    Dim dt As New DataTable()
    Using cnn = New SQLiteConnection(dbConnection)
        cnn.Open()
        Using cmd As SQLiteCommand = cnn.CreateCommand()
            cmd.CommandText = sql
            Using reader As System.Data.SQLite.SQLiteDataReader = cmd.ExecuteReader()
                dt.Load(reader)
                reader.Dispose()
            End Using
            cmd.Dispose()
        End Using
        If cnn.State <> System.Data.ConnectionState.Closed Then
            cnn.Close()
        End If
        cnn.Dispose()
    End Using
    Return dt
End Function

Onega

Answer 13

Ensuring that any IDisposable (e.g., SQLiteConnection, SQLiteCommand, etc) is properly disposed of solves this problem. I should re-iterate that one must be using "using" as a habit to ensure proper disposing of disposable resources.

Zealot

Answer 14

I was only having the problems mentioned here when locking the computer, even after unlocking it, was working fine otherwise lucky cuz i just started locking it lately and the software just released couple days ago before anyone knows about it.

Anyway i had all the stuff like closing connections and ClearAllPools etc but was missing aTableAdapter.Adapter.Dispose() and that fixed it.

Armoire

Answer 15

Had the same problem. Solved it simply by installing SQLite 1.0.111 (via nuget).

Did not have to change anything on my code, only update to that version. Previously I used 1.0.85 where the file was locked, although the connection was closed and disposed.

Chloramphenicol

Answer 16

In my case, I'm creating a new smaller DB from a larger DB. I was initially attaching the larger DB. I switched it around so that the smaller DB is attached. I run a DETACH command on the smaller DB before closing the connection. The smaller DB is no longer locked so it's available for users to download. I was still able to use the larger DB with simultaneous connections without issue.

Ramiform

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Judaica

Answer 17

Further to Rene's answer. My application .NET 7 - not using EF - was using nuget Microsoft.Data.Sqlite 5.x and once I upgraded to 7.x the database was being locked on the first call to the database and stayed that way until the application was closed even though I was correctly closing and disposing.

It appears that Pooling was added to improve performance. https://github.com/dotnet/efcore/issues/13837

So, if there is a place in your code that you need unlock the database you can do this:

 using SqliteConnection sqliteConnection = new($"data source = {MyApplication.PathToDatabase}");
        SqliteConnection.ClearPool(sqliteConnection);

That's it. You don't need to do it in your data library or on every call. Just wherever you need it.

Potsdam

Answer 18

I did a good bit of testing to find that pooling was the issue. I know they said above to:

System.Data.SQLite.SQLiteConnection.ClearAllPools();

But this didn't work for me as I'm using Microsoft.Data.Sqlite. Instead, I had to set pooling to false in the connection string directly, or in the connection string builder. Like this:

string connectionString = new SqliteConnectionStringBuilder
    {
        DataSource = fileName,
        Mode = SqliteOpenMode.ReadWriteCreate,
        Pooling = false,
    }.ToString();

Or like this if you want to do it directly in the connection string:

using (var mDbConnection = new SqliteConnection("DataSource=DatabaseFileNameHere,Pooling=false"))

So all together that's:

public void CreateSqliteDbFile(string fileName)
{
    string connectionString = new SqliteConnectionStringBuilder
    {
        DataSource = fileName,
        Mode = SqliteOpenMode.ReadWriteCreate,
        Pooling = false,
    }.ToString();
    using (var mDbConnection = new SqliteConnection(connectionString))
    {
        mDbConnection.Open();

        foreach (var sqlCommand in _createTableSqlCommands)
        {
            using (var command = new SqliteCommand(sqlCommand, mDbConnection))
            {
                command.ExecuteNonQuery();
            }
        }

        mDbConnection.Close();
    }
}

Lactate