Node-sqlite3: Handle SQLITE_BUSY gracefully

[kkaefer]

[angelochen960]

have a similar issue, two node apps:

1. app1, writes
2. app2, queries only, file is opened:var db = new sqlite3.Database(log_db, sqlite3.OPEN_READONLY);
   app1 gets following error if app2 runs:
   SQLITE_BUSY: database is locked

[kkaefer]

@angelochen960, try running PRAGMA journal_mode = WAL; (See http://www.sqlite.org/pragma.html#pragma_journal_mode). This requires SQLite 3.7.

[angelochen960]

the sqlite and dev installed is 3.3, this option is not doable. is there a way to check if db is locked before starting the db.run("insert into..."), if it is locked, then i can just avoid insert statement.

[kkaefer]

3.3 is ancient and I do not recommend running it. The minimum supported version for node-sqlite3 is 3.6, so I'm surprised that it even compiles. When you clone the git repository (there's no release in npm yet), you can run ./configure --internal-sqlite to use a more recent version of sqlite3.

[PherricOxide]

I'm also having issues related to this. It would be great if there was a way we could specify a number of query attempts and let the library handle multiple attempts if it gets SQLITE_BUSY, instead of having to deal with it in client code (especially annoying in asynch architecture).

[nicStuff]

Yes, facing the same problem: it would be great to act on it with some high-level configuration on the library.

[saraf]

What is the recommended way to deal with SQLITE_BUSY?

Writing a test suite for a cloud based API. I need to seed a database with fake user data - with part of the data coming from an API server - (the API key and secret are received from the API server after registering with the fake generated user details)

When trying to write records to a file using sqlite3 with node, I tried writing the logic in a naive manner, without any error checks - but it was failing with numerous SQLITE_BUSY database locked errors.

Then I added code to retry in case of an error, but was insufficient - ended up with corrupt tables, and so I added transactions as suggested in #node.js on freenode.

Now, it works. However the problem is that it is pathetically slow - takes up to 5 minutes for around 50 records. Also, I need to pass in a retries parameter of roughly number_of_users * 2 in order for the process to complete successfully or else it bails out. Something is not correct, but I cannot pin it down. SQLite3 performance cannot be this slow.

What is wrong?

TestController.prototype.connectToDb = function () {
    var db = new sqlite3.Database(this.dbpath);
    return db;
}

TestController.prototype.writeUserToLocalDatabase = function (retries,username,useremail,password,api_key,api_secret, callback_after_all_users_have_been_added) {
    var dbase = this.connectToDb();
    var controller = this;

    if (retries < 0) {
       console.log("Bailing out of writeUserToLocalDatabase after max retry attempts");
       return;
    }

    dbase.run("BEGIN TRANSACTION");
    //console.log("Try #"+retries);
    var stmt = dbase.prepare("insert into user_table values(?,?,?,?,?)", function(err) {
        if(err !== null) {
          console.log("Error when trying to write user to database in prepare! "+err);
          dbase.run("ROLLBACK");
          dbase.close(function(err){if(err !== null) {console.log("Close failed in in prepare "+err);}});
          controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret, callback_after_all_users_have_been_added);
       }
    });

    stmt.run(username,useremail,password,api_key,api_secret, function(err) {
       if(err !== null) {
          //console.log("Error when trying to write user to database in run! "+err);
          stmt.finalize();
          dbase.run("ROLLBACK");
          dbase.close(function(err){if(err !== null) {console.log("Close failed in in run "+err);}});
          controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret, callback_after_all_users_have_been_added);
       }
       else {
          stmt.finalize(function(err) {
             if(err !== null && err !== undefined) {
                console.log("Error when trying to write user to database in finalize ! "+err);
                dbase.run("ROLLBACK");
                dbase.close(function(err){if(err !== null) {console.log("Close failed in in finalize "+err);}});
                controller.writeUserToLocalDatabase(--retries,username,useremail,password,api_key,api_secret, callback_after_all_users_have_been_added);
             }
             else {
                dbase.run("COMMIT");
                controller.appendToUserFile(useremail,password,api_key,api_secret);
                controller.pending_db_writes_for_user--;
                if (controller.pending_db_writes_for_user <= 0) {
                   console.log("Finished writing all users, now invoking db.close with callback");
                   dbase.close(callback_after_all_users_have_been_added);
                }
                else {
                   console.log("Remaining users to add: "+controller.pending_db_writes_for_user);
                   dbase.close(function(err){if(err !== null) {console.log("Close failed in after adding user "+err);}});
                }
             }
          });
       }
    });
}

[michael-ts]

Um... why can't we use SQLite's busy_handler?

[guruz]

With a busy handler you'd potentially lock up the thread for a long time. Depends I guess..

[wmertens]

I'm encountering this too with WAL turned on, between two different processes, one of which is doing a really long transaction, the other is only reading.

I also call db.configure('busyTimeout', 15000) after opening, but that makes no difference.

It was my understanding that WAL + busyTimeout should eliminate these errors?

[wmertens]

oops never mind, I didn't enable WAL mode headdesk

[NxtChg]

I am using WAL and I still get the BUSY error.

It seems neither
db.configure('busyTimeout', 5000);
nor
db.run('PRAGMA busy_timeout = 5000');
work.

The timeout is never activated and trying to start a transaction fails immediately.

Any ideas?

[wmertens]

The busy timeout is handled by the CLI - this module AFAIK doesn't install
a busy handler and so you have to write your own thing that does polling to
start a transaction.

On Wed, Jan 3, 2018 at 10:07 AM NxtChg [e-mail] wrote:

I am using WAL and I still get the BUSY error.

It seems neither
db.configure('busyTimeout', 5000);
nor
db.run('PRAGMA busy_timeout = 5000');
work.

The timeout is never activated and trying to start a transaction fails
immediately.

Any ideas?

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
https://github.com/mapbox/node-sqlite3/issues/9#issuecomment-354964567,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AADWlh-Jk6_vebaPXomieKAoPJO2YDWlks5tG0NEgaJpZM4AMdDM
.

[irsl]

What is the point of exposing the busyTimeout configuration option when the driver does not feature a handler? The source code contains no reference to sqlite3_busy_handler.

This is just misleading.

[rahuljayaraman]

Configuring busy_timeout will make sqlite retry individual queries. ButSQLITE_BUSY errors may pop up (without waiting) with busy_timeout configured as well. In WAL mode, it might mean a BUSY_SNAPSHOT error and in Rollback journal mode, it might mean transactions are deadlocked. In both cases, re-trying individual queries does not help & transactions have to be rolled back & re-tried.

If anyone's interested, I penned down my understanding of SQLITE_BUSY in an article after facing similar issues recently.
https://www.activesphere.com/blog/2018/12/24/understanding-sqlite-busy
I would appreciate feedback on this as well ^ (mistakes, topics which need more clarity etc.)