SQLite DB with Content Provider race conditions

Question

I implemented Content Provider that uses a SQLiteDatabase as its backing data source.

One activity writing to the DB by calling getContentResolver().applyBatch(operations), which should be atomic.

protected void onPause() {
    new Thread(){
        @Override
        public void run() {
            ArrayList<ContentProviderOperation> ops = new ArrayList<>();
            ContentProviderOperation.Builder builder;
            for (Tag tag: mTopicAdapter.getTags()) {
                builder = ContentProviderOperation.newUpdate(QuizProvider.TAG_URI);
                builder.withValue(Tag.Table.SELECTED, tag.getSelectionStatus());
                builder.withSelection(Tag.Table._ID + " = " + tag.getId(), null);
                ops.add(builder.build());
            }
            try {
                ContentProviderResult[] res = getContentResolver().applyBatch(QuizProvider.AUTHORITY, ops);
                Timber.d("Update result: %d", res.length);
                getContentResolver().notifyChange(QuizProvider.TAG_URI, null);
                getContentResolver().notifyChange(QuizProvider.QUESTION_URI, null);
            } catch (RemoteException e) {
                e.printStackTrace();
            } catch (OperationApplicationException e) {
                e.printStackTrace();
            }
        }
    }.start();
    super.onPause();
}

Second activity reading from the DB with the help of Cursor Loader, and sometimes gets old data (race condition).

@Override
public Loader<Cursor> onCreateLoader(int id, Bundle args) {
    Uri randQuestionUri = QuizProvider.QUESTION_URI
            .buildUpon()
            .appendPath("rand").appendPath(Integer.toString(QUIZ_SIZE))
            .build();
    return new CursorLoader(this, randQuestionUri, null, null, null, null);
}

@Override
public void onLoadFinished(Loader<Cursor> loader, Cursor data) {
    if (DEBUG) Timber.d("load finished: %d", data.hashCode());
    mPagerAdapter.swapCursor(data);
}

@Override
public void onLoaderReset(Loader<Cursor> loader) {
    mPagerAdapter.swapCursor(null);
}

Full project here.

Log output:

NSA:QuizProvider:138: update db: selected=false _id = 4
NSA:QuizProvider:138: update db: selected=false _id = 5
NSA:QuizProvider:138: update db: selected=false _id = 26
NSA:QuizProvider:138: update db: selected=false _id = 19
NSA:QuizProvider:138: update db: selected=false _id = 28
NSA:QuizProvider:138: update db: selected=false _id = 10
NSA:QuizProvider:138: update db: selected=false _id = 12
NSA:QuizProvider:138: update db: selected=false _id = 15
NSA:QuizProvider:138: update db: selected=false _id = 18
NSA:QuizProvider:138: update db: selected=false _id = 25
NSA:QuizProvider:138: update db: selected=false _id = 16
NSA:QuizProvider:138: update db: selected=false _id = 17
NSA:QuizProvider:138: update db: selected=false _id = 8
NSA:QuizProvider:138: update db: selected=false _id = 3
NSA:QuizProvider:138: update db: selected=false _id = 20
NSA:QuizProvider:138: update db: selected=false _id = 29
NSA:QuizProvider:138: update db: selected=false _id = 24
NSA:QuizProvider:138: update db: selected=false _id = 23
NSA:QuizProvider:138: update db: selected=false _id = 30
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuizProvider:103: query db: content://doit.study.droi    question/ran    280 null null
NSA:QuizProvider:138: update db: selected=false _id = 6
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuizProvider:138: update db: selected=false _id = 1
NSA:QuizProvider:138: update db: selected=false _id = 14
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuizProvider:138: update db: selected=false _id = 7
NSA:QuizProvider:138: update db: selected=false _id = 27
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuestionsActivity:84: load finished: 154982045
NSA:QuestionsPagerAdapter:66: swap cursor, cnt: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:35: instantiateItem, pos=0
NSA:QuestionsPagerAdapter:25: getItem, pos=0
NSA:QuestionsPagerAdapter:35: instantiateItem, pos=1
NSA:QuestionsPagerAdapter:25: getItem, pos=1
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuizProvider:138: update db: selected=false _id = 2
NSA:QuizProvider:138: update db: selected=false _id = 11
NSA:QuizProvider:138: update db: selected=false _id = 22
NSA:QuizProvider:138: update db: selected=false _id = 9
NSA:QuizProvider:138: update db: selected=false _id = 31
NSA:QuizProvider:138: update db: selected=false _id = 21
NSA:QuizProvider:138: update db: selected=false _id = 32
NSA:QuizProvider:138: update db: selected=false _id = 13
NSA:QuizProvider:138: update db: selected=false _id = 4
W/FragmentManager: moveToState: Fragment state for QuestionFragment{5c25e01 #0 id=0x7f0f00e5} not updated inline; expected state 3 found 2
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:52: title pos: 0, questions: tags: [User Interfaces]
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:52: title pos: 1, questions: tags: [User Interfaces]
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuizProvider:103: query db: content://doit.study.droi    tag null null
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuestionsPagerAdapter:42: counter: 104
NSA:QuizProvider:138: update db: selected=false _id = 5
NSA:QuizProvider:138: update db: selected=false _id = 26
NSA:QuizProvider:103: query db: content://doit.study.droi    tag null null
NSA:QuizProvider:138: update db: selected=false _id = 19
NSA:QuizProvider:138: update db: selected=false _id = 28
NSA:QuizProvider:138: update db: selected=false _id = 10
NSA:QuizProvider:138: update db: selected=false _id = 12
NSA:QuizProvider:138: update db: selected=false _id = 15
NSA:QuizProvider:138: update db: selected=false _id = 18
NSA:QuizProvider:138: update db: selected=false _id = 25
NSA:QuizProvider:138: update db: selected=false _id = 16
NSA:QuizProvider:138: update db: selected=false _id = 17
NSA:QuizProvider:138: update db: selected=false _id = 8
NSA:QuizProvider:138: update db: selected=false _id = 3
NSA:QuizProvider:138: update db: selected=false _id = 20
NSA:QuizProvider:138: update db: selected=false _id = 29
NSA:QuizProvider:138: update db: selected=false _id = 24
NSA:QuizProvider:138: update db: selected=false _id = 23
NSA:QuizProvider:138: update db: selected=false _id = 30
NSA:QuizProvider:138: update db: selected=false _id = 6
NSA:QuizProvider:138: update db: selected=false _id = 1
NSA:QuizProvider:138: update db: selected=false _id = 14
NSA:QuizProvider:138: update db: selected=false _id = 7
NSA:QuizProvider:138: update db: selected=false _id = 27
NSA:QuizProvider:138: update db: selected=false _id = 2
NSA:QuizProvider:138: update db: selected=false _id = 11
NSA:QuizProvider:138: update db: selected=false _id = 22
NSA:QuizProvider:138: update db: selected=false _id = 9
NSA:QuizProvider:138: update db: selected=false _id = 31
NSA:QuizProvider:138: update db: selected=false _id = 21
NSA:QuizProvider:138: update db: selected=false _id = 32
NSA:QuizProvider:138: update db: selected=false _id = 13
NSA:QuizProvider:103: query db: content://doit.study.droi    question/ran    280 null null
NSA:QuestionsActivity:84: load finished: 19434496
NSA:QuestionsPagerAdapter:66: swap cursor, cnt: 0
NSA:QuestionsPagerAdapter:42: counter: 0
NSA:QuestionsPagerAdapter:42: counter: 0

Log shows that applyBatch is not finished and Cursor Loader gets partially modified data (cursor counter=104, should be 0 or 280).

Some resources (sorry, cannot add more then two links):

_http://developer.android.com/guide/topics/providers/content-provider-basics.html#Batch

_http://www.androiddesignpatterns.com/2012/10/sqlite-contentprovider-thread-safety.html

_http://stackoverflow.com/questions/8104832/sqlite-simultaneous-reading-and-writing

_http://www.grokkingandroid.com/better-performance-with-contentprovideroperation/

Do you have any ideas what's wrong?

There are two issues with your code: the "race condition" your are facing is caused by using a new thread each time you submit a change instead of using a queue (e.g. AsyncTask with default Executor). Your code also does not call `ContentResolver#notifyChange`, so the CursorLoader is not reloaded when a change happens. — user1643723, Apr 09 '16 at 17:30
Thank you for response. I submit changes once on leaving Activity One in method onPause(). Added notifyChange() (on your advice). Still cannot get the meaning atomic for applyBatch(operations). As log output shows it's not atomic. — Maxim G, Apr 09 '16 at 20:11
You are supposed to call `notifyChange` inside the ContentProvider's methods. You are also supposed to set observable Uri on created SQLiteCursor at the same time. Learn proper way of doing things by studying code of existing ContentProviders (I recommend looking at code, generated by [AnnotatedSQL](https://github.com/hamsterksu/Android-AnnotatedSQL/)). And make sure to *use the AsyncTask instead of `new Thread`*. — user1643723, Apr 10 '16 at 03:29
I see, you suggested to re-use sequential execute order of AsyncTasks, but what's disturbing me is nonatomic applyBatch. I created small tests with transaction and raw java threads and it works as I expect (operations are [mutually exclusive](https://github.com/mgolokhov/TestBatchModeDB/tree/master)) — Maxim G, Apr 10 '16 at 09:09
I am sorry, but your explanations are a bit too… laconic. Could you please explain what do you mean by "non-atomic" in *five sentences*? — user1643723, Apr 10 '16 at 09:25
I expect that applyBatch works in one transaction, so when we writing something we actually locking DB as well. If the locking is not happening, I think it should work as snapshots, so reader should get old data, or new data (but not partially modified, as I see in QuizProvider logs). — Maxim G, Apr 10 '16 at 09:48
Oh, right, so there is an elepha…TL; DR: Base ContentProvider is completely storage-agnostic, and the concept of transactions is closely tied to actually used database engine/storage API. You are expected to override `applyBatch` and wrap it with transaction yourself. This is why I am suggesting you to read the code of some ContentProviders to get the idea, how everything is supposed to be done. — user1643723, Apr 10 '16 at 16:20

score 4 · Accepted Answer · answered Apr 10 '16 at 19:08

There are basically three things to consider here:

Move those notifyChange() calls to your ContentProvider. That way you can be sure that they are called whenever you make a change. Or in other words: You might forget to do so somewhere else. The client shouldn't be responsible for this - it doesn't belong here.
Ensure that your applyBatch() method actually uses transactions. only then do you get the desired performance benefits and only then locks are used in the way you need.
Ensure that while applyBatch() is running, no notifications are emitted. Otherwise your Loader would be called too often. You definitely want to avoid that.

You can see my cpsample project for a content provider that follows these rules.

No problem. Just remember to return when you can :-) – Wolfram Rittmeyer Apr 11 '16 at 20:23 — Wolfram Rittmeyer, Apr 11 '16 at 20:23

SQLite DB with Content Provider race conditions

1 Answers1