Stuck query after calling `serialize()` and `configure()`

[fflorent]

Issue Summary

When calling successively (assuming your database is effectively open):

1. db.serialize() to execute queries sequentially;
2. db.configure() to change settings to the sqlite connection ¹;
3. db.exec() to run some query;

Then the last query is never run, we're stuck.

A workaround we identified consists in calling db.wait(callback) before step 3, and run db.exec() in the callback.

Steps to Reproduce

Here is a script to reproduce it (credits to @jonathanperret):

const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(":memory:");
const { setTimeout: setTimeoutPromise } = require('node:timers/promises');

const LONG_QUERY = `WITH recursive recur(n)
        AS (SELECT 1
        UNION ALL
        SELECT n + 1
        FROM recur where n < 1000000
        )
        SELECT n FROM recur;`;

db.on('open', async () => {
    db.serialize();

    await setTimeoutPromise(100);
    
    // initial condition: all queues empty

    db.exec(LONG_QUERY);        // push Exec on thread pool queue
    
    // - Work_BeginExec called synchronously
    // - Work_Exec added in thread pool queue
    // - db->locked = true
    // - db->pending = 1

    // Workaround: call db.wait() just before db.configure()
    //   console.time("wait"); await new Promise(r => db.wait(() => { console.timeEnd("wait"); r() }));

    db.configure('limit', sqlite3.LIMIT_ATTACHED, 1);  // push SetLimit on node-sqlite queue

    // because serialize == true && pending > 0, SetLimit goes to node-sqlite queue
    // Process() called but (!locked || pending == 0) is false so does nothing

    db.exec("SELECT 1", () => { console.log("SUCCESS"); process.exit(0); });     // push Exec on node-sqlite queue

    // - second Work_Exec added in thread pool queue

    // - first Work_Exec (SELECT 0) starts and completes in thread pool
    // - first Work_AfterExec runs, calls its callback then Process()
    // - Process() pops SetLimit off queue, calls it
    // - SetLimit() returns, Process() exits because locked is still true

    // At this stage the node-sqlite queue still contains the second Work_BeginExec,
    // but there is no-one to call Process() again so it remains there.

    await setTimeoutPromise(1000);

    console.error("This is taking too long! Are we stuck?");
    process.exit(1);
});

Version

v5.1.7

Node.js Version

v22.12.0

How did you install the library?

npm install sqlite3@5.1.7

Footnotes

1. Could also be triggered by a call to db.on("trace", ...) or db.on("profile", ...) and other methods as well, which also call internally Database::Configure. ↩