StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSqlite Transaction with read and write
text
Body
copied!<p>is it possible to use a transaction in android to read value that will be deleted later in the same transaction?</p> <p>Something like this:</p> <pre><code>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(); </code></pre> <p>So i want to get my data that Im going to delete, to notify UI components that the data has been deleted.</p> <p>I work with multiple threads and want to ensure that between quering and deleting no other data in the table has been changed.</p> <p>I could do that in java with synchronized etc. or lock the whole database (which I would only do as very last option).</p> <p>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?</p> <p>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)</p> <p>Example: I have this table called Foo:</p> <pre><code> ID | COL1 | COL2 ---|------|----- 1 | 1 | 1 ---|------|----- 2 | 0 | 1 ---|------|----- 3 | 1 | 1 </code></pre> <p>Lets assume I use the transaction approach: I would execute <code>SELECT * FROM Foo WHERE COL1 = 1 AND COL2 = 1</code>. As result I will get the rows with the ID = 1 and ID = 3.</p> <p>Lets assume right now, before executing the DELETE statemet another Thread have inserted a row so that the table looks like this:</p> <pre><code> ID | COL1 | COL2 ---|------|----- 1 | 1 | 1 ---|------|----- 2 | 0 | 1 ---|------|----- 3 | 1 | 1 ---|------|----- 4 | 1 | 1 </code></pre> <p>Next the DELETE statement will be executed: <code>DELETE FROM Foo WHERE COL1 = 1 AND COL2 = 1</code></p> <p>So the rows with the ID = 1, ID = 3 and ID = 4 are deleted.</p> <p>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.</p> <p>And thats exactly what I'm trying to avoid.</p> <p>I guess that will be a usecase for a LOCK, but I don't like LOCKS ...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload