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.
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
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.
You should explicitly use ROLLBACK to make it clear what you are doing to anyone who has to read your code later, which may be actually you :)
Also, if you add code, that is to be executed after a COMMIT/ROLLBACK, it's better to trigger it explicitly to have a more predictable state of your data. If you rely on auto-ROLLBACK, that code may be expecting the database to be in it's unmodified state while it's actually still in the uncommited transaction.
It depends how you're doing error handling in general. Using transactions is nice, because if your code inside the transaction throws an exception (which may OR MAY NOT be because a database query failed), normally your exception handler will cause a rollback to happen.
Moreover, if you closed the connection without committing, for example because the process quit unexpectedly, the rollback would happen implicitly, which is generally a good thing (it improves robustness, because when the process restarts, it can try again)
Now, of course, using PHP and the "old" mysql API is not conducive to getting this right, as it doesn't support error reporting by exceptions.
You can however, workaround it by registering a PHP error handler which throws an exception when an error occurs, rather than doing the default "charge headlong into disaster" method:
"Captain, we've hit an iceberg
"Full steam ahead, put more coal in, we'll get to new york soon...
If you have configured PHP to use persistent MySQL connections, then NOT rolling back transactions if something fails will cause problems for subsequent connections. Transactions are not auto-aborted when the script aborts/exits, as the connection is kept alive in the background. Any subsequent re-use of this particular connection will then carry on as if nothing had happened, and you end up in the middle of this old transaction.
As well, if the transaction aquired any locks, these locks still stay active until something causes a rollback or kills the connection (which auto-rollbacks). You may end up causing a log jam for any other queries if they touch the locked tables/rows.
