CODEWITHSUNDEEP
node.jssqlite
Vort3x
Vort3x

Reputation: 1828

Why am I getting node-sqlite3 SQLITE_BUSY

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.

Upvotes: 7

Views: 17368

Answers (4)

Ulad Melekh
Ulad Melekh

Reputation: 944

SQLite database works fine with just 1 transaction a time. So, if you have several concurrent requests to your DB, sometimes you will lock database.

SQLite has some very simple mechanism for a transaction to wait until another transaction is in progress, but it's not enough.

I solved the same problem in a similar way, as @MattShirilla suggested. You either need to use a queue or control flow to avoid locks for sure.

Upvotes: 1

Hill5Air
Hill5Air

Reputation: 889

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.

Upvotes: 3

Alex Jones
Alex Jones

Reputation: 156

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!

Upvotes: 13

hrbrchenko
hrbrchenko

Reputation: 13

Queries in cycle -- bad idea at all. Use https://github.com/mapbox/node-sqlite3/wiki/API#databaseexecsql-callback instead

SQLITE_BUSY: database is locked

I gives this messages when database already was opened by another program.

Upvotes: 0

Related Questions