I ran a test and I can still find "database is locked" even if I use
busy_handler(threadsafe=2, 2 connections).
When thread 1 executing a writing transaction, thread 2 runs the code below
at the same time:
sqlite3_exec("BEGIN")
//SELECT
sqlite3_prepare_v2("SELECT * FROM t1;");
sqlite3_step;
sqlite3_reset;
//INSERT
sqlite3_exec("INSERT INTO t1 VALUES(1, 1, \"aweagsr\")");//database is
locked
sqlite3_exec("COMMIT");
Writing in thread 1 will no block SELECTs in thread 2 as I use WAL. But the
INSERT within the transaction of thread 2 still returns SQLITE_BUSY.
I think I have used sqlite3_busy_timeout() in right way and I find that
sqliteDefaultBusyCallback() did not be called.
Is it expected?
It's almost like you are not reading the posts by Keith - which leads me
to believe you might have messages channeled to spam for some reason,
it's worth checking the spam folder.
To answer the questions: YES, it is expected.
To elaborate:
A - When starting a writing transaction from ANY thread, then ANY other
write attempt (threaded or otherwise) will find the DB/Table(s) locked,
that's by design and an absolute necessity in any RDBMS.
B - Should this happen, sqlite /CAN/ use a default busy handler that
does nothing more than waits a bit, then see if the in-progress lock is
lifted before continuing. Rinse and repeat until busy timeout time is
exhausted. It is important to note that this busy handler ONLY
functions if there is an allowed time-out set on the connection. (See
pragma timeout, or it can be set via the api too).
C - You can provide a busy-handler of your own making that will fire
whenever an SQLITE_BUSY signal is received, regardless of timeouts. You
can specify via this handler if the operation should wait, cancel, etc.
Usually setting the internal busy timeout is enough, and with large DBs
running huge transactions, a very long timeout is common (sometimes in
the order of minutes) - you will need to study your DB use case and
decide what a good time is to give up on waiting. If you /DO/ expect a
lot of timeout waiting on an interactive user-based system, probably
best to indicate to the user and not look like you died - perhaps even
prompt them to find if they would like to wait for xxx to finish or
cancel - in which case a combination of setting a busy handler and also
a progress handler will be best.
More information on all the above is easily found in the sqlite site's
search - if you do decide which to do and have any difficulty with a
specific method, or fail to find information - please post again.
Good luck!
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
