Error SQLITE_BUSY immediately after opening database

[Aehmlo]

The following code gives me the output Error when updating recipe table: { [Error: SQLITE_BUSY: database is locked] errno: 5, code: 'SQLITE_BUSY' }:

var change = function(req, res) {
    var id = req.param("recipe");
    var db = new sqlite3.Database(path.join(__dirname, "recipes.sqlite"), function(err) {
        if(err) {
            console.log(err);
            return db.close(function(err) {
                res.status(500).send("Could not open database.");
            });
        }
        db.configure("busyTimeout", 60000); //Did this just to double-check everything. Even with this here, the error persists.
        var recipe = req.body.recipe;
        var shortname = null;
        db.run("UPDATE recipes SET name=?, shortname=?, prep_time=?, cook_time=?, serves=? WHERE id=?", recipe.name, shortname, recipe.prep, recipe.cook, recipe.serves, id, function(error) { //Was previously using statements here, not that it really matters.
            if(error) {
                console.log("Error when updating recipe table:", error);
                return db.close(function(err) {
                    res.status(500).send("Error modifying recipe.");
                });
            }
            //...

I am using Express, and this database is not open anywhere else in the app. I'm not sure what would cause such an issue - I do not have this problem elsewhere (where I use it for such things as fetching recipe information - and I close the database before serving every page, so it isn't an artifact from those other pages).

Recipe table schema, in case it's helpful: CREATE TABLE IF NOT EXISTS recipes(id INTEGER PRIMARY KEY, name TEXT NOT NULL, author TEXT DEFAULT NULL, notes TEXT DEFAULT NULL, shortname TEXT DEFAULT NULL, prep_time INT DEFAULT 0, cook_time INT DEFAULT 0, serves INT DEFAULT NULL);

[ErisDS]

I wanted to drop by and mention a similar, possibly related issue we've seen in Ghost.

We've recently started seeing the same error on our sqlite3 builds on Travis. It only happens on node 0.12.* and io.js 1.2, not on the 0.10 build. It fails roughly 2 out of every 3 tries, so we've just been restarting builds for a few weeks :flushed:. It's happening in any one of a set of integration tests which load a reasonable chunk of data in and out of the DB to test our JSON importer.

I've only just started to debug the issue, and I wasn't sure where to start seeing as we've updated pretty much everything recently (sqlite, mocha, etc). Not sure if this is the same issue, but it is the same error. The way this occurs also points at it being something to do with SQLite on more recent node versions, but it could easily be something going wrong with mocha/async. If there's any more useful info I can provide to help track this down let me know.

[springmeyer]

@ErisDS Thanks for dropping by. Let us know if you are able to reduce a testcase. /cc @kkaefer

[springmeyer]

@Aehmlo - looks like https://github.com/TryGhost/Ghost/issues/5411 fixed this problem by avoiding multiple connections to the same db. Can you comment to see if that is your problem too?

[lchacha]

my team has also experienced the problem when we have too many get requests to the smae db.

[kkaefer]

You should limit your application to one connection per database file. It can't write faster anyway.

[andrewharvey]

You should limit your application to one connection per database file. It can't write faster anyway.

Ah thanks that saved me. Since node is single threaded only, in my application I had a node instance running for each CPU core but they were all using this library to write to the same database file. I had read that sqlite3 could handle concurrency and the database would use locking or the WAL to ensure consistency, but it appears this is not 100% true.

[raomaneesh]

I have cron jobs that can fire at same time and inserts into same table in same database which results in SQLITE_BUSY error many times. @andrewharvey , @ErisDS Does having only single connection for single database file helped to resolve this SQLITE_BUSY issue for you?? And does it have any negative impacts if it the connection is kept open forever and not closing after every query?? Also do we need to do commit() explicitly after every query because we are not closing the connection ?? I have tried using single connection and it seems to be working fine for now but not sure if this is perfect solution without any pitfalls?? Any suggestion or experience that anybody has had will help..!!

[md-fahad-ali32]

hello i also fetch this issue please help me i am begineer