Memory leak issue when querying SpatiaLite database continuously from multiple threads in C++

[hr_may]

Hello everyone,

I am currently facing a memory leak issue when performing continuous queries on a SpatiaLite database from my C++ program. Let me describe my setup and problem in detail.

In the main thread, I open the database and initialize SpatiaLite using the following code:

int rc = sqlite3_open_v2(dbName, &db, SQLITE_OPEN_READONLY, nullptr);
if (rc == SQLITE_OK) {
    this->dbExit = true;
}
if (enableExtension) {
    spatialite_init_geos();
}
cache = spatialite_alloc_connection();
spatialite_init_ex(db, cache, 0);

However, the actual queries are executed in a newly created thread, for example:

std::thread drawThread([this, globeControl, corners]() {
    std::vector<std::vector<int>> array;
    for (const auto &layerValue : gestureFeedbackCase->layerCasesMap) {
        layerValue.second->startControl(globeControl, corners);
    }
    update(globeControl, globeControl->getVisibleCorners());
});
drawThread.detach();

Inside the startControl method, I query the database like this:

std::vector<Airport> airports;

if (this->dbExit) {
    std::string sql = "...";  // simplified for clarity

    sqlite3_stmt *stmt;
    int rc = sqlite3_prepare_v2(db, sql.c_str(), -1, &stmt, nullptr);
    rc = sqlite3_bind_text(stmt, 1, "Y",-1,SQLITE_STATIC);
    rc = sqlite3_bind_text(stmt, 2, "MA",-1,SQLITE_STATIC);

    if (rc != SQLITE_OK) {
        return airports;
    }
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        Airport airport;
        fillAirportFromSql(stmt, airport);
        airports.push_back(airport);
    }
    sqlite3_finalize(stmt);
}

return airports;

Since these queries run continuously, I very quickly encounter memory exhaustion.
When I checked the SpatiaLite documentation, I found that this approach may cause the spatial cache not to be released. On the other hand, the documentation also says that spatialite_cleanup_ex() should only be used when closing the database, which does not seem realistic for my use case because I need to keep the database connection open for ongoing queries.

So my questions are:

1. What is the actual root cause of the memory leak in my scenario?

2. For my use case (continuous queries from a long-lived connection), what is the correct way to use SpatiaLite to avoid memory leaks?

Any guidance would be greatly appreciated.

Thanks in advance,
by may

[sandro furieri]

Il 2025-09-09 10:39 hr_may ha scritto:
> Since these queries run continuously, I very quickly encounter memory
> exhaustion.
>

> So my questions are:
> * What is the actual root cause of the memory leak in my scenario?
>

hy May,

it's virtually impossible to give you an answer based on the limited
details provided. Especially because the problem could be caused by
other parts of your code that are only that only indirectly relate
to SQLite or SpatiaLite.

In these cases, it's always highly recommended to use some tool for
dynamic memory analysis, which is the only way to identify the real
cause of any type of memory leak.

If you develop on Linux you can use the excellent Valgrind which is
not complicated at all.
You can find the basic instructions to get started here:
https://valgrind.org/docs/manual/quick-start.html

I believe MS Visual C++ also supports a substantially similar
memory analyzer; I don't know what to tell you about other environments.


> * For my use case (continuous queries from a long-lived connection),

> what is the correct way to use SpatiaLite to avoid memory leaks?
>

I've personally developed many server-side apps that churn out
countless SQLite+SpatiaLite queries one after the other, and
I can assure you that they run continuously for many months
without ever showing symptoms of memory exhaustion.
This is normal; it's exactly what you expect from a DBMS.

Generally speaking, it's not a good idea to share a single
SQLite connection across multiple threads.
Personally, when I absolutely need to manage multiple threads,
I always open a new connection for each individual thread.

It's not just a code stability issue; it's even more of
an efficiency issue.
SQLite isn't designed to operate in parallel; it achieves
this through some acrobatics, but behind the scenes it makes
generous use of mutexes and semaphores to ensure that only
a single operation is running at any given time.
Sharing a single connection across multiple threads
ultimately offers no real benefit, but it can easily
create a lot of unexpected troubles.

bye Sandro

[hr_may]

Hi Sandro, 

Thank you very much for your reply!
Your suggestions are extremely valuable to us. I will next try to use a single thread for both database connection and query operations, and also make use of the memory analysis tools you mentioned.

Lastly, please allow me to express my sincere admiration for your work — SpatiaLite is undoubtedly outstanding, and in my field (civil aviation) it has been of tremendous help to us.

by may