Why am I getting node-sqlite3 SQLITE_BUSY

Question

I have a problem with a webserver that stores data periodically in a node-sqlite3 driven database. It will work fine a few times and then just fails with

SQLITE_BUSY: database is locked

Sometimes all inserts are not completed to the database even when not failing, leaving only a portion of the inserts in the table.

setInterval(doInserts,60000);

doInserts() {
calculateData(function(data){
    if(data)
    {
        insertData(data);
    }
});
}

function insertData(data) {
var sqlite3 = require("sqlite3").verbose();

//user fs existSync to check if file exists and openSync(file,'w') to create if not

db.serialize(function(){
    var stmt = db.prepare("INSERT INTO mytable (col1,col2) values (?,?)");

    for(var i =0;i<data.length;i++)
        stmt.run(data[0],data[1]);
    stmt.finalize();
});
db.close();
}

Taking account I am new to both node and SQLite, I'm guessing that I am using something in the wrong way, causing multiple open threads or processes to lock up the db, but no documentation or research has gotten me any closer to a solution.

Answer 1

Just chipping in what solved it for me. This was happening whenever I had a data viewer application looking at the database. Once closed, it began working again!

Answer 2

I suspect you understand Node\Sqlite correctly. I had the same problem using Sqlite3#serialize. I ended up not using it in the end, and instead used the pattern in chapter 7.2.2 of Mixu's Node Book. to control the flow of the db queries. Basically you have an array of functions. Each function has a callback that invoke the next function.