Richard Boulton wrote:
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.
Good point. I think the right fix for this is for me to change SQLite
so that it does not invoke the busy callback when it is trying to
acquire a RESERVED lock. If it fails to get a RESERVED lock, then
it returns SQLITE_BUSY right away. The busy callback will then only
be invoked when trying to get a PENDING lock.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
