CODEWITHSUNDEEP
mysqlsqltransactionsinnodb
Googlebot
Googlebot

Reputation: 15683

Mysql transaction rollback on failure in update

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

Upvotes: 10

Views: 14718

Answers (2)

Vyktor
Vyktor

Reputation: 21007

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

Upvotes: 13

Eugen Rieck
Eugen Rieck

Reputation: 65342

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

Upvotes: 5

Related Questions