dimanche 21 juin 2015

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

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

enter image description here

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 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

Aucun commentaire:

Enregistrer un commentaire