SQL Commit And Rollback

Filed Under: SQL

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

Comments

  1. dfcvc d says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages