Large operations in sync databases

Andrew Sutherland asutherland at asutherland.org
Tue Jul 13 03:44:22 UTC 2010


  On 07/12/2010 03:41 PM, Kent James wrote:
> 1) Any suggestions about what I do about large operations with lots of 
> messages? For example, deleting a folder requires me to obtain a long 
> list of itemIds, and delete those messages from the supposedly sync 
> metadata store. How do I do that on the UI thread? One possibility is 
> to setup an async delete loop, one message per loop, that would delete 
> these messages. Another might be to use sync deletes, one per 
> messages, with some sort of manual yielding function. So the basic 
> question is, how would you recommend that I setup a delete of a large 
> number of messages from an sqlite store that does not block possible 
> other sync calls that might be UI sensitive?

There is no silver bullet here for 1.9.2, but deleting folders is rare, 
likely to be perceived by the user as potentially expensive, and not a 
great place for complex implementations.  I'd do the simplest thing you 
can and worry about it later.  In my mind, this would be a synchronous 
operation on the main thread that blocks everything else.

> 2) If I setup a db file primarily for sync access, is there any reason 
> to avoid all async statements on that database? Or could I also add 
> some short async statements (such as the proposed delete loop above) 
> without compromising the ability to prevent blocking of the UI thread?

Any concurrent access can potentially cause the main thread to block (in 
1.9.2).  Even read-only async statements on their own connection could 
cause an attempt to acquire a write-lock on the main thread to fail.

The good news is that it sounds like SQLite 3.7.0 will land on 1.9.3 if 
it gets released as planned which changes the game considerably.

Andrew



More information about the tb-planning mailing list