I have a user table, and I have 5 records.
I deleted two records, then executed the rollback command, it executed successfully.
But that deleted two records not recovered.
The user table engine is InnoDB.
ROLLBACK; 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.
A COMMIT or ROLLBACK statement ends the current transaction and a new one starts. If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.
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.
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.
You should be able to rollback your transaction as the table engine is InnoDB.
Anyways here is the correct way to do transactions,
SET autocommit=0;
START TRANSACTION;
Your Query here.
ROLLBACK;
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. 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
. Refer this link for more info.
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