Concurrent writing to android database (from multiple services)?

Question

I've a serious problem with android sqlite database and concurrent writing. For better explanations, I will give you a real life example:

I've an desktop widget, where I'm showing a list of items from my database (and on background I have DataService, which in regular intervals collects fresh data from my remote server, and update my database with them). So - when i click on some item in list, i need to update clicked item (=do write operation) in database. BUT when i click on item exactly in moment, when DataService is updating fresh data in my database, it of course logs an error like this:

android.database.sqlite.SQLiteException: error code 5: database is locked

Normally its hard to simulate, but if u schedule DataService to run for example every 10 seconds (just for demonstration), u can simulate this error very easily.

And my question is, how to handle this? I read in docs, that if there are two write events in same time, only first will be executed, second will be logged as an error. Its sounds strange, there must be another options, for example the second write would wait until first write finish. Or maybe other solution? Trying to read docs, but it seems, that this item is not very good covered in google docs...Almost every info I have, I found on other than official pages.

PS: This is my shortened version of my DBHelper class:

public class DBHelper extends SQLiteOpenHelper {

    private static final String TABLE_NEWS = "News";    
    private static final String COL_ID = "id";
    private static final String COL_TITLE = "title";
    private static final String COL_ALERT = "alert";

    public DBHelper(Context context) {
        super(context, "MY_DB_NAME", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TABLE_NEWS + "(" + COL_ID + " TEXT PRIMARY KEY," + COL_TITLE + " TEXT," + COL_ALERT + " INTEGER" + ")");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NEWS);
        onCreate(db);
    }

    public void addRecords(ArrayList<NewsItem> items) {
        SQLiteDatabase db = this.getWritableDatabase();    
        for (int i = 0; i < items.size(); i++) {
            NewsItem item = items.get(i);    
            ContentValues values = new ContentValues();
            values.put(COL_ID, item.getId());
            values.put(COL_TITLE, item.getTitle());
            values.put(COL_ALERT, item.getAlertMe());    
            db.insert(TABLE_NEWS, null, values);
        }    
        db.close();
    }

    public int updateRecord(NewsItem item) {
        SQLiteDatabase db = this.getWritableDatabase();    
        ContentValues values = new ContentValues();
        values.put(COL_ALERT_ME, item.getAlertMe());
        int updated = db.update(TABLE_NEWS, values, COL_ID + " = ?", new String[] { item.getId() });
        db.close();    
        return updated;
    }
}

Being

You are probably opening and closing multiple database connections in your various threads. This is not very good. You should open a single database connection. – Saddlecloth

thats true, I'm doing something like this in my services: DBHelper dbHelper = new DBHelper (this); etc...but how to create only one instance - what happens if my process will be destroyed? PS: please click on "Answer your quertion" - than I can write directly to you, (if somebody else comments right here, it would be multi chat) – Being

typically add synchronized for each method is related to db open/close. – Soosoochow

Answer 1

You need to use a single SQLiteDatabase object, across all threads (and their hosting components), to get thread safety. Make your DBHelper be a singleton, or use a ContentProvider, to achieve this effect.

Syntactics

thanx, thats exactly what I wanted to hear - all possible options :) I choose to test singleton first (its more simple), and it works like a charm (for now), so I'm closing the question. – Being

Answer 2

The ContentProvider was created for this reason. You can call it from multiple threads for insert/update/delete operations.

http://developer.android.com/reference/android/content/ContentProvider.html

Many people feel that you should only want to use a ContentProvider if you want to share data. That is a great benefit of a ContentProvider, but it's not the only benefit. The major benefit is that when using a ContentProvider, Android will manage the database connections for you.

This is a good tutorial on Content Providers

http://www.vogella.com/articles/AndroidSQLite/article.html

Note: While the JavaDoc for ContentProvider does state that it is REQUIRED in order to share data, it does not mean that it should ONLY be used to share data. In the Application Fundamentals documentation, it has this to say about ContentProviders as well..

Content providers are also useful for reading and writing data that is private to your application and not shared. For example, the Note Pad sample application uses a content provider to save notes.

http://developer.android.com/guide/topics/fundamentals.html#lcycles

Symbolist

maybe I'm same like many people :) but as I read docs, A content provider is only required if you need to share data between multiple applications - which is not my case. I don't need to share db connection between applications, only between services. But I really don't have much experiences in Android, so this is the best way to handle issuse like this? – Being

Again, it is REQUIRED if you want to share data between applications, but that certainly is the ONLY reason to use it. If you read the javadoc for the insert() (and other methods), then you'll see it clearly states that it supports multiple threads. You can build your own threadsafe db manager, or you could just use the content provider and let is do the work. I personally don't like to use content providers, but if I were doing work across multiple threads then I'd consider it for that case. – Symbolist

But, in your case, if you don't want to use a ContentProvider, then perhaps keep a writeable database instance in your Application subclass. You can open it in the onStart() and close it in the onTerminate(). The key is to ensure that you never have more than one instance of the writeable database open at any given time, and the Application lifecycle is a good place to ensure this. – Symbolist

well, I tried to make my DBHelper a Singleton class and it works excellent for now. Anyway thanks, good to know its possible to use Content provider to solve issue like this. – Being

@Symbolist I think specifying that the ContentProvider supports multiple threads as a reason for using it is a little misleading... SQLiteDatabase can be called from multiple threads as well and it is often used as the backing data source for the ContentProvider. – Unmanned

@AlexLockwood - I agree that is wasn't created solely for this reason, but again, the ContentProvider does solve the problem of concurrent access from multiple threads. For some, this alone might be enough of a reason to use it. For others, the complexity of using ContentProviders will force people to implement their own solution. – Symbolist