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

Answer 1

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.