Back to: Structured Query Language (SQL)
We are a little spoiled when we use most software because much of it automatically keeps track of changes and allows us to “undo” or “autosave”. In MySQL we can protect our data with some ability to rollback. Whenever you execute a transaction in MySQL, that transaction is saved.
There are occasions where you may want to try some steps but have the ability to ROLLBACK to a predefined point. To achieve this we turn off the AUTOCOMMIT feature and manually COMMIT changes. This is like a restore point that we can ROLLBACK to.
With AUTOCOMMIT enabled, after EVERY executed commend, a COMMIT is made, so you can NOT ROLLBACK.
To disable AUTOCOMMIT we use the phase SET AUTOCOMMIT = OFF; the execute. You can do the same thing in MySQL Workbench:
After disabling AUTOCOMMIT, you can now set a manual “restore point” by using the command COMMIT;
COMMIT;Now we can make a change like deleting an employee, but OOPS, I accidentally put a semicolon in the wrong place and deleted the entire table:
DELETE FROM employees;
WHERE employee_id = 6;
SELECT * FROM employees;Fortunately, we can now use the ROLLBACK; command to restore our table to the last COMMIT; point.