Rolling Back Transactions

Rolling back a transaction undoes its changes. You can roll back the entire current transaction, or you can roll it back only to a specified savepoint.

To roll back the current transaction only to a specified savepoint, you must use the ROLLBACK statement with the TO SAVEPOINT clause.

To roll back the entire current transaction, use either the ROLLBACK statement without the TO SAVEPOINT clause, or (in the SQL Developer environment) the Rollback Changes icon.

Rolling back the entire current transaction:

Ends the transaction
Reverses all of its changes
Erases all of its savepoints
Releases any transaction locks

Rolling back the current transaction only to the specified savepoint:

Does not end the transaction
Reverses only the changes made after the specified savepoint
Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)
Releases all table and row locks acquired after the specified savepoint

Other transactions that have requested access to rows locked after the specified savepoint must continue to wait until the transaction is either committed or rolled back. Other transactions that have not requested the rows can request and access the rows immediately.

To see the effect of a rollback in SQL Developer, you might have to click the Refresh icon.

As a result of the example in About the INSERT Statement, the REGIONS table has a region called 'Middle East and Africa' and a region called 'Africa'. The following example corrects this problem (a very simple transaction) and checks the change, but then rolls back the transaction and then checks the rollback.

Rolling Back an Entire Transaction

Before transaction:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

Transaction (change table):

UPDATE REGIONS
SET REGION_NAME = 'Middle East'
WHERE REGION_NAME = 'Middle East and Africa';

Result:

1 row updated.

Check change:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East
         5 Africa

Roll back transaction:

ROLLBACK;

Result:

Rollback complete.

Check rollback:

SELECT * FROM REGIONS
ORDER BY REGION_ID;

Result:

 REGION_ID REGION_NAME
---------- -------------------------
         1 Europe
         2 Americas
         3 Asia
         4 Middle East and Africa
         5 Africa

Rolling Back Transactions

Related Topics