Mysql transaction rollback on failure in update
13,813
Solution 1
Here is in PHP (haven't tested, needs adapting to your situation):
mysql_query('START TRANSACTION;')
mysql_query("UPDATE posts SET status='approved' where post_id='id' AND status != 'approved';");
if (mysql_affected_rows()){
mysql_query('COMMIT');
} else {
mysql_query('ROLLBACK');
}
Or, If you want to be clever and do it in SQL (using ROW_COUNT()
and IF
):
START TRANSACTION;
UPDATE posts SET status='approved' where post_id='id' AND status != 'approved';
SELECT ROW_COUNT() INTO @affected_rows;
-- .. other queries ...
IF (affected_rows > 0) THEN
COMMIT;
ELSE
ROLLBACK;
END IF
Solution 2
You will need to do this in some sort of programming logic - maybe a stored procedure is best.
START TRANSACTION
- run
UPDATE
query - SELECT ROW_COUNT() INTO some_variable
-
IF (some_variable>0) THEN
[run the other statements includingCOMMIT
]ELSE ROLLBACK
Comments
-
Googlebot almost 2 years
With a simple transaction as
START TRANSACTION; UPDATE posts SET status='approved' where post_id='id' AND status != 'approved'; .. other queries ... COMMIT;
I want to perform the transaction only once when changing the status; but the above
UPDATE
will not give an error to rollback the transaction when no row is updated.How can I limit the transaction to commit only if the row is updated (I mean the status is changed).
-
Googlebot about 12 yearsPerfect! As a matter of fact, I am in PHP :)
-
Vyktor about 12 years@Ali I recommend updating tags than :P
-
MatBailie about 12 yearsDon't run "the other queries" then roll them back, that's a bit inneficient. Just move them into the same code block as the COMMIT, so they only ever execute if needed (as opposed to always executing, then rolling them back sometimes).
-
Googlebot about 12 years@Dems very good point; I always try to avoid ROLLBACK and execute COMMIT if possible.
-
Vyktor about 12 years@Dems I assumed that he may have more queries which will affect records and which will matter... So the real condition would be:
(affected_1 + affected_2 + .... > 0) THEN
but in the case that this is the only relevant one, than you're of course right.