How to handle transactions with SQLite and Android?

Question

I try to include transactions in my application because writing in database is very slow and I saw here and here that transactions are a solution but they are still very confusing to me.

I have Schedule objects that contains an object LineStation, and I want to write them in database using transactions.

Here, the method addSchedules in my class ScheduleDAO, that writes all schedules in database. It contains only one transaction.

public void addSchedules(ArrayList<Schedule> schedulesList) {
    SQLiteDatabase db = this.dh.getWritableDatabase();
    db.beginTransactionNonExclusive();

    for (Schedule schedule : schedulesList) {
        ContentValues values = new ContentValues();

        // insert linestation
        LineStationDAO.getLineStationDAO().addLineStation(schedule.getLineStation());

        values.put(/*...*/);
        /* ... */

        db.insert(DatabaseHandler.TABLE_SCHEDULE, null, values);
    }

    db.setTransactionSuccessful();
    db.endTransaction();
    db.close();
}

And this is, the method addLineStation in my class LineStationDAO that saves the object LineStation given. It's called by addSchedules and doesn't contain transaction because it is "nested" in the addSchedules transaction.

public void addLineStation(LineStation lineStation)  {
    SQLiteDatabase db = this.dh.getWritableDatabase();
    ContentValues values = new ContentValues();

    values.put(/*...*/);
    /* ... */

    db.insert(DatabaseHandler.TABLE_LINE_STATION, null, values); // database is locked (code 5)
    db.close();
}

The LineStation insert implies an SQLiteDatabaseLockedException (database is locked -code 5). What I have done wrong, please? Thanks.

I *think* that is because you get another Database object (`getWriteableDatabase()`), reusing the same instance should fix that. — David Medenjak, Jan 09 '16 at 23:47

score 1 · Accepted Answer · answered Jan 09 '16 at 23:53

The problem is that a database transaction cannot exist across multiple database connections. In the addLineStation method, you are opening a second database connection, when you should be using the one created in addSchedules.

You need to pass the SQLiteDatabase db object down to the addLineStation method like this:

LineStationDAO.getLineStationDAO().addLineStation(db, schedule.getLineStation());

and change this:

public void addLineStation(SQLiteDatabase db, LineStation lineStation)  {
    ContentValues values = new ContentValues();

    values.put(/*...*/);
    /* ... */

    db.insert(DatabaseHandler.TABLE_LINE_STATION, null, values); // database is no longer locked (code 5)
}

Thanks! SQLiteDatabaseLockedException gone. I also add a nested transaction to LineStation and it works perfectly! 30 seconds instead of 3 minutes previously! — Jéjé, Jan 10 '16 at 01:37
No worries. You shouldn't need to add a nested transaction. By having one outer transaction, all the inserts work or they all fail. — DMozzy, Jan 10 '16 at 01:40

How to handle transactions with SQLite and Android?

1 Answers1