Menu
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).
876
To have possibility to ROLLBACK DML statements (like INSERT , UPDATE or DELETE queries) you should use transaction blocks: START TRANSACTION; UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6; -- and more DML queries COMMIT; -- or ROLLBACK; Since transaction was COMMIT ed it can not be rolled back.
This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. So if you need to do transactions yourself, you must turn off the autocommit mode by AUTOCOMMIT = 0 .
By setting XACT_ABORT to ON and we can rollback all the statements inside a transaction when an error occurred. Thus, let's rewrite the code again in this manner. It will also roll back the transaction when the error occurred in the third statement.
If a rollback fails, then you would have a serious problem. The reliability of the database cannot be guaranteed. In other words; you probably have some sort of corruption in your transaction log and will end up with an inconsistent database.
To roll back the current transaction and cancel its changes, you use the ROLLBACK statement. To disable or enable the auto-commit mode for the current transaction, you use the SET autocommit statement. By default, MySQL automatically commits the changes permanently to the database.
The rollback SQL statement is used to manually rollback transactions in MS SQL Server. Transactions in SQL Server are used to execute a set of SQL statements in a group.
ROLLBACK rolls back the current transaction, canceling its changes. SET autocommit disables or enables the default autocommit mode for the current session. By default, MySQL runs with autocommit mode enabled. This means that, when not otherwise inside a transaction, each statement is atomic, as if it were surrounded by START TRANSACTION and COMMIT.
MySQL provides us with the following important statement to control transactions: To start a transaction, you use the START TRANSACTION statement. To commit the current transaction and make its changes permanent, you use the COMMIT statement. To roll back the current transaction and cancel its changes, you use the ROLLBACK statement.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
