Is it OK to keep a read transaction open for a log time in SQLite?

read transaction: http://ift.tt/1PXIkO9

we assume WAL is enabled so we can read in one tread while another one is writing.

---

What I want to achieve is something like this.

I have two thread, one is UI thread that performs db read sometimes, and another is a db thread, that performs db read/write within transactions.

The result in UI thread is cached and sometimes has incomplete infos, like for a list of things, we only query the size of the list, or only query first 10 items of that query, then we load more incrementally when the UI reaches that part.

And on db thread we will do the transactions and remember all the changes, and then post (UI thread is a looper thread) the changes to UI thread and update the UI cache incrementally so that it blocks UI thread as little as possible.

The problem is when the db transaction is complete, the UI thread might trigger a DB read on UI thread before the looper thread process that message, also due to some limitations of the UI frame work, we cannot update the cache because the UI thread is currently using it.

So my idea is on the UI thread, before any query happens, begin a read transaction (begin a transaction in deferred mode, and this transaction will not lock the database because there is no write), then any read happens in the UI thread will happen within this transaction, and then the looper thread got the update info, end the transaction, and update cache, and begin another one. So the database and cache is translated to the new state in UI thread and it is synchronized.

---

So can I keep a read transaction open a long time?