The use case:

1. remove thread safe when compile sqlite
2. create multi connection for each thread
3. every thread sqlite connection instance open they own database
4. only use memvfs, and no memvfs is shared
5. only one thread can write, all other thread open with readonly flags

Is this safe ?

If this is not safe then I will try wasm, build sqlite into wasm and create instance inside wasm vm instance for each thread.

Your items 3 and 5 seem to be mutually exclusive. If each thread connects to a different database, how do "readonly" threads get hold of data from the "writer" thread?

"Single-thread. In this mode, all mutexes are disabled and SQLite is unsafe to use in more than a single thread at once."

YOU have to MAKE SURE that no more than 1 thread is inside the library at any time when using single-thread mode. Your items 2 thru 5 are unsuitable to achieve this.

Your items 3 and 5 seem to be mutually exclusive. If each thread connects to a different database, how do "readonly" threads get hold of data from the "writer" thread?

It dose not hold of data from writer, every thread get they own memvfs object. there is no memvfs is shared

YOU have to MAKE SURE that no more than 1 thread is inside the library at any time when using single-thread mode. Your items 2 thru 5 are unsuitable to achieve this.

shared cache is disabled and memory database opened without name. I guess in this case no global vars is required.

If there is some global vars is used by sqlite in this case, the system will not work.

Why do you ask? Just compile SQLite for maximum thread safety and you should be fine. Why do you want to "remove thread safe when compile sqlite"? It almost sounds like you are falling into the premature optimization trap.

I want to avoid lock in main thread event loop.

I create my own memvfs with wal mode.

In main thread, when I get a readonly query task, I will create a new SQLITE instance with main thread writer memvfs by ref, and increase reader atomic counter.

Then in the worker thread do the read transaction.

In the read thread memvfs object, if I need write into a page I will create a thread local copy to save the change.

If I need read from worker memvfs page, I first check if there is a local page, if not exits then I will read the main thread memvfs page.

when worker read transaction is finished, I will decrease the reader atomic counter, so main thread can do the checkpoint if the counter is zero.

The design is to avoid lock since there is a RAFT log running in main thread, If
main thread locked without response the RAFT group main think the node offline.

Only in the main thread I can write into database, and only do checkpoint if there is no reader alive.

This design is for HA and high QPS.

There is a global unique ID come from RAFT group and will write into database, it will be used as optimistic lock to avoid read old data from system.

All write into system is simple and quick, but some read will use GROUP BY and some time cost query.

I suspect that your view of this problem suffers from (too) narrowly considering database access as read-only or modifying operations. That is valid with respect to the database itself, but in order to access it memory allocations must be done, and certain global (to the library) data structures must be setup. Those operations present the possibility of going awry when attempted by multiple threads without exclusion. And when you eliminate the possibility of "lock" by building the library with do-nothing replacements for the exclusion functions, you must either take responsibility for the necessary exclusion, avoid the need for it, or accept the chaos that results from doing neither.

the main thread calling sqlite3_open() to "create a new instance" any number of worker threads calling sqlite3_step() with a connection passed from the main thread

This seems cloud work, I am not sure if there will be any lock when operate in the reader database. (I want avoid long time lock in main thread)

Why not move the writes to a dedicated writer thread? Then there would be no lock in the main thread and SQLite could be compiled and run in standard multi-thread mode.

There is some logic need to handle from RAFT and business logic with writer, bring new thread will require some synchronization mechanism. (for example a business cache need to sync)

Thanks for the explain.

The original idea is to create a WASM vm to do used in worker thread.

The WASM vm will build into AOT mode, or use wasm2c to improve speed. each vm will have they own memory space.

My question is with my solution will still break the main writer for sure?

Is this safe ?

The details of how (some/most) operating systems implement locking mean that this is categorically not safe.

If you intend to access the database from multiple threads in a process, and any of those threads can modify the DB, then you should use a thread-safe build. It's probably best to just always use a thread-safe build and pretend that the other alternative doesn't exist.

Having multiple threads do writes is fine though, provided:

1. Transactions that include a write are usually short. For a lot of database use cases, this is indeed the case.
2. You make sure that any transaction that includes a write is began in immediate mode. Without this, you can get some really horrible deadlocks (e.g., when two threads with read locks both decide to upgrade their locks to write locks at about the same time). Lock upgrading is bad when write locks are exclusive.

I use SQLite read-write in multiple threads in the same process (with my own connection pool because I wanted to control thread-binding semantics, and I don't want to do the per-connection setup very often either) and that works fine. It's fast too. But the two conditions above are really important for doing this; you need careful control of transaction semantics. In my case, that meant doing some fairly ugly things to the DB driver to stop it from being "helpful" (because JDBC's default transaction semantics are rather weird when you get into the details; if you're not using Java — specifically the Xerial driver, but maybe the others too — then you don't need to care about that).

If you intend to access the database from multiple threads in a process, and any of those threads can modify the DB, then you should use a thread-safe build. It's probably best to just always use a thread-safe build and pretend that the other alternative doesn't exist.

None of them access same database, that is why I ask here.

They are all open they own database connection, without know othere exists. and they dont write into same page.

if there is reader exists, writer will not checkpoint, so reader can safelly read from writer page.

It does not matter whether or not you are accessing the same database. If you are using SQLite in multiple threads, you need to activate mutexes.