SQLite DB, available from multiple threads

Question

Consider the following: I have a Service that is written to the database in AsyncTask . And my activity is reading data from the database (consider a UI thread for simplicity). I am SQLiteOpenHelper database using SQLiteOpenHelper . I create a separate instance in Application onCreate () and then get it in the service and activity. Is there a chance that I will make my database "dead locked"? I used to use ContentProvider for such operations. Although it is based on using a single instance of SQLiteOpenHelper , I decided to simplify my project by excluding ContentProvider .

Consider the code:

 public class App extends Application { private OpenHelper openHelper; @Override public void onCreate(){ super.onCreate(); openHelper=new OpenHelper(); } public OpenHelper getHelper(){ return openHelper; } } 

In action:

 OpenHelper helper=(App)getApplication().getHelper(); SQLiteDatabase db=helper.getReadableDatabase(); // Do reading 

And inside Serice, in a separate thread:

 OpenHelper helper=(App)getApplication().getHelper(); SQLiteDatabase db=helper.getWritableDatabase(); //Do writing 

Would it be safe?

UPD This may be a solution, but not sure how to use it.

Answer 1

Late, late answer. You are in perfect order. This is the right way to do this, actually. See my blog post: http://touchlabblog.tumblr.com/post/24474750219/single-sqlite-connection/ . Spend my profile here. Many examples of this. ContentProvdier is just overhead and not needed if you are not using data outside of your application. Transactions are good to speed things up and (obviously) improve consistency, but not necessary.

Just use one SqliteOpenHelper in your application and you are safe.

Answer 2

My bet: this is unsafe.

To be in a safer position, you must use SQL transactions. Start with beginTransaction() or beginTransactionNonExclusive() and end with endTransaction() . As shown here

Answer 3

This is my solution. I created a class and a private static object to synchronize all access to db

 public class DBFunctions { // ... private static Object lockdb = new Object(); /** * Do something using DB */ public boolean doInsertRecord(final RecordBean beanRecord) { // ... boolean success = false; synchronized (lockdb) { // ... // // here ... the access to db is in exclusive way // // ... final SQLiteStatement statement = db.compileStatement(sqlQuery); try { // execute ... statement.execute(); statement.close(); // ok success = true; } catch (Exception e) { // error success = false; } } return success; } 

}

I tried using the ASYNC task and it works fine. I hope this is the right way to solve the problem.

Any other suggestions

Answer 4

Good question. My first thought is that it’s not safe. However, according to SQLite docs , SQLite can be used in three modes. The default mode is "serialized" mode:

Serialized In serialized mode, SQLite can be safely used across multiple threads without restriction.

Therefore, I assume that it is compiled in serialized mode on Android.

Answer 5

I just saw it while I was looking for something else. This problem looks like it will be effectively resolved using ContentProvider. Thus, both Activity and Service can use the content provider, and this will take care of the problems associated with Db.