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– hackp0int2013-04-30 14:11:15 +00:00Commented Apr 30, 2013 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– njzk22013-04-30 14:22:36 +00:00Commented Apr 30, 2013 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– m0skit02013-04-30 14:28:53 +00:00Commented Apr 30, 2013 at 14:28
-
Cause you have to take care of optimistic-concurrency, that's because SQL-lite do not support concurrency.hackp0int– hackp0int2013-04-30 20:29:10 +00:00Commented Apr 30, 2013 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– Amir Uval2013-04-30 23:33:01 +00:00Commented Apr 30, 2013 at 23:33
2 Answers
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.
5 Comments
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().
