Concurrent execution of two SQLite transactions in two threads

Question

I have a certain update method in my Android App, which generates quite an amount of data - up to hundreds of database entries. I also have a background service, aside from the UI thread. Both threads have to execute the update method, sometimes even at the same time - basically, this is about generating and caching data to display. Both the UI and background service need this data.

Currently, I have wrapped the method's execution in an ORMLite transaction, which maps to an ordinary SQLite transaction. However, I am afraid that this will bite me in the butt one day, when some race condition screws up the data cache.

The question: Do SQLite transactions protect me from concurrent execution, or should I rather implement some kind of worker thread which is spawned when the generator-method shall start, or blocking if the generator-method is already running?

UPDATE:

I have decided to not rely on SQLite logic for the protection of my high-level Java method. The solution was for me as follows:

• Wrap the generating part of the method with synchronized
• Introduce a variable which tracks the last time of executing the method (set at the end of the method, so it is the marker of execution END)
• First thing in the synchronized section, check if the last execution is in a specific threshold (e.g. <= 100ms in the past)
  • If yes, skip generation
  • If no, perform generation

In this way, duplicate generation should not take place, since when the method is accessed from two threads at the same time, the first will generate, but the second will not. The most important part for me here is that it is still blocking, since both threads rely on the generation having taken place after they have called the method.

Answer 1

EDIT:

It seems I'm wrong in my below statement: The SQLite implementation is, according to many, thread safe. I have, however, bitterly experienced threading issues, especially when testing database access, but that must have been caused by other factors in my code then, I assume.

Sorry for the misleading answer.

ORIGIN:

Good question!

You should be very careful here because the standard Android database access objects (such as SQLiteDatabase, Cursor etc) are not thread-safe by default. Not even ContentProvider's seem to give you a complete protection unless you explicitly write them with multithreading in mind.

According to Android documentation on ContentProvider's and threading (almost at the end of the page):

"Because these methods [update() is one of the functions] might be called from any number of threads at the same time, they too must be implemented to be thread-safe."

I don't know if there is any explicit locking mechanism to SQLiteDatabases (as in locking the actual database file). I would assume that a transaction itself would lock, at least the very handle you access your database with. I don't know what is true for the case where you have multiple handles to your database.

Maybe you could try to implement some singleton object (A ContentProvider maybe?) to access your database with, but even then you'd have to manage some sort of "request queue" I suppose.

You should also consider not to make any calls to the file system (the database is on the file system) from the UI-thread, what-so-ever. There is no guarantee that the database will answer in time and you're likely to end up with an ANR (especially as you write "...which generates quite an amount of data").