Mysql transaction rollback on failure in update

Question

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).

score 12 · Accepted Answer · edited May 29 '14 at 00:46

12

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

edited May 29 '14 at 00:46

Bardi Harborow

1,803
1
28
41

answered Feb 17 '12 at 12:00

Vyktor

20,559
6
64
96

Perfect! As a matter of fact, I am in PHP :) – Googlebot Feb 17 '12 at 12:12
@Ali I recommend updating tags than :P – Vyktor Feb 17 '12 at 12:14
Don'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). – MatBailie Feb 17 '12 at 12:17
@Dems very good point; I always try to avoid ROLLBACK and execute COMMIT if possible. – Googlebot Feb 17 '12 at 12:21
@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. – Vyktor Feb 17 '12 at 12:23

score 5 · Answer 2 · answered Feb 17 '12 at 11:55

5

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 including COMMIT] ELSE ROLLBACK

answered Feb 17 '12 at 11:55

Eugen Rieck

64,175
10
70
92

Mysql transaction rollback on failure in update

2 Answers2

Linked