sqlite - 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;
    }
}

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.