How can I access db from two different processes?

(1) By chovy (chovyfu) on 2022-07-13 06:08:32 [link] [source]

I'm using sqlite library for Deno and from what I've read, you cannot have more than one connection to db even if from separate processes.

I have my api which is a standard rest api. That works fine.

But I have a cronjob which runs periodically and takes about an hour to run, when this runs the api is non-respnosive despite being two separate scripts.

Is there anyway around this?

I tried setting await db.query('PRAGMA busy_timeout=30000'); but that didn't solve the problem.

I am somewhat surprised if this is indeed the case that you cannot have multiple connections simultaneously.

(2) By Stephan Beal (stephan) on 2022-07-13 08:59:18 in reply to 1 [link] [source]

I'm using sqlite library for Deno and from what I've read, you cannot have more than one connection to db even if from separate processes

What you're seeing is not a limitation of sqlite. This very forum uses sqlite and has lots of processes accessing the db. Perhaps your app is opening an exclusive transaction at the start or perhaps deno, whatever deno is, is doing something boneheaded behind the scenes (sqlite wrappers and proxies have been known to try to be "too clever" and break it in the process).

(3) By Gunter Hick (gunter_hick) on 2022-07-13 09:05:52 in reply to 1 [link] [source]

SQLite suports multiple connections, both from threads within the same process and from several processes.

What it does not support is more than one write transaction simultaneously. With WAL mode, multiple readers and ONE writer can peacefully coexist.

I guess you are negelcting to manage your transactions properly. Split up your cronjob to perform shorter transactions, so that the application can read/modify data in between.

(4) By Gunter Hick (gunter_hick) on 2022-07-13 09:12:48 in reply to 1 [link] [source]

This sounds like a crippling disadvantage: "due to limitations in Denos file system APIs, SQLite can't acquire file locks or memory map files"

Direct your questions at the Deno and Deno-sqlite developers, they are responsible for whatever mess this is.

(5) By chovy (chovyfu) on 2022-07-13 09:32:41 in reply to 4 [source]

Where do you see that?

(6) By Ryan Smith (cuz) on 2022-07-13 09:41:29 in reply to 5 [link] [source]

Probably from the same place we all saw it when looking up "Deno" from your post, the GIT for it, here: deno.land/x/sqlite@v2.4.2

You can find that wording and other important notes in the lower section on "Comparison to Plugin based Modules" here: deno.land/x/sqlite@v2.4.2#comparison-to-plugin-based-modules

(7) By Donal Fellows (dkfellows) on 2022-07-19 07:27:39 in reply to 6 [link] [source]

The troublesome phrasing is also present in the current version, 3.4.0.

Disadvantages

Weaker Persistence Guarantees: due to limitations in Denos file system APIs, SQLite can't acquire file locks or memory map files (e.g. this module does not support WAL mode)

There is apparently experimental support for file locking, but not for anything like mmap(), so maybe that situation will be possibly improved. It's not the SQLite core team's job to do anything about it however.