Sqlite leaves connection open (Mono.Data.Sqlite.dll)

Question

I am using this library to query a sqlite database using mono:

https://www.mono-project.com/docs/database-access/providers/sqlite/

And this is how I use the library:

var dbLocation = "/var/log/asterisk/master.db";
var conString = $"Data Source={dbLocation}; Read Only=True; Pooling=True;";
using (var sqlite3 = new Mono.Data.Sqlite.SqliteConnection(conString)
{

       try
       {
                 sqlite3.Open();

                 // query database
                 using(var command = sqlite3.CreateCommand()){
                     command.CommandText = "select * from foo";
                     var reader = command.ExecuteReader();
                     // etc...

                     reader.Close(); // close reader
                 }
        }
        catch
        {
                     // log error
        }


}

Note this code executes every second. Another processes (Asterisk) is writing to the database and my processes is reading from the database. I cannot leave the connection open because if I do so Asterisk is not able to write to that database; therefore, I must open and close the connection every time I plan to query the database.

This code works great! I am able to query the database in read only mode. I created the connection string based on this link:

http://docs.go-mono.com/?link=T%3aMono.Data.Sqlite.SqliteConnection

This code works great but after 2 days my program crashes because sqlite.dll does not properly dispose the connection. sqlite.dll is leaving the connections open and here is the proof:

the process id of my application is 29140:

I then ran the command lsof -a -p 29140 and that returns me something like this:

COMMAND   PID USER   FD   TYPE  DEVICE SIZE/OFF     NODE NAME
Main    29140 root  cwd    DIR     8,1     4096 46137345 /root
Main    29140 root  rtd    DIR     8,1     4096        2 /
Main    29140 root  txt    REG     8,1  3845024 12586404 /usr/bin/mono-sgen
Main  ........
... etc
.... etc
Main    29140 root   74r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   75r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   76r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   77r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   78r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   79r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   80r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   81r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   82r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   83r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   84r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db
Main    29140 root   85r   REG     8,1 39170048 49023734 /var/log/asterisk/master.db

Because I query the database every second I would expect to create a "file lock" to /var/log/asterisk/master.db every second. This happens randomly! If the database is being used a lot I see an increase in the number of /var/log/asterisk/master.db. How can I properly dispose this sqlite connection?

Things I have tried:

Calling GC.Collect() and GC.WaitForPendingFinalizers() as this answers suggests: https://stackoverflow.com/a/8513453/637142

You're already disposing it; however, many ADO.NET providers implement *connection pooling* to improve performance; check whether your ADO.NET provider (i.e. the specific `DbConnection` type) has controls for this - it usually means tweaking the connection string to explicitly *disable* connection pooling - for example, with `SqlClient` (SQL Server) it would be achieved by setting the `Pooling` property to `false` in the connection string — Marc Gravell, May 01 '19 at 17:44
Are you disposing the Command that you created? (Each SQLite level prepare needs to have a finalize) — SushiHangover, May 01 '19 at 17:47
Yes my rule is to place everything that is disposable inside a using statement @SushiHangover. So yes everything is being disposed. — Tono Nam, May 01 '19 at 17:55
@TonoNam I see this `var command = sqlite3.CreateCommand();` in your code sample, but do not see a dispose on it... (using blocks do not auto-dispose instances created within the block....) — SushiHangover, May 01 '19 at 17:59
My real code is just harder to read. It contains the real query and it might take more time to read. Let me update the question to have the dispose like you said. My real code has the using statement. . @SushiHangover — Tono Nam, May 01 '19 at 18:01

score 0 · Accepted Answer · answered May 01 '19 at 18:40

I changed connection string to : (Poling=False)

var conString = $"Data Source={dbLocation}; Read Only=True; Pooling=False;";

and I also closed my reader as:

    // using very important!
    using(var command = sqlite3.CreateCommand())
    {
                 command.CommandText = "select * from foo";
                 var reader = command.ExecuteReader();
                 // etc...

                 reader.Close(); // close reader! <----- this is very important for some reason
    }

This has been running for one hour and the count has not increased. I hope this is the solution.

Sqlite leaves connection open (Mono.Data.Sqlite.dll)

Things I have tried:

1 Answers1