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.
http://sqlite.org/c3ref/busy_handler.html
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 or SQLITE_IOERR_BLOCKED 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.
-j
--
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
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
