Lesson : 27 - ROLLBACK

ROLLBACK

ROLLBACK statement is used to undo work done in the current transaction. If we specify savepoint, it rollbacks(undo) all changes after the specified savepoint.

Syntax -1  :

ROLLBACK   or ROLLBACK WORK

Syntax - 2

ROLLBACK  TO  <savapoint name>;  or  ROLLBACK  TO  SAVEPOINT <savapoint name>;

Note : WORK  and SAVEPOINT are optional key workds.

Examples :

SQL> insert into emp(empname) values (‘Mr Testing’);

SQL> savepoint step1;

SQL> insert into emp(empname) values (‘Bill’);

SQL> insert into emp(empname) values (‘Robert’);

SQL> insert into emp(empname) values (‘Ramesh’);

SQL> Rollback to savepoint step1;

It  undo last three changes. (Records of Bill,Robert,Rames).

Note :

If we use  ROLLBACK without the TO SAVEPOINT clause, it  performs the following operations,

·         Ends the current transaction

·         Undoes all changes in the current transaction

·         Erases all savepoints in the transaction

·         Releases any transaction locks

If we use  ROLLBACK with the TO SAVEPOINT clause, it  performs the following operations,

·         Rolls back just the portion of the transaction after the savepoint. It does not end the transaction.

·         Erases all savepoints created after that savepoint. The named savepoint is retained, so you can roll back to the same savepoint multiple times. Prior savepoints are also retained.

·         Releases all table and row locks acquired since the savepoint. Other transactions that have requested access to rows locked after the savepoint must continue to wait until the transaction is committed or rolled back. Other transactions that have not already requested the rows can request and access the rows immediately.

Note : Transaction is set of DML  changes between two commits.