1

I recently implemented Node cluster as a way to vertically scale my application. Since it uses multiple threads now I naturally added stress tests to the application and that revealed an issue with my application.

So for the sake of this question I will really simplify my setup. I have Node clustered app and each one of children processes is accessing the same SQLite file. I know that is not the ideal solution but to my knowledge when concurrent queries try to access it the latter one should wait for the database to become free. Unfortunately, this is not happening.

I am getting the following error

Error: SQLITE_BUSY: database is locked
errno: 5, code: 'SQLITE_BUSY'

My configuration is 

"sqlite": {
  "client": "sqlite3",
  "connection": {
    "filename": "app/database/database.sqlite"
  },
  "useNullAsDefault" : true,
  "pool": {
    "min": 1,
    "max": 1 <- i tried increasing this to the number of cluster instances
  }
}

Is there a solution for this, other than removing the SQLite in general?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rouz
  • 1,247
  • 2
  • 15
  • 37
  • Wait a short while and try again. If Node bindings provide a way to call the native C `sqlite3_busy_timeout()` function this can be done automatically. Otherwise use [PRAGMA busy_timeout](https://www.sqlite.org/pragma.html#pragma_busy_timeout). – Shawn Apr 21 '19 at 20:23
  • ahm? wait for what? How long to wait? IMHO this should be done by the database connector: **wait in the queue for the lock to be lifted or die with timeout** Is there a way to achieve that with knex? – Rouz Apr 21 '19 at 20:25
  • How long to wait is up to you and the needs of the application. I usually use 250-500 milliseconds. – Shawn Apr 21 '19 at 20:29
  • Oh come on this was a rhetoric question. I obviously do not want to recover from the timeout error, and I would really hate to be forced to do that manually (wait and retry). I will show that as service unavailable error. The real question here would be - HOW to set busy timeout with knex. Can that be done? – Rouz Apr 21 '19 at 20:43

0 Answers0