MySQL - ROLLBACK Statement

In general, transactions are units or sequences of work accomplished in a logical order, whether in a manual fashion by a user or automatically by some sort of a database program.

A database transaction is the propagation of one or more changes as a single action on the database. MYSQL provides supports for transactions using the SET autocommit, START TRANSACTION, COMMIT, and ROLLBACK statements.

The COMMIT statement saves all the modifications made in the current.

ROLLBACK Statement

The ROLLBACK operation undoes all the changes done by the current transaction i.e. If you invoke this statement, all the modifications are reverted until the last commit or the START TRANSACTION statement.

Syntax

Following is the syntax of the MySQL ROLLBACK statement −

ROLLBACK

Example

MySQL saves the changes done after the execution of each statement. To save changes automatically, set the autocommit option as shown below −

SET autocommit=0;

Assume we have created a table with name Players in MySQL database using CREATE statement as shown below −

CREATE TABLE Players(
   ID INT,
   First_Name VARCHAR(255),
   Last_Name VARCHAR(255),
   Date_Of_Birth date,
   Place_Of_Birth VARCHAR(255),
   Country VARCHAR(255),
   PRIMARY KEY (ID)
);

Now, we will insert 3 records in Players table using INSERT statements −

Insert into Players values
(1, 'Shikhar', 'Dhawan', DATE('1981-12-05'), 'Delhi', 'India');
(2, 'Jonathan', 'Trott', DATE('1981-04-22'), 'CapeTown', 'SouthAfrica'); 
(3, 'Kumara', 'Sangakkara', DATE('1977-10-27'), 'Matale', 'Srilanka');

Following query saves the changes −

COMMIT;

Now, let us add more records using the INSERT statements −

Insert into Players values
(4, 'Virat', 'Kohli', DATE('1988-11-05'), 'Delhi', 'India');
(5, 'Rohit', 'Sharma', DATE('1987-04-30'), 'Nagpur', 'India');
(6, 'Ravindra', 'Jadeja', DATE('1988-12-06'), 'Nagpur', 'India');
(7, 'James', 'Anderson', DATE('1982-06-30'), 'Burnley', 'England');

You can retrieve the contents of the above create table using the SELECT statement as shown below −

SELECT * FROM Players;

Output

Following is the output of the above program −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
4 Virat Kohli 1988-11-05 Delhi India
5 Rohit Sharma 1987-04-30 Nagpur India
6 Ravindra Jadeja 1988-12-06 Nagpur India
7 James Anderson 1982-06-30 Burnley England

Following statement reverts the changes after the last commit.

ROLLBACK;

All the changes done past the last commit will be reverted if we rollback a transaction. Since we have inserted the last 4 records after commit, they will be reverted at the time of roll back.

SELECT * FROM Players;

Output

The above query produces the following output −

ID First_Name Last_Name Date_Of_Birth Place_Of_Birth Country
1 Shikhar Dhawan 1981-12-05 Delhi India
2 Jonathan Trott 1981-04-22 CapeTown SouthAfrica
3 Kumara Sangakkara 1977-10-27 Matale Srilanka
Advertisements