Mysql transaction rollback on failure in update
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
You will need to do this in some sort of programming logic - maybe a stored procedure is best.
START TRANSACTION- run
UPDATEquery - SELECT ROW_COUNT() INTO some_variable
IF (some_variable>0) THEN[run the other statements includingCOMMIT]ELSE ROLLBACK