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_prepare_v2("SELECT * FROM t1;");
sqlite3_exec("INSERT INTO t1 VALUES(1, 1, \"aweagsr\")");//database is

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!

sqlite-users mailing list

Reply via email to