SQLite Forum

DB read fails while doing parallel bulk DB updates
> While we are doing bulk updates to DB (using thread 1) if we issue select query using thread 2 it passes only 2% of the time, our expectation is since we have separate handles and we have used THREADSAFE=2 option DB read should pass all the time while the other thread is updating the DB.

Why do you believe that, and what is the error returned?

WAL <https://www.sqlite.org/wal.html> is what permits reading while writing (but not during checkpointing).  

THREADSAFE controls how the single-entrance requirement is to be policed.  The default THREADSAFE=1 means that SQLite3 will use mutexes on connections to ensure that single-entrance is enforced, thus taking a few extra nanoseconds on each library entry but preventing all hell from breaking loose.  THREADSAFE=2 means that SQLite3 will not use mutexes to ensure that single-entrance requirements are met thus saving those few nanoseconds per library call but will not prevent all hell from breaking loose if you do not properly adhere to the entrance requirements.

THREADSAFE does not affect locking or concurrency.

So, there are a couple of possibilities here:  
 1) Your cache_size is too small  
 2) Your update is taking too long  

In any case, your scheduler yield will do nothing since nothing is waiting on a scheduler yield (this is pre-emptive multitasking, not co-operating multitasking).

If you are sure your cache\_size is ample for the changeset then you might want to try compiling with SQLITE\_ENABLE\_SETLK\_TIMEOUT=1 if your system supports file control of lock timeouts.  It may or may not solve your problem by changing the locking operations to block on the lock rather than block on a timer to re-poll the lock.

Otherwise (after making sure your cache\_size is big enough) use WAL mode (pragma journal\_mode=WAL) and set the pragma wal\_autocheckpoint=1 (to ensure maximum determinism).