Is it necessary to write ROLLBACK if queries fail?

Question

I write

mysql_query("SET AUTOCOMMIT=0");
mysql_query("START TRANSACTION");

before I write all queries. Then check if all of them are true and then write:

mysql_query("COMMIT");

But if one of query fails, I just pass COMMIT query. So do I really need ROLLBACK function if one of the queries fail? Because without ROLLBACK it also works.
Thanks.

Answer 1

The reason to use transactions is to group multiple changes together so they all succeed atomically, or else if they can't, don't do any of them. In other words, if any change fails, the transaction would leave the database in a logically inconsistent state.

Example: debit one account in one UPDATE, and credit a different account in a separate UPDATE. This represents a money transfer. If the debit succeeds but the credit fails, you should roll back the whole transaction or else it appears that money vanished into thin air.

So the intended usage would be to roll back the transaction if one of the changes fails.

You seem to be saying that in your application, it's okay if one of the changes fails. This makes me think that you've grouped changes into transactions inappropriately.

Decide which group of changes must all succeed together, and put those into one transaction. Any changes that don't go with this group should be in a separate transaction.

Answer 2

I think you're asking if executing ROLLBACK is necessary, since without it the commits still don't get applied. That's technically true, but only because the transaction is still open since you haven't ended it. Anything that implicitly commits the transaction (for example, starting a new transaction) will act as though you ran COMMIT, which is the opposite of what you want