sockeqwe sockeqwe - 5 months ago 27
Android Question

Sqlite Transaction with read and write

is it possible to use a transaction in android to read value that will be deleted later in the same transaction?

Something like this:

SQLiteDatabase db = helper.getReadableDatabase();
db.beginTransaction();

String whereClause = "COL1 = ? AND COL2 = ?";

// make a SELECT Query to read data
Cursor c = db.rawQuery("SELECT * FROM Foo "+whereClause, whereValues);

...
// read Cursor data etc.
...

db = helper.getWriteableDatabase();
// Delete data
db.delete("Foo", whereClause, whereValues);

db.setTransactionSuccessful();
db.endTransaction();


So i want to get my data that Im going to delete, to notify UI components that the data has been deleted.

I work with multiple threads and want to ensure that between quering and deleting no other data in the table has been changed.

I could do that in java with synchronized etc. or lock the whole database (which I would only do as very last option).

So Is it possible and save to use a transaction for this usecase? If yes how do I use getWriteableDatabase() and getReadableDatabase() in a write and read transaction?

Does using a transaction guarantees that exactly those data will be deleted that has been queried before? And it's not possible that another datarecord has been inserted between the SELECT query and the DELETE (for instance by another Thread) ... I want to avoid that a data record will be deleted that has not been queried by the previous SELECT query (because it has been inserted AFTER the SELECT query and BEFORE the DELETE statement)

Example: I have this table called Foo:

ID | COL1 | COL2
---|------|-----
1 | 1 | 1
---|------|-----
2 | 0 | 1
---|------|-----
3 | 1 | 1


Lets assume I use the transaction approach:
I would execute
SELECT * FROM Foo WHERE COL1 = 1 AND COL2 = 1
. As result I will get the rows with the ID = 1 and ID = 3.

Lets assume right now, before executing the DELETE statemet another Thread have inserted a row so that the table looks like this:

ID | COL1 | COL2
---|------|-----
1 | 1 | 1
---|------|-----
2 | 0 | 1
---|------|-----
3 | 1 | 1
---|------|-----
4 | 1 | 1


Next the DELETE statement will be executed:
DELETE FROM Foo WHERE COL1 = 1 AND COL2 = 1


So the rows with the ID = 1, ID = 3 and ID = 4 are deleted.

But the resultset of the previous SELECT was the rows with the ID = 1 and ID = 3.
So I have lost row with ID = 4 without notice that.

And thats exactly what I'm trying to avoid.

I guess that will be a usecase for a LOCK, but I don't like LOCKS ...

Answer

Try this:

long id;
db = helper.getWriteableDatabase();

db.beginTransaction();
Cursor c = db.rawQuery("SELECT * FROM Foo " + whereClause, whereValues);
try {
    while (cursor.moveToNext()) {
        id = cursor.getLong(cursor.getColumnIndex("_id"));
        db.delete("Foo", "_id = " + id,null);
    }
    db.setTransactionSuccessful();

}catch {
            //Error in between database transaction 
}finally {
    db.endTransaction();

}

I am not sure if i can iterate a cursor and delete rows at same time. If not, you can save the id´s in an array first.

Comments