Updating two Tables at same time but rollback if update fails
Let's say we have the above diagram. We have two tables, which I want to update.In case one update fails, I want to rollback both updates.
I want the best performance, so I realized there is only one solution which I can think of:
viewModel.updateTable1(
updatedId1 ->{
viewModel.updateTable2(
updatedId2 ->{
Toast.makeText(this,"great, both succes!!!! :)",Toast.LENGTH_LONG);
},
er->{
//somehow a rollback
}
},
er->{
Log.e("error",er.getMessage())
}
);
However I am by FAR not a big fan of my solution as, I need to wait till table1 gets updated and then I can update table2. So my question would be is it possible to update 2 or more tables but ROLLBACK BOTH TRANSACTION in case one update fails?
Your best option is to run both updates in a single transaction.
An example, albeit in Java, that is purposefully designed to fail (on the 2nd update) and thus rollback the first successful update.
So first we have an Update Method (function in Kotlin) that utilises and update Dao @Update int update(Bookmark bookmark); that is in the AllDao interface :-
public static void updateTwo(TheDatabase db, Bookmark bookmark1, Bookmark bookmark2) {
boolean updateOk = true;
AllDao dao = db.getAllDao();
SupportSQLiteDatabase sdb = db.getOpenHelper().getWritableDatabase();
sdb.beginTransaction();
if (dao.update(bookmark1) > 0) {
if (dao.update(bookmark2) < 1) {
Log.d("UPDATETWO","Second Update failed");
updateOk = false;
}
} else {
Log.d("UPDATETWO","First Update failed to update anything.");
updateOk = false;
}
if (updateOk) {
Log.d("UPDATETWO","Both updates OK");
sdb.setTransactionSuccessful(); //<<<<< ONLY SET TRANSACTION OK if both updates (aka ROLLBACK if not both OK)
}
sdb.endTransaction();
}
- Note SupportSQliteDatabase used but really
runInTransactionshould be used (am looking at using this, so I may update answer) - Logging included for showing results.
- P.S. I have allowed running on main thread for convenience/brevity
And in an Activity I have code that is designed to not update (i.e. the update fails (albeit that it doesn't it just doesn't update which I assume is what you mean by fail rather than an exception being thrown)):-
public class MainActivity extends AppCompatActivity {
TheDatabase db;
AllDao dao;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = TheDatabase.getInstance(this);
dao = db.getAllDao();
dao.deleteAll();
Bookmark b1 = new Bookmark(), b2 = new Bookmark();
// Insert Row
b1.setPostTitle("P1");
b1.setPostUrl("U1");
b1.setId(dao.insert(b1)); // sets the id so row can be updated
// Prepare for update
b1.setPostUrl("URL1"); // change so there is an update to be performed i.e. change U1 to URL1
// Prepare a second (no-existent so doomed to fail) Row for update
b2.setPostTitle("P2");
b2.setPostUrl("U2");
// Do the update
Bookmark.updateTwo(db,b1,b2);
for (Bookmark b: dao.getAll()) {
Log.d("BOOKMARKINFO","ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
}
}
}
Result
The Log :-
D/UPDATETWO: Second Update failed
D/BOOKMARKINFO: ID = 1 PostTitle = P1 PostURL =U1
i.e. the Row is back to U1 not URL1 (aka ROLLED BACK)
If before the update the line b2.setId(dao.insert(b2)); is added (so the 2nd row exists and thus will be updated (even though no data is actually change)) then the Log is :-
D/UPDATETWO: Both updates OK
D/BOOKMARKINFO: ID = 1 PostTitle = P1 PostURL =URL1
D/BOOKMARKINFO: ID = 2 PostTitle = P2 PostURL =U2
i.e. first row is URL1 (aka NOT ROLLED BACK)
- The above as it stands is only suitable for single row updates as it only checks if any update has been made rather than the number of updates matches.
Update
here's the equivalent using runInTransaction. It appears that "The transaction will be marked as successful unless an exception is thrown in the Runnable." is only if an SQLite Exception is thrown. As such a precarious dao was added to force an exception i.e.
@Insert(onConflict = OnConflictStrategy.ABORT)
long abort(Bookmark bookmark);
- Precarious as it assumes that the Bookmark exists and therefore cannot be inserted as the UNIQUE constraint is violated (id column).
The alternative code being :-
public static void update2(TheDatabase db,Bookmark b1, Bookmark b2) {
try {
db.runInTransaction(new Runnable() {
@Override
public void run() {
try {
upd(db, b1, b2);
} catch (Exception e) {
}
}
});
} catch (Exception e) {}
}
private static void upd(TheDatabase db, Bookmark b1, Bookmark b2) throws Exception {
boolean updateOk = true;
AllDao dao = db.getAllDao();
if (dao.update(b1) > 0) {
if(dao.update(b2) < 1) {
updateOk = false;
Log.d("UPDATETWO","Second Update failed");
}
} else {
Log.d("UPDATETWO","First Update failed to update anything.");
}
if (!updateOk) {
dao.abort(b1); // Force SQLite Exception to ROLLBACK
} else {
Log.d("UPDATETWO","Both updates OK");
}
}
The following code in the activity was used for testing :-
dao.deleteAll();
Bookmark b1 = new Bookmark(), b2 = new Bookmark(), b3 = new Bookmark();
// Insert Row
b1.setPostTitle("P1");
b1.setPostUrl("U1");
b1.setId(dao.insert(b1)); // sets the id so row can be updated
// Prepare for update
b1.setPostUrl("URL1"); // change so there is an update to be performed i.e. change U1 to URL1
// Prepare a second (no-existent so doomed to fail) Row for update
b2.setPostTitle("P2");
b2.setPostUrl("U2");
// Do the update
Bookmark.updateTwo(db,b1,b2);
logInfo("-A1");
b2.setId(dao.insert(b2));
Bookmark.updateTwo(db,b1,b2);
logInfo("-A2");
b1.setPostUrl("U R L 1");
b3.setPostTitle("P3");
b3.setPostUrl("U3");
Bookmark.update2(db,b1,b3);
logInfo("-B1");
b3.setId(dao.insert(b3));
Bookmark.update2(db,b1,b3);
logInfo("-B2");
}
private void logInfo(String extra) {
for (Bookmark b: dao.getAll()) {
Log.d("BMINFO" + extra,"ID = " + b.getId() + " PostTitle = " + b.getPostTitle() + " PostURL =" + b.getPostUrl());
}
}
This uses both methods -A (as per log) using the SupportSQliteDatabase the -B using runInStransaction. The Log :-
2021-07-01 13:19:50.944 D/UPDATETWO: Second Update failed
2021-07-01 13:19:50.946 D/BMINFO-A1: ID = 1 PostTitle = P1 PostURL =U1
2021-07-01 13:19:50.949 D/UPDATETWO: Both updates OK
2021-07-01 13:19:50.951 D/BMINFO-A2: ID = 1 PostTitle = P1 PostURL =URL1
2021-07-01 13:19:50.951 D/BMINFO-A2: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.953 D/UPDATETWO: Second Update failed
2021-07-01 13:19:50.963 D/BMINFO-B1: ID = 1 PostTitle = P1 PostURL =URL1
2021-07-01 13:19:50.963 D/BMINFO-B1: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.966 D/UPDATETWO: Both updates OK
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 1 PostTitle = P1 PostURL =U R L 1
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 2 PostTitle = P2 PostURL =U2
2021-07-01 13:19:50.969 D/BMINFO-B2: ID = 3 PostTitle = P3 PostURL =U3
Edit re the comment:-
Wouldnt't the anotation Transaction safe me the "boilercode"
Tried using :-
@Transaction
public static boolean update3(AllDao dao,Bookmark b1, Bookmark b2) {
boolean updateOk = true;
if (dao.update(b1) > 0) {
if (dao.update(b2) < 1) {
updateOk = false;
}
}
if (!updateOk) {
dao.abort(b1);
}
return updateOk;
}
However, the abort results in a crash e.g. :-
E/AndroidRuntime: FATAL EXCEPTION: main
Process: a.a.so67958704javaroomconvertexistingdb, PID: 6136
java.lang.RuntimeException: Unable to start activity ComponentInfo{blah....}: android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed: mylist_data.ID (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2913)
....
So I believe that @Transaction, unlike runInTransaction, isn't designed for Rollback.
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?