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.

–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


–Result after rollback

SELECT * FROM tblEmployee;


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s