SQLite Forum

DB read fails while doing parallel bulk DB updates
Login
Hi Keith

We are running the application under embedded Linux OS 3.10.17 in iMx dual lite with eMMC storage, so our writes are not as fast as SSD but i haven’t seen bad performance with WAL mode during 500K inserts/update (this is our max supported records at this point of time). The response event runs for few milliseconds we just have to check bunch of conditions and activate a GPIO, I have been doing some stress test and observed a new issue

Configuration:
In default journal or WAL mode with SQLITE_CONFIG_SERIALIZED, single open handle to DB and busy callback function configured with timeout value 2 seconds (sqlite3_busy_timeout(db_,2000) )

Issue:
During parallel write from 2 different threads (Thread 1 doing bulk insert/update as mentioned in my above posts (30 to 40K records) and thread 2 doing minor updates (10 to 15 records) to different table in the same DB file) i can see busy handler getting invoked multiple times and eventually both write passes and everything ends well, but when i do a bulk delete from thread 1 (32 to 40 K records by giving a range in a single delete query this will run for 15 to 20 seconds) and minor updates from thread 2 the busy callback handler gets invoked for the same query numerous time beyond the 2 seconds cutoff (it goes close to 13 to 15 seconds, and sometimes this leads to abrupt application crash. I saw you mentioned about usleep issue. We do use usleep in our code and it works well hence i know the function call works but i’m not sure about the reporting part. 

Is there a chance that the callback handler can get into infinite loop if the usleep doesn’t work properly? in that case the best way to get out of the callback is to check the count and have some condition and exit the busy callback handler? Another option i could think of is avoid running such single big delete query instead do the bulk delete in terms of batches of 250 within a begin and end transaction and that might give chance to the second thread.