Rollback not working in MySQL

mysql

Solution

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.

Problem

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.

Original source