Q:

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 ...

A:

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.

A:

You don't handle the transaction.
And, if you use it like that, it's perfectly useless.
You should commit (setTransactionSuccessful()) ONLY if no error occurs during the whole data manipulation (in a try...catch).
Also note that a transaction is only useful if you do multiple data manipulations.
A SELECT does not manipulate any data.
So, after all this digression, the answer is... YES, YOU CAN.

