SQL COMMIT & ROLLBACK commands

What are COMMIT and ROLLBACK?

COMMIT and ROLLBACK are two of the most frequently used in Transaction SQL (T-SQL).

During SQL Transaction, COMMIT and ROLLBACK commands are used to maintain the integrity of data during a SQL Transaction such as executing an UPDATE or DELETE command.

A SQL Transaction only exists on the current session and without committing, the changes might be lost if the session is terminated. So COMMIT is the SQL command that saves the changes to the server permanently.

ROLLBACK is the SQL command that reverts the change from a SQL Transaction when it’s not committed to the server. And same as COMMIT, this revert action creates permanent results on the database.

Syntax

For COMMIT:

COMMIT;

For ROLLBACK:

ROLLBACK;

Example

We have table departments:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources
d004	Production
d005	Development
d006	Quality Management
d007	Sales
d008	Research
d009	Customer Service

Now we delete the Customer Service department:

DELETE FROM departments WHERE dept_no = 'd009';

And commit it to the server to make the changes permanent:

COMMIT;

The new table after changes:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources
d004	Production
d005	Development
d006	Quality Management
d007	Sales
d008	Research

Now we delete the Research department

DELETE FROM departments WHERE dept_no = 'd008';

And delete the Sales department too:

DELETE FROM departments WHERE dept_no = 'd007';

And rollback the changes:

ROLLBACK;

Then the table after ROLLBACK:

dept_no	dept_name
d001	Marketing
d002	Finance
d003	Human Resources
d004	Production
d005	Development
d006	Quality Management
d007	Sales
d008	Research

So the ROLLBACK command brings the table to the point right after the last COMMIT or ROLLBACK command.

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.