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