Rollback multiple update query if one fails

Question

So what I am doing is am having multiple UPDATE queries, which changes the group name in the tbl_groups table and then updates all the users in tbl_users which belongs to that group, but if the user update query fails, it updates group, but I want to update both together or none, am using PHP and MySQL.

Answer 1

It sounds like all you need to do is use transactions.

You must use InnoDB tables to use transactions (actually, some other DB engines have transactions, but InnoDB is most common with MySQL).

Issue the "BEGIN TRANSACTION" query before the first update.

If any query fails, issue the "ROLLBACK" query to undo everything.

It's really pretty simple.

And if you ever decide you want to do a partial rollback (back to some point after the beginning of the transaction), then you can use "ROLLBACK TO SAVEPOINT savepoint_name". You will have had to issue the "SAVEPOINT savepoint_name" query 1st.

E.g., in PHP

mysql_query("BEGIN TRANSACTION");

$result1 = mysql_query("UPDATE `tbl_groups` SET `user_id` = 5 WHERE `group_id` = 3");
if($result1 === false) {
mysql_query("ROLLBACK");
}

mysql_query("SAVEPOINT savepoint1");

$result2 = mysql_query("UPDATE `tbl_users` SET `group_id` = 3 WHERE `user_id` = 5");

if($result === false) {
ROLLBACK TO SAVEPOINT savepoint1;
}
// COMMIT saves the changes to the db, making them visible to other sessions
// if the ROLLBACK TO SAVEPOINT statement executed, then only changes up to that SAVEPOINT will be saved
// if no ROLLBACK statements were executed, then all changes will be saved (assuming no MySQL errors that cause implicit ROLLBACK)
mysql_query('COMMIT');