On Mon, Apr 2, 2012 at 7:51 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > On Mon, Apr 02, 2012 at 07:40:58PM +0400, Gregory Petrosyan scratched on the > wall: >> Hello, >> >> I am experiancing a weird problem: sometimes (1 time in a 10-100) when >> 2 processes try to open the same database file (and execute something >> like 'create table foo if not exists'), one of them fails with >> SQLITE_BUSY ??? despite 1 second (or bigger) timeout. >> >> Processes themselves produce almost no DB activity; they merely start >> and initialize the database roughly at the same time. >> sqlite3_open_v2() is immediately followed by sqlite3_busy_timeout(). >> >> Unfortunately I can't write a small program that reproduces this >> reliably: my test program never crashes this way (except when timeout >> is really small ??? say, 10ms). Yet, this behaviour is rare but >> reproducible, both from Python (apsw) on Windows, Python (apsw) on OS >> X and native C API on OS X (in entirely different program). >> >> Can you please point me at what can be the cause of this? > > You may be seeing a deadlock situation. This is most commonly > associated with explicit transactions (that are open for a longer > period of time), but it is possible with implicit transactions. > > If two connections attempt to write to the DB at the same time, it is > possible for them to deadlock on the file locking. SQLite recognizes > this and has one of the connections back off with an SQLITE_BUSY error. > If this happens in an explicit transaction, the program needs to > ROLLBACK the current transaction and start over. In the case of an > implicit transaction around a statement, you can safely re-run > the statement.
Thanks a lot for the reply. Shouldn't it be extremely unlikely for this situation to happen, though? Can it be diagnosed with more certaincy? I feel uncomfortable guessing (hoping) that the bug exists due to this race/deadlock. As a side note: why does not SQLite automatically retry implicit transactions after invoking busy handler? This is IMO the behaviout most people will think SQLite has (after reading the docs); if this is not true, it is almost impossible to rely on busy handler (timeout) at all, and it is difficult to understand the need for it — if I need to wrap all queries into retry/sleep on my own, why have extra strange internal timeouts in SQLite? Gregory _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
