Hi, I have been experimenting with SQLite version 3.0 and am trying to implement a simple publish - subscribe model where multiple processes can publish/subscribe to messages stored in a SQLite table.
This works fine when a single process is updating the database at any one time, however I am struggling to get this working with multiple processes concurrently updating the database. Once multiple processes try to perform updates on the database, most processes fail to update the database and I get lots of SQLITE_BUSY errors. I have tried using the sqlite3_busy_timeout routine, but this just seems to make things worse as all processes remain locked out for the period of the timeout and then return SQLITE_BUSY anyway. I turned on the debugging in the locking code and ran a simple case where 2 processes were trying to update the database, it appears that one process is trying to COMMIT it's transaction (it has a PENDING lock and is trying to get an EXCLUSIVE lock), while the other process is attempting to BEGIN a transaction (it has a SHARED lock and is trying to get a RESERVED lock). This seems to cause a deadlock for the period of the sqlite3_busy_timeout. I had a look back through the mailing list and found a thread with a discussion in this area "A quick code review, analysis of locking model, "fine-tuning" suggestions", are any changes being made in this area to resolve this type of problem? I am new to SQLite so maybe I'm not handling the SQLITE_BUSY return codes correctly for this scenario. Does anybody know of a way to handle multiple processes updating a database efficiently in version 3? Many thanks, Richard
