mysql失败回滚,更新失败时的MySQL事务回滚

柔情只为你懂 2023-01-24 03:23 97阅读 0赞

![Image 1][]

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

解决方案

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

[Image 1]:

发表评论

表情：
评论列表 （有 0 条评论，97人围观）

还没有评论，来说两句吧...

相关阅读