MySQL Rollback Changes

All user activity in InnoDB takes place within a transaction. Each SQL query produces its own transaction when the auto-commit mode is enabled. Every time a new connection is made, MySQL automatically initiates a commit after each SQL statement if the statement did not result in an error. The behavior to commit or roll back when a statement returns an error depends on the error.

Every SQL statement is immediately committed as you execute it when the auto-commit mode is set by default while connecting to the MySQL server.

Use the SQL statement SET autocommit = 0 to disable auto-commit, and then end each transaction with COMMIT or ROLLBACK, as appropriate, to use multiple-statement transactions. This will allow you to specify the sequence of transactions and control it to commit or roll back changes.

SET AUTOCOMMIT = 0;
–Result before insertion
SELECT * FROM tblEmployee;

INSERT INTO tblEmployee
(id, first_name, middle_name, last_name, Address, City, Country) VALUES (1004,’Shaheen’,”,”,”,”,”);

–Result after insertion
SELECT * FROM tblEmployee;

— Now let’s UNDO the above-specified INSERT statement
ROLLBACK;

–Result after rollback
SELECT * FROM tblEmployee;