Tutorial

SQL Commit And Rollback

Published on August 3, 2022
author

Meghna Gangwar

SQL Commit And Rollback

The most important aspect of a database is the ability to store data and the ability to manipulate data. COMMIT and ROLLBACK are two such keywords which are used in order store and revert the process of data storage. These keywords are usually used in context with a transaction. Let’s try to understand the details about COMMIT and ROLLBACK.

SQL Commit and Rollback

COMMIT and ROLLBACK are performed on transactions. A transaction is the smallest unit of work that is performed against a database. Its a sequence of instructions in a logical order. A transaction can be performed manually by a programmer or it can be triggered using an automated program.

SQL Commit

COMMIT is the SQL command that is used for storing changes performed by a transaction. When a COMMIT command is issued it saves all the changes since last COMMIT or ROLLBACK.

Syntax for SQL Commit

COMMIT;

The syntax for commit includes just one keyword COMMIT.

SQL Commit Example

Let us consider the following table for understanding Commit in a better way. Customer:-

CUSTOMER ID CUSTOMER NAME STATE COUNTRY
1 Akash Delhi India
2 Amit Hyderabad India
3 Jason California USA
4 John Texas USA

Now let us delete one row from the above table where State is “Texas”.

DELETE from Customer where State = 'Texas';
SQL Commit
SQL Delete without Commit

Post the DELETE command if we will not publish COMMIT, and if the session is closed then the change that is made due to the DELETE command will be lost. Updated Command with COMMIT

DELETE from Customer where State = 'Texas';
COMMIT;
SQL transaction commit
SQL Commit Execution

Using the above-mentioned command sequence will ensure that the change post DELETE command will be saved successfully.

Output After Commit

CUSTOMER ID CUSTOMER NAME STATE COUNTRY
1 Akash Delhi India
2 Amit Hyderabad India
3 Jason California USA
SQL Commit example
Table After SQL Commit

SQL RollBack

ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK.

Syntax for SQL Rollback

ROLLBACK;

The syntax for rollback includes just one keyword ROLLBACK.

SQL Rollback Example

Let us consider the following table for understanding Rollback in a better way. Customer:-

CUSTOMER ID CUSTOMER NAME STATE COUNTRY
1 Akash Delhi India
2 Amit Hyderabad India
3 Jason California USA
4 John Texas USA

Now let us delete one row from the above table where State is “Texas”.

DELETE from Customer where State = 'Texas';
SQL Delete without rollback
SQL Delete without Rollback

Post the DELETE command if we publish ROLLBACK it will revert the change that is performed due to the delete command. Updated Command with ROLLBACK

DELETE from Customer where State = 'Texas';
ROLLBACK;
SQL rollback example
SQL Delete with Rollback

Using the above-mentioned command sequence will ensure that the change post DELETE command will be reverted successfully.

Output After Rollback

CUSTOMER ID CUSTOMER NAME STATE COUNTRY
1 Akash Delhi India
2 Amit Hyderabad India
3 Jason California USA
4 John Texas USA
sql rollback command
Table after executing Rollback

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors
Default avatar
Meghna Gangwar

author

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 
JournalDev
DigitalOcean Employee
DigitalOcean Employee badge
January 21, 2022

Very well explained! You really make it easy for me🥰

- Irsa

    JournalDev
    DigitalOcean Employee
    DigitalOcean Employee badge
    July 12, 2019

    Post the DELETE command if we will not publish COMMIT, and if the session is closed then the change that is made due to the DELETE command will be lost. Updated Command with COMMIT DELETE from Customer where State = ‘Texas’; COMMIT; SQL transaction commit SQL Commit Execution Using the above-mentioned command sequence will ensure that the change post DELETE command will be saved successfully. Output After Commit CUSTOMER ID CUSTOMER NAME STATE COUNTRY 1 Akash Delhi India 2 Amit Hyderabad India 3 Jason California USA SQL Commit example Table After SQL Commit SQL RollBack ROLLBACK is the SQL command that is used for reverting changes performed by a transaction. When a ROLLBACK command is issued it reverts all the changes since last COMMIT or ROLLBACK. Syntax for SQL Rollback ROLLBACK; The syntax for rollback includes just one keyword ROLLBACK. SQL Rollback Example Let us consider the following table for understanding Rollback in a better way. Customer:- CUSTOMER ID CUSTOMER NAME STATE COUNTRY 1 Akash Delhi India 2 Amit Hyderabad India 3 Jason California USA 4 John Texas USA Now let us delete one row from the above table where State is “Texas”. DELETE from Customer where State = ‘Texas’; SQL Delete without rollback SQL Delete without Rollback Post the DELETE command if we publish ROLLBACK it will revert the change that is performed due to the delete command. Updated Command with ROLLBACK DELETE from Customer where State = ‘Texas’; ROLLBACK; SQL rollback example SQL Delete with Rollback Using the above-mentioned command sequence will ensure that the change post DELETE command will be reverted successfully. Output After Rollback CUSTOMER ID CUSTOMER NAME STATE COUNTRY 1 Akash Delhi India 2 Amit Hyderabad India 3 Jason California USA 4 John Texas USA sql rollback command Table after executing Rollback

    - dfcvc d

      Try DigitalOcean for free

      Click below to sign up and get $200 of credit to try our products over 60 days!

      Sign up

      Join the Tech Talk
      Success! Thank you! Please check your email for further details.

      Please complete your information!

      Featured on Community

      Get our biweekly newsletter

      Sign up for Infrastructure as a Newsletter.

      Hollie's Hub for Good

      Working on improving health and education, reducing inequality, and spurring economic growth? We'd like to help.

      Become a contributor

      Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

      Welcome to the developer cloud

      DigitalOcean makes it simple to launch in the cloud and scale up as you grow — whether you're running one virtual machine or ten thousand.

      Learn more