SQLite Forum

Sqlite3 is multi-core multi-OS system
Login

Sqlite3 is multi-core multi-OS system

(1) By anonymous on 2020-04-24 11:22:15 [link]

Hi,

Is it possible to use sqlite3 is multi-core multi-OS system?

For e.g. we have a dual core system on which Linux is running on Core-1 & RTOS on Core-2.

And applications running on both cores need to access database.

Can this be achieved using sqlite3?

Can we have a shared sqlite3 database between both cores?

Any help/leads are appreciated.

Thanks

(2) By Warren Young (wyoung) on 2020-04-24 20:42:14 in reply to 1 [link]

> applications running on both cores need to access database.

Whether that works well (or even at all!) depends on the concurrency modes you choose and the filesystem you use.

For instance, the [WAL mode][1] has different concurrency behavior than the [legacy locking behavior][2]. Also involved are [thread-safe build-time options and application runtime choices][3].

Then there's the question of whether your RTOS can even link to and call the SQLite library in a way that allows it to interoperate with all of the above. For instance, if you store your data on a FAT32 SD card you [probably have no file locking at all][4], and even if you do have a Linux-side method of adding file locking to FAT32, your RTOS's FAT driver might ignore this mechanism entirely, blindly assuming that all files on the SD card belong unconditionally to the RTOS.


[1]: https://sqlite.org/wal.html
[2]: https://sqlite.org/lockingv3.html
[3]: https://sqlite.org/threadsafe.html
[4]: https://www.sqlite.org/faq.html#q5

(3) By Warren Young (wyoung) on 2020-04-24 20:45:33 in reply to 2 [link]

One more thought: does "RTOS" even belong in the same sentence as "persistent database?"

If you don't see the point, answer me this: what is the I/O latency of this query:

      UPDATE MyData SET field=1 WHERE id=42

If you think it's well-bounded, I'm going to guess that you're new at this. :)

(4) By Warren Young (wyoung) on 2020-04-24 20:57:46 in reply to 3

All of the above is fairly negative, so how about a positive suggestion? Move all persistent I/O stuff from the RTOS to the Linux side.

For instance, if your RTOS is collecting data from a sensor, don't have the RTOS write the data to SQLite. Instead, push it into a shared-memory [message queue][1] consumed on the Linux side, which persists the records in batches.

That transforms the problem from unbounded SD I/O times with SQL tree rebalancing, reindexing, and such thrown in to one of hard-bound RAM write times. With a suitable locking structure, you probably don't even have cross-core locking times to worry about.

I realize that none of this may apply to your application, but that just means you need to share more details of what you're trying to accomplish if you want better feedback. :)


[1]: https://en.wikipedia.org/wiki/Message_queue

(5.1) By Keith Medcalf (kmedcalf) on 2020-04-24 22:14:40 edited from 5.0 in reply to 1 [link]

SQLite3 is a library.  Everything it does executes within the thread that you provide when you made a call into the library.  Hence if this thread is executing on Processor 1, then the library code will execute on Processor 1.  If the thread making the call is executing on Saturn, then the library code will execute on Saturn.

The only possible exception to this is if you have compiled the library to use a mode other than single-threaded and you have enabled `worker_threads` in which case the library may spin up worker threads to help process sort and index operations. The request for a "thread" will be made the the "Operating System" which owns the current thread of execution and will execute wherever the "Operating System" (being in charge of such things) dispatches it to execute.  You may set the number of worker_threads to zero either at compile time or in a pragma (per connection) at runtime.

As a library, SQLite3 neither knows nor cares where it is executing.  It is no different than any other library (zlib, glibc, etc) in that that knowledge is beyond the scope and control of the library and is in the charge of the Operating System under which the application that is linked with the library is running.

Using the library under Linux should be no problem.  Whether or not you can compile the library for your RTOS is another issue.

(6) By anonymous on 2020-04-27 05:50:29 in reply to 4 [link]

Thanks a lot for your time & reply.
We are at a very early stages of finalizing the HW & SW design of our product.

So it is completely in our hands to design the solution in a way it works the best in terms of performance .

I needed these inputs to decide how to design our DB solution & think that your inputs will help me in doing so.