Hi,
Thanks a lot for this suggestion. I have reduced the delay in the busy handler from 10ms to 1ms and added a delay of 2ms after "commit" and before "begin". With this it is working fine now. After reading you input we got to know the root cause is because of the CPU context switch between the process. whenever busy handle start executing another process would have started the "begin" causing the DB lock. Thanks again. Thanks and Regards Deepak ________________________________ From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of Simon Slavin <slav...@bigfraud.org> Sent: Thursday, October 5, 2017 8:00:31 AM To: SQLite mailing list Subject: Re: [sqlite] Sqlite3 Multi-process handling with continuous begin and commit in one thread cause error for other thread sqlite3_exec. On 5 Oct 2017, at 3:17am, Hegde, Deepakakumar (D.) <deep...@allgosystems.com> wrote: > 2) Both the link are added the busy handler and busy handler function is > retries for 10000 times with 10ms second of delay. > sqlite3_busy_handler(psRaceSqlite->sSqliteInfo.pSqlHandle, > dbm_sqlite_busy_handler,psRaceSqlite); Instead of this, just set an timeout, which in your case would be 1000000ms. This may look big but is an appropriate value. <https://sqlite.org/c3ref/busy_timeout.html> SQLite’s own timeout routine knows more about how SQLite works than yours do. It implements exponential backoff and other clever things. Do not try to use both at once: strip out your use of sqlite3_busy_handler() . Remember that you need to set timeout for every connection separately, in other words not only for the connections doing IHSERT but for the other processes which are reading or doing individual INSERT commands. > 3) In one thread there is a continuous record insertion with begin and commit > operation. This begin and commit will be done with for every 200 record > insertion. time for a single begin to the commit is about 1 second. Use BEGIN EXCLUSIVE. After each COMMIT, pause for at least 20ms. If that doesn’t improve things you may need more — anything up to 1000ms could help. You could set that number for testing then reduce it until it stops working. Thank you for your detailed description and timings which help us make good suggestions. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
