I understand your logic about retrying on a SQLITE_BUSY return. This is a little difficult because of abstraction layers. I have a generic sqlite Exec method that is used for many different statements. If the database is busy during a commit, my busy handler will be called. If the database is busy during an update, my busy handler will not be called. If the sqlite3_exec function returns BUSY, I should retry immediately, but only if my busy handler wasn't called. The bookkeeping gets messy.
I would appreciate being able to control this behavior. Perhaps a pragma or database setting? At the very least, a compile-time switch? I know this increase the complexity of testing, but one of the reasons I made the switch from SQLite 2 to SQLite 3 was to get the increased concurrency, and I really like being able to control the deadlock behavior. --Ned. -----Original Message----- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Monday, August 16, 2004 4:38 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Concerns about checkin 1879 Ned Batchelder wrote: > > With the change in checkin 1879, it looks like my busy handler doesn't > get a chance to make a choice any more. Now thread B immediately gets a > BUSY error, without invoking the busy handler. > > Wouldn't it be better to invoke the busy handler in all cases? Have I > gotten something wrong? > What I found is that most programmers are simply calling sqlite3_busy_timeout() to set (say) a 10-second timeout on all locks. Then they merrily begin issuing SQL statements in multiple threads. In that scenario, the old behavior prior to check-in [1879] was causing deadlocks that resulted in nasty 10-second timeouts and all kinds of resulting performance difficulties. One could, as you observer, handle the busy callbacks more intelligently and make an informed choice about which thread to rollback. But most programmers would probably prefer to not have to worry with such choices. The default behavior should be to do the thing that is right most of the time. And that is what check-in [1879] does. Most of the time when you have thread A that is trying to commit and thread B which is trying to do its very first update, you'd rather rollback thread B. Prior to check-in [1879], the transaction that rolls back was arbitrary and the rollback only occurred after a long delay. More sophisticated programs that want more control can still have it, even with check-in [1879]. If thread A is trying to COMMIT and thread B is trying to UPDATE, you will get a busy handler callback from thread A and an SQLITE_BUSY reply from thread B. But the SQLITE_BUSY return from thread B did not clear any locks. There is nothing that prevents the program from rolling back thread A then reissuing the UPDATE statement of thread B. But even with check-in [1879], I'm finding that a lot of users are getting all twisted up around locking. So I'm thinking of going back to the 2.8 behavior of taking out an exclusive lock the moment you begin a transaction. This reduces the amount of concurrency but it also makes locking easier to understand for programmers who have more pressing things to worry with. A PRAGMA or a new API could be used to reenable the enhanced-concurrency behavior that was introduced with version 3.0. If I make that change, perhaps I'll start issuing busy callbacks for all lock types again. We'll see... -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
