ExecuteNonQuery Memory Leaks without using unmanaged code implementations

Question

I hate hate posting to ask for help. I can usually just figure this stuff out. I'm in over my head.

--How I arrived at my current conclusions (only read if you want my problem solving steps--

I discovered an application on which I was working had memory leaks. I endeavored to find them. I engaged in a process of trial-and-error removal of calls and functions until I eventually found a set of processes which appeared to be the culprit. It was a simple function which queried a sqlite database using an .ExecutableNonQuery(). More trial-and-error ensued to see if I could reproduce the error in a new function. I could. I tried to strip that function down to its absolute bare minimum and polished it to continue be more in line with best practices (as far as I can tell).

The problem persisted. Confused, I started trying to break the code altogether. I started taking out certain key components of the query to see if I could eliminate the memory leak. To be sure that the problem wasn't hidden somewhere else in the application, I built a new Unity Project with only one game object (no camera). It has one component on it (a C# script titled: Script; it's below). Nothing else. There are no other objects, scripts, functions, calls, references or anything in this project. The leak persists.

Here's the most minimal code I generated that works in full:

using System.Collections;
using System.Collections.Generic;
using UnityEngine;
using Mono.Data.Sqlite;
using System.Data;

public class Script : MonoBehaviour {

    string dbconnstring;

    void Start () {
        dbconnstring = "URI=file:" + Application.dataPath + "/civicismdb.sqlite";
    }

    void Update () {
        dbconnect();
    }

    void dbconnect()
    {
            using (IDbConnection conn = new SqliteConnection(dbconnstring))
            {
                using (IDbCommand cmd = conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"select 1";
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
        }
}

This is the most minimal query I could possibly have. Two things stop the memory leak. If I comment out:

cmd.ExecuteNonQuery();

Or I adjust the query to be empty:

cmd.CommandText = @"";

In the latter case, I suspect .ExecuteNonQuery isn't actually running, but I'm unsure. Moreover, I've created a cmd.CommandText and assigned it the query value at this line:

cmd.CommandText = cmd1.CommandText;

Same result. The memory leak persists, of course, because that wouldn't really make much sense as a fix. Whatever, due diligence...

I tried to create my connections and commands once and simply reference them, but 1) that really isn't optimal and 2) it doesn't work anyway. The leak persists.

I tried to ensure that my cmd and conn were disposed of. Again, that 1) didn't fix the problem, and 2) doesn't make much sense as a fix because the using statement should ensure that they are disposed of anyway. But, I tried. To no avail.

Though explicitly calling GC.Collect() is a bad idea, I tried that as well given that it might be creating more connections/commands than it could dispose of given the update function calling the DB. No dice.

Also, I know querying the DB every update is a bad idea. I'm doing that for illustrative purposes. It makes the leaks clearer. In reality, I'm querying it often as called by a coroutine.

Now, I know that coroutines could cause their own memory leaks, but when running this with a coroutine, the leak stops if I comment out the .ExecuteNonQuery().

--My conclusion--

ExecuteNonQuery is leaking. Somewhere/somehow under the hood.

--My questions --

Am I missing anything?

Is this a known issue and I failed to find it in my googling?

Do you see a solution?

Thank you for reading. This has been a long and frustrating experience and my posting here is my conceding defeat.

---

EDIT -

I'm learning that I think SQlite does memoryleak a little bit at each invoke but for most applications this is just not relevant because the database is queried so infrequently. I'm also learning that sqlite does a ton to provide options to developers to manage memory more efficiently. See here for example:

https://www.sqlite.org/malloc.html

So, this isn't really a Unity problem. It's a user-error when using SQlite problem.

As far as I can tell, what I need to be doing is using unmanaged code to manage the memory myself. I'm only just learning how to do this and am struggling mightily to figure out how specifically to change configuration options, manage the heap, finalize connections, etc. any insights are very much appreciated.

At the moment, I'm trying to figure out how to call the sqlite3_config option so that I can the configuration to MemSys5 instead of using malloc. No idea if that will actually solve the problem.

Answer 1

Alright, I think I finally have this sorted. when wrapping these in using statements, the idea is that the garbage collector (GC) will collect all the discarded commands and connections no longer in use. something about this implementation is preventing the GC from doing that. I don't know what or why. In theory this implementation should have been stable. It isn't. So here's the plan instead:

Generally it is silly to create a pool of connections to be cycled through because I think sqlite already pools connections for you. But, again... something in this design is stopping it from pooling properly. Either that or the individual commands themselves are inflating. Either way, I'm building a pool of connections, readers, and commands and building a function to cycle through them to be constantly reused dynamically. I'll also build in a query cue so that no two queries can query at the same time.

Basically, build a query+command+reader combo, send it to the cue. Cue grabs the next one off the stack and runs it. Returns the result or null. That appears to be robust to inflation as long as I'm not closing the collections or commands. The readers can be closed without inflation.