About android Sqlite safety in multi-process case

Question

In my application, there exist more than one process, and in each process, I need access the same SQLite database (of course, it means more than 2 theads), so I worried about not only the thread-safety about the SQLite, but also the process-safety.

One solution for this case is using content-provider. But from android sdk, it warns that its methods may be called from multiple threads and therefore must be thread-safe. If content provider itself not necessarily means thread-safe, how can I assume it is process-safe?

The article also clarifies that SQLiteDatabase itself is synchronized by default, thus guaranteeing that no two threads will ever touch it at the same time. What if in the multi-process case? Can two processes modify the same table at the same time? Will it crash?

Archaeology

no, they can't. you will get SQLiteDatabaseLockedException – Iinden

@Archaeology you found any solution for this problem? – Animatism

@Iinden What would be the solution for the database-locked issue for multiple processes? Is enableMultiInstanceInvalidation enough for this use case? – Pita

so...... i went down this path, using Room DB, and multiple processes by using enableMultiInstanceInvalidation to build the Room DB instance. It worked, but in my telemetry, i could see lots of users getting SQLiteDatabaseLockedException exceptions. the situation was improved by adding retries with exponential backoff, but it was still an issue to 200/12K users. next, I am going to try having only 1 service in a separate process mutate the DB while the rest only do read operations.....wish me luck :( – Woolery

Answer 1

You can find answer here: https://www.sqlite.org/faq.html (point 5). Briefly:

Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in time, however.

Katydid

Answer 2

Multiple processes behave just like multiple threads, i.e., their transactions are safe from being interfered with by each other.

Sholem

But with the two processes, I actually could get 2 SQLiteDatabase instances, will it cause any potential problems about the thread- or process-safety? – Archaeology

Different processes have different address spaces; the two objects cannot even see each other. (They only see the lock on the DB file.) – Sholem

Yes, i know that. So it means, the SQLite database lock should be global to the entire system, if one app acquires the lock, any other application in another process should get a "locked" state if accessing the DB, right? – Archaeology

Yes; a transaction writing to the DB file is global to any system that accesses the file. – Sholem

I just ran a simple test on a 2.3 device. Without content provider it crashes (database is lock exception) otherwise it works well. But on a 6.0 device both work. Not sure why. Any ideas? – Weatherbeaten

@Weatherbeaten Newer platforms are implemented to wait when it sees a lock. But it won't wait longer than 2.5 seconds. If database is still locked after 2.5 seconds, the 'database is locked' exception is thrown. – Endosteum

@Endosteum I am facing database is locked exception (code 5) in the multiprocess application. what should I do to solve this problem? – Animatism

Answer 3

If you use Android Room, see https://issuetracker.google.com/issues/62334005

Lowman