Android: multiple threads writing on SQLite database

Question

In the onCreate() method of my main activity I call the constructor of my dbManager, I call the open function that creates an instance of a SQLiteOpenHelper and than I call the getWritableDatabase() on it. Within the UIThread I add records to the database and save those records to an ArrayList. Two other threads check the ArrayList do stuff and than update the list and the database. Now I want to add a button in the UI to delete both database and list records using an AsyncTask. I've read that the SqliteOpenHelper object holds on one database connection. So if there is one helper instance there is only one db connection. This connection could be used from multiple threads and the SqliteDatabase object uses java locks to keep access serialize. So If I have multiple threads writing on the database, a thread will wait until the previous one has finished his operation?
Adding the new functionality (remove all) could create problems, because I have to avoid that one of the two threads may try to edit a record that no longer exists. How can I achieve my goal? Thanks.

You should implement consumer and producer pattern, it will help you to build a queue of requests, don't write parallel to database. — hackp0int, Apr 30 '13 at 14:11
depends on how you implement your edit, but an sql update return n, where n is the number of items modified, which totally can be 0 if no record exists matching the where clause. — njzk2, Apr 30 '13 at 14:22
1) You shouldn't do DB operations on UI Thread. 2) Have only ONE thread reading/writing the ArrayList. @IamStalker why don't write parallel to DB? — m0skit0, Apr 30 '13 at 14:28
Cause you have to take care of optimistic-concurrency, that's because SQL-lite do not support concurrency. — hackp0int, Apr 30 '13 at 20:29
true, sqlite does not support concurrency by itself, but Android's SQLiteOpenHelper does support concurrency perfectly. You just need to make sure all your threads are using the same instance! — Amir Uval, Apr 30 '13 at 23:33

Amir Uval · Accepted Answer · 2013-04-30T14:46:07.010

4

The databese:

As long as you use only one helper, you are thread safe without a need to do anything.
In a multi threaded app, you'll need synchronized only on the creation of the helper. You can use transacion if you want to define a critical section (more than one atomic operation).
Wrap each call with getWritableDatabase() and close(false):

public void doSomething() {
    SQLiteDatabase tdb = getWritableDatabase();
    dbInstance.writeSomething(tdb, 1);
    close(false);
}

In this way I have multiple threads reading and writing to the database without any problem.

You app logic:

Use memory to keep track of object state, and use the database as storage only. So if one thread deletes rows from the database - you can immediately update your objects in memory, and handle your ui accordingly.
If your data is very large, than you can keep in memory only a SparseArray of dirty rows id.
It may be useful to synchronize some operations here.

edited Apr 30 '13 at 14:46

answered Apr 30 '13 at 14:29

Amir Uval

14,425
4
50
74

The database helper is doing all the synchronization needed to maintain the database integrity. And unrelated to the db - in the app logic, the developer may need to sync around critical sections in the code to maintain business logic integrity. – Amir Uval Apr 30 '13 at 23:26
There is no such limitation, a hundred threads can read and write together to the same DB without any problem. Using one helper. This is *not* where the problem lies when writing multi-threaded app. – Amir Uval May 01 '13 at 07:52
see also http://stackoverflow.com/questions/2493331/what-are-the-best-practices-for-sqlite-on-android/3689883#3689883 - he says exactly as I say: use ONE helper instance, and you'll be ok with multithreaded app – Amir Uval May 01 '13 at 08:27
Dude! you miss understood the whole thing,it's a provider consumer queue, there is nothing to talk about it. – hackp0int May 01 '13 at 10:16
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/29244/discussion-between-uval-and-iamstalker) – Amir Uval May 01 '13 at 12:55

score 0 · Answer 2 · answered Apr 30 '13 at 14:37

So If I have multiple threads writing on the database, a thread will wait until the previous one has finished his operation?

Doesn't need to be so. See this for more details. Anyway, this is up to the DB engine and should be transparent.

Adding the new functionality (remove all) could create problems, because I have to avoid that one of the two threads may try to edit a record that no longer exists. How can I achieve my goal?

Have only one thread update the DB. Other threads only modify the ArrayList -also watch out because ArrayList is not thread-safe, consider using Collections#synchronizedList().

Android: multiple threads writing on SQLite database

2 Answers2