SQLite Forum

DB read fails while doing parallel bulk DB updates
Our application has 2 threads, thread 1 opens the DB handle with read/write option and thread 2 opens the DB with read only option. sqlite library is compiled with  -DSQLITE_THREADSAFE=2 option

Thread 1 takes care of DB updates (records update can be 200K to 500K updates). In order to have optimal user experiance we have used begin/end transaction to do bulk updates as shown below
    1. Begin transaction 
    2. In a loop insert/update 250 records 
    3. End Transaction
    4. used thread yield, sched_yield(); 
    5. Start from step 1 (we dint want to lock the DB with single begin and end transaction)

Thread 2 is just used to query DB when a HW event occurs and its critical for us to make sure the query is serviced successfully on ALL occasions within 1 second.

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. 

If we use a select count query in step 4 of thread 1 then the pass rate of select query 
in thread 2 increases exponentially to 95%.  We are not exactly sure what's going wrong.

How to achieve consistent DB read (response time < 1sec) while bulk DB updates are going on in parallel