Android: how can I lock SQLite database accessible with ContentProvider (or other way of executing atomic conditional operations)

Question

I got two tables in my SQLite DB: entities and user_actions. Their approximate schemes:

The flow of the program is something like this (all DB accesses handled by ContentProvider):

1. The user performs some action which modifies one of the entities
2. The corresponding entity is updated in entities immediately. locally_modified value of this entity is set to 1
3. The information about user's action is stored in user_actions
4. At some point in future a sync session with the server is being initiated (I use SyncAdapter framework)
5. User's actions from user_actions are uploaded to the server one by one and removed from the DB in a background thread
6. When the uploading completed, I need to clear locally_modified flags in entities

At this point I encounter my atomicity issue: the synchronization with the server happens in a background thread, therefore the user can use the app and perform additional actions. As a consequence, right before I clear locally_modified flag for an entity, I must check that there are no records in user_actions corresponding to this entity. These three steps must be executed atomically for each entity having locally_modified set to 1:

1. Query user_actions for entries corresponding to entity's _id
2. Test whether the query from #1 returned an empty set
3. Clear locally_modified of that entity to 0

Given the above scenario, I have three questions:

Q1: Is there a way to lock SQLite DB accessed over ContentProvider in Android such that it can be accessed only by the locking thread?

Q2: If the answer to Q1 is positive, what happens if some other thread tries to access a locked DB? What precautions should I take to ensure reliable operation?

Q3: It is possible to execute atomic transactions with conditional logic using ContentProviderOperation? You can use "back-references" as described in this answer and this blog post to reference the result of a previous operations, but is there a way to use that result in some kind of if-else statement?

Thanks

Answer 1

Is there a way to lock SQLite DB in Android such that it can be accessed only by the locking thread?

Yes, have a look at SQLiteDatabase.beginTransaction() (source). I believe you need SQLite's exclusive transactions, but you need to study that a bit more for your exact usage.

If the answer to Q1 is positive, what happens if some other thread tries to access a locked DB? What precautions should I take to ensure reliable operation?

There's an SQLite.amIInTransaction() method that you could check, or just catch an SQLiteDatabaseLockedException (more SQLite exceptions that you should look up)

It is possible to execute atomic transactions with conditional logic using ContentProviderOperation? You can use "back-references" as described in this answer and this blog post to reference the result of a previous operations, but is there a way to use that result in some kind of if-else statement?

Never done that, but it seems that overriding ContentProvider's applyBatch and wrapping it in a transaction should work:

Android: SQLite transactions when using ContentResolver

Answer 2

The answer turned out to be pretty simple, but it is kind of a "hack" - just add additional Uri to ContentProvider.

For example: initially my ContentProvider supported the following URIs:

• Uri.withAppendedPath(MyContract.CONTENT_URI, "entities")
• Uri.withAppendedPath(MyContract.CONTENT_URI, "user_actions")

In order to support the atomic operation described in the question I added an additional Uri:

• Uri.withAppendedPath(MyContract.CONTENT_URI, "clear_modified_flag")

When this Uri is updated through:

getContentResolver().update(
    MyContract.ClearModifiedFlag.CONTENT_URI,
    new ContentValues(),
    null,
    null);

my ContentProvider executes an SQLite transaction that locks the database for the duration of the operation and rolls it back in case of any errors (as described in this answer).

That's it.

P.S. my ContentProvider is not exported (i.e. other apps can't access and use it), therefore it is safe to add this new Uri to it. But keep in mind that if you do export your ContentProvider, then exposing functionality like this one could be problematic.