The sqlite3_busy_timeout() function is just a wrapper for sqlite3_busy_handler().
As explained at http://www.sqlite.org/c3ref/busy_handler.html, the use of a busy handler does not avoid all SQLITE_BUSY situations: The presence of a busy handler does not guarantee that it will be invoked when there is lock contention. If SQLite determines that invoking the busy handler could result in a deadlock, it will go ahead and return SQLITE_BUSY to the application instead of invoking the busy handler. Consider a scenario where one process is holding a read lock that it is trying to promote to a reserved lock and a second process is holding a reserved lock that it is trying to promote to an exclusive lock. The first process cannot proceed because it is blocked by the second and the second process cannot proceed because it is blocked by the first. If both processes invoke the busy handlers, neither will make any progress. Therefore, SQLite returns SQLITE_BUSY for the first process, hoping that this will induce the first process to release its read lock and allow the second process to proceed. Basically, if SQLite detects a dead-lock situation, that no amount of waiting will resolve, the busy handlers will immediately return an SQLITE_BUSY. The only way to resolve this situation is for the connection to rollback its current transaction, release all locks currently being held, and allow the other connection to continue. In short, locking is hard, there is no easy way out. -j On Jul 9, 2015, at 6:45 AM, Constantine Yannakopoulos <alfasud.ti at gmail.com> wrote: > ?Hello all, > > I have a multithreaded server that may perform intense database actions > (both reads and writes) concurrently from different threads against two > database connection (sqlite3* objects) to the same database file. The > database connections are in shared-cache mode and I have implemented the > unlock notification mechanism (sqlite3_unlock_notify()) in order to > facilitate table-level locking according to instructions given here: > https://www.sqlite.org/unlock_notify.html. I also use sqlite_busy_timeout() > to set an internal retry mechanism with a reasonably large timeout value in > order to avoid SQLITE_BUSY errors. > ?? > > While in rudimentary tests the whole mechanism seems to work as expected, > in normal server operation under heavy load some statements (different ones > each time) seem to return SQLITE_BUSY immediately, which seems to indicate > that busy_timeout() dos not work in some cases. And this does not change > even if I set the busy timeout to a very large value, e.g. 0x7FFFFFFF > ?, and calling PRAGMA busy_timeout reports the expected value? > . Also, it doesn't seem to have a difference if the database is in WAL or > DELETE mode. > > What may I be doing wrong? Or is there something regarding busy_timeout() I > have missed? A scenario where it is bypassed? Unfortunately this behaviour > appears only under heavy load in customer sites and is very hard to > reproduce and debug. > > SQLite version is 3.8.10.1, OS is Win7 x64 and the server is compiled in > 32-bit and SQLite is dynamically linked via separate library (sqlite3.dll), > locking_mode is NORMAL, synchronous is FULL and threading mode is > SERIALIZED. > > Thank you in advance. > -- > Constantine. > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable." -- Angela Johnson
