SQLite locks the database even after closing the connection

Question

I am using the System.Data.SQLite provider in an ASP.NET application (framework 4.0). The problem I am facing is that when I insert something into the SQLite database table, the database is locked and the lock is not released even after the connection is established.

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

My code is pretty simple, I open the connection, read some data from the SQLServer database, paste this data into SQLite (via SQLiteDataAdapter), and then close the connection and delete everything to be safe. But still, I get this error when I try to fix the file after filling it with data.

I read all the suggestions here in StackOverflow, but none of them helped to solve the problem (turning off the antivirus, changing the transaction model, waiting a few seconds before fixing the file, wrapping all the inserts in the transaction, etc., but no one helped solve this problem.

Maybe something special for ASP.NET (is multithreading a problem? Although I am testing it on a development machine where there is only one call to this function and concurrency?)

As a side note, I tried to avoid the DataTable and SQLiteDataAdapter and use only SQLiteCommand directly, and this way it works. Of course, I can continue to build my queries as strings instead of using data adapters, but it seems to me that this is a little inconvenient when there is an infrastructure created for this.

Answer 1

I had the same problem using datasets / table adapters generated using the constructor supplied with System.Data.Sqlite.dll version 1.0.82.0 - after closing the connection, we were not able to read the database file using System.IO.FileStream . I managed the connection and tables correctly, and I did not use the connection pool.

According to my first search queries (like this and this thread ), which seemed to be a problem in the library itself - either objects that were not correctly released, and / or problems with the pool (which I do not use).

After reading your question, I tried to replicate the problem using only SQLiteCommand objects, and found that the problem occurs when you do not destroy them. Update 2012-11-27 19:37 UTC : this ticket is confirmed for System.Data. SQLite, in which the developer explains that "all SQLiteCommand and SQLiteDataReader objects associated with the connection [must be] correctly placed."

Then I returned to the generated table adapters, and I saw that the implementation of the Dispose method was not implemented, so in fact the created commands were not deleted. I implemented this, taking care of recycling all the commands, and I have no problem.

Here's the code in C #, hope this helps. Note that the code is converted from original to 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 changelog on the official site System.Data.SQLite, starting from version 1.0.84.0, the above code is not required, since the library will take care of this. I have not 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(); } 

• on the implementation of the Dispose TableAdapter : it’s better to put this in a partial class so that the regeneration of the data set will not affect this code (and any additional code you may need to add).

Answer 2

I have the same problem. My scenario was that after receiving the data in the SQLite database file, I want to delete this file, but it always gives the error " ... using a different process ." Even I delete SqliteConnection or SqliteCommand, the error still occurs. I fixed the error by calling GC.Collect() .

Code snippet

 public void DisposeSQLite() { SQLiteConnection.Dispose(); SQLiteCommand.Dispose(); GC.Collect(); } 

I hope for this help.

Answer 3

In my case, I created SQLiteCommand objects without using them explicitly.

 var command = connection.CreateCommand(); command.CommandText = commandText; value = command.ExecuteScalar(); 

I wrapped my command in a using statement and fixed my problem.

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

Answer 4

The following worked for me: MySQLiteConnection.Close(); SQLite.SQLiteConnection.ClearAllPools() MySQLiteConnection.Close(); SQLite.SQLiteConnection.ClearAllPools()

Answer 5

In most cases, the problem arises if you do not properly manage your readers and teams. There is a scenario in which commands and readers will not be placed correctly.

Scenario 1: If you use the boolean function. until the result is reached, the code in the finally block will not be canceled. This is a big problem if you are going to evaluate the results of the isDataExists function when executing the code, if it matches the result ie

  if(isDataExists){ // execute some code } 

Function to be 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: Remove 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, remove the reader and command just in case something went wrong.

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

Answer 6

I found that edymtt correctly answered the TableAdapters / Datasets accusation, but instead of modifying the newly created TableAdapter code file each time, I found another solution: manually invoke. Set TableAdapter to 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()); } 

Answer 7

As stated earlier, SQLite objects must be destroyed. However, there is a strange behavior: the connection should be open during a call to 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 lock file

Answer 8

This was one of the best Google results I found when I encountered this error. However, none of the answers helped me so much after further searching and searching on Google. I came up with this code that works from some 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 NuGet at all. What my program does is download the db file from the server every time I open it. Then, if the user updates this db, he will be downloaded for everyone, so that next time he will open the same program. I get an error message that the file used after updating the local file and trying to upload it to our SharePoint. Now it works great.

 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 

Answer 9

Ensuring the proper removal of any IDisposable (e.g. SQLiteConnection, SQLiteCommand, etc.) solves this problem. I have to repeat the repetition of the need to use “use” as a habit to ensure that disposable resources are properly disposed of.

Answer 10

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

  SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder { Pooling = true };