Mac OS X: Database busy errors with Live Queries when adding many documents

[Mark]

CBL lite 1.0.2 (with the exported-symbol fixes) on Mac OS X 10.9.4 (XCode 5.1.1)

I was running a test this morning creating a large number of documents through the model API. Not syncing. Like:
CBLDocument *doc = [db documentWithID:id]
CBLModelSubclass *docModel = [CBLModelSubclass modelForDocument:doc];
Add stuff to model
Release model (well, let ARC release) and let autosave do it's work.

All this is on the main thread. This works well.

However, if I add a liveQuery (actually 3, to count the incoming sub-docs) I start getting
"-[CBL_FMDatabase executeUpdate:error:withArgumentsInArray:orVAList:]:750 Database busy (/path/to/database.cblite)" somewhat randomly (or at least, I haven't seen a pattern).

I updated to 1.0.2 to see if some of the view enhancements would help, but it doesn't seem to.

Any suggestions on best approaches to address this?

Thanks!

[Mark W.]

Was finally able to dig into this today. Turns out that I was missing almost half of my documents in my index because of the database busy errors.  So I built a test app to try out some variations. 

TL;DR: CBL lite will run just fine if you do everything in one thread (or a dispatch_queue). The problem is there's no way I can find to force all automated actions through the main thread or a designated queue. This will likely never affect most installations - this requires serious document creation. Also: I have not tested this with any kind of sync.

I started this test after seeing this is CBLManager.h

@property (strong) dispatch_queue_t dispatchQueue;

I created a serial queue and wrote everything to take advantage of it. Unfortunately, this doesn't force the internal features to use this queue. For instance, I was using CBLModel's autosave feature - that always triggers on a separate thread. CBLLiveQuery's indexing is done on a separate thread too. 

When under load, these background threads don't serialize access to the database and start throwing database busy errors. Looking at the code, there does not currently appear to be a way to trap or report on those errors - they just go to NSLog. The load I was working with was just over 1 new document/model per second. This is on an older MacBook Pro with a spinning drive. Faster equipment probably makes this less of an issue. 

My solution:

1. Use [CBLModel save:] on the insert thread. I've left autosave on for now as a backup - but it doesn't fire if there's nothing to save.

2. Built a version of "live updating" on the main thread by counting the number of items I'm inserting and triggering an occasional [QBLQuery run:] synchronous on the main thread.

With those changes, I can insert as fast as the machine can create and save them without any database busy errors - which is impressive. It's less ideal, especially the livequery change, but doable. 

This is an edge case even for me so I'm not sure if I'm going to port this work back out of the test app, but I wanted to report the outcome. Couchbase, if you want any of this as bug reports, let me know. I can see 3 potential:

1. Better handling of "Database busy" errors - even if it just throws a NSNotification so the app can be aware.

2. If dispatchQueue is set, run "automated tasks" in that queue.

3. Some version of validation test for view indices. 

Maybe ForestDB fixes some of these for free?

[Jens Alfke]

On Sep 8, 2014, at 2:26 PM, Mark W. <mark...@gmail.com> wrote:

I created a serial queue and wrote everything to take advantage of it. Unfortunately, this doesn't force the internal features to use this queue. For instance, I was using CBLModel's autosave feature - that always triggers on a separate thread. CBLLiveQuery's indexing is done on a separate thread too. 

Sorry you're having trouble with threads contending over the database; it's frustrating, I know.

Autosave happens on the database's thread/queue except in the case where all models are autosaved when the app goes to the background; there was a bug (#441) that caused this to happen on the main thread. I fixed that last week after it was reported, and the patch is very small and easy to cherry-pick if you need it.

Indexing and replication always happen on a background thread, though.

When under load, these background threads don't serialize access to the database and start throwing database busy errors.

Database access is serialized through file locks by SQLite. The problem is that SQLite polls the lock, i.e. keeps trying to open the database until a timeout. This doesn't work well when the database is under heavy load by a different thread, it turns out.

The real fix for this will be replacing SQLite with ForestDB, which doesn't have any of these issues. This will be part of the next major release of CBL.

Looking at the code, there does not currently appear to be a way to trap or report on those errors - they just go to NSLog.

I don't believe so. They'll be reported through the API. The SQLite call will fail with error SQLITE_BUSY, and the CBL code will pass that up the call chain and wrap it in an NSError at some point.

The load I was working with was just over 1 new document/model per second. This is on an older MacBook Pro with a spinning drive.

That in itself is a tiny load, but by spacing documents out that way you're creating a lot of transaction overhead. A single SQLite transaction takes a lot of time (on the order of 100ms, I think) flushing data to the filesystem to ensure durability. If you're creating or updating a lot of documents, it's much better to collect them together and save them in one transaction.

You also end up triggering the LiveQueries and re-indexing your views after every individual insertion, which adds more overhead too.

1. Better handling of "Database busy" errors - even if it just throws a NSNotification so the app can be aware.

Again, as far as I know they are handled. If you know specific cases where they aren't, please file issues.

2. If dispatchQueue is set, run "automated tasks" in that queue.

No, the indexing and replication tasks are intentionally run on a background thread to avoid blocking the thread/queue you're using.

3. Some version of validation test for view indices. 

I'm not sure what you mean by this?

—Jens