[sqlite] High memory usage for in-memory db with nested transactions

[Brendan E. Coughlan]

Hello everybody,

The included C-Program takes a database file name as a command line 
parameter.

I compiled it with SQLite 3.19.3 under Visual Studio 2017.

If I supply test.db as a command line parameter / file name (and test.db 
doesn't exist yet) the size of test.db, the size of test.db.journal and 
the program's memory usage reach a combined maximum a little short of 2MB.

If I replace the supplied file name by :memory:, the program uses all 
available memory and eventually runs into a memory allocation error.

Am I doing something wrong and if yes what?

Program:

#include <stdio.h>
#include <stdlib.h>
#include "sqlite3.h"

void runOrCrash_str(sqlite3 *db, const char *sql)
{
    int err = sqlite3_exec(db, sql, NULL, NULL, NULL);
    if (SQLITE_OK != err)
    {
        printf("Error running %s: %s.", sql, sqlite3_errmsg(db));
        exit(1);
    }
}

void runOrCrash_stmt(sqlite3_stmt * const stmt)
{
    int err = sqlite3_reset(stmt);
    if (SQLITE_OK != err)
    {
        printf(
            "Error resetting %s: %s.",
            sqlite3_expanded_sql(stmt),
            sqlite3_errmsg(sqlite3_db_handle(stmt))
        );
        exit(1);
    }
    err = sqlite3_step(stmt);
    if (SQLITE_DONE != err)
    {
        printf(
            "Error running %s: %s.",
            sqlite3_expanded_sql(stmt),
            sqlite3_errmsg(sqlite3_db_handle(stmt))
        );
        exit(1);
    }
}

void finalizeOrCrash(sqlite3_stmt *stmt)
{
    int err = sqlite3_finalize(stmt);
    if (SQLITE_OK != err)
    {
        printf(
            "Error resetting %s: %s.",
            sqlite3_expanded_sql(stmt),
            sqlite3_errmsg(sqlite3_db_handle(stmt))
        );
        exit(1);
    }
}

sqlite3_stmt *getStmtOrCrash(sqlite3 *db, const char *sql)
{
    sqlite3_stmt *rv = NULL;
    int err = sqlite3_prepare_v2(db, sql, -1, &rv, NULL);
    if (SQLITE_OK != err)
    {
        printf("Error preparing statement %s: %s.", sql, 
sqlite3_errmsg(db));
        exit(1);
    }
    return rv;
}

int main(int argc, char* argv[])
{
    sqlite3 *db = NULL;
    int err = SQLITE_OK;

    if (2 == argc)
    {
        const char *fileName = argv[1];
        err = sqlite3_open(fileName, &db);
    }
    else
    {
        printf("The database file name should be the only command line 
parameter.");
        exit(1);
    }
    if (SQLITE_OK != err)
    {
        printf("Error opening the database: %d", err);
        exit(0);
    }

    runOrCrash_str(db, "CREATE TABLE bla(content INTEGER);");
    runOrCrash_str(db, "INSERT INTO Bla(content) VALUES(1)");
    sqlite3_stmt * sp2 = getStmtOrCrash(db, "SAVEPOINT SP2;");
    sqlite3_stmt * sp2Release = getStmtOrCrash(db, "RELEASE SP2;");
    sqlite3_stmt * update = getStmtOrCrash(db, "UPDATE bla SET content 
= 1;");

    runOrCrash_str(db, "BEGIN IMMEDIATE;");
    runOrCrash_str(db, "SAVEPOINT SP1;");


    for (int ii = 0; ii < 500000; ++ii)
    {
        runOrCrash_stmt(sp2);
        runOrCrash_stmt(update);
        runOrCrash_stmt(sp2Release);
    }


    runOrCrash_str(db, "RELEASE SP1;");
    runOrCrash_str(db, "COMMIT;");

    finalizeOrCrash(sp2);
    sp2 = NULL;
    finalizeOrCrash(sp2Release);
    sp2Release = NULL;
    finalizeOrCrash(update);
    update = NULL;
    err = sqlite3_close(db);
    if (SQLITE_OK != err)
    {
        printf("Error closing database: %s", sqlite3_errmsg(db));
        exit(1);
    }
    db = NULL;

    return 0;
}

Thanks in advance,
Brendan E. Coughlan


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users