Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

With PHP and MySQL, should you check for rollback failures?

I'm using PHP's mysqli library. Database inserts and updates are always in a try-catch block. Success of each query is checked immediately (if $result === false), and any failure throws an exception. The catch calls mysqli_rollback() and exits with a message for the user.

My question is, should I bother checking the return value of mysqli_rollback()? If so, and rollback fails, what actions should the code take?

I have a hard time understanding how a rollback could fail (barring some atrocious bug in MySQL). And since PHP is going to exit anyway, calling rollback almost seems superfluous. I certainly think it should be in the code for clarity, but when PHP exits it will close the connection to MySQL and uncommitted transactions are rolled back automatically.

like image 452
giskard22 Avatar asked Aug 23 '12 21:08

giskard22


People also ask

Why rollback is not working in MySQL?

and make sure that you are not using COMMIT after the Query which you need to rollback. Refer Table Engines and Transaction. And When a DB connection is created, it is in auto-commit mode by default.

What is rollback PHP?

Definition and Usage. The rollback() / mysqli_rollback() function rolls back the current transaction for the specified database connection. Tip: Also look at the commit() function, which commits the current transaction, and the autocommit() function, which turns on or off auto-committing database modifications.

What does MySQL rollback do?

MySQL Rollback statement allows you to rollback or undo one more statements that have been recently executed. For example, if you have accidentally deleted or updated rows, you can use MySQL rollback those statements and restore original databases.

Can transaction rollback fail?

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.


1 Answers

if rollback fails (connection failure for example), the changes will be rollbacked after the connection close anyway, so you don't need to handle the error. When you are in transaction, unless you have explicit commit (or you are running in autocommit mode, which means you have commit after each statement), the transaction is being roll back.

If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

The only case you would like to handle rollback error is if you are not exiting from your script, but starting a new transaction later, as starting transaction will implicitly commit the current one. Check out Statements That Cause an Implicit Commit

like image 96
Maxim Krizhanovsky Avatar answered Nov 10 '22 04:11

Maxim Krizhanovsky