On 04/03/2012 12:48 PM, Gregory Petrosyan wrote:
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?
It's a race. That's what SQLITE_BUSY means.
As Jay says, deadlock is not possible for implicit transactions.
SQLite will keep retrying until either your busy-handler returns
zero (if you configured a busy-handler) or the timeout is reached
(if you configured a timeout). It sounds like the latter in this
case.
If you are using WAL mode, check that connections are being closed
properly. If they are not, a *-wal file may be left on the disk even
after all active clients have disconnected. In this case, when the
next client connects it has to read and process the entire *-wal
file. This is called "recovery", and the client holds an exclusive
lock on the db file while it is underway. Any other clients
that attempt to read or write the db see an SQLITE_BUSY. Any
busy-handler (or timeout) will apply here, but if recovery takes a
while then a short timeout could be exceeded.
Other than that, you probably just want to increase the busy-timeout
some.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
