ROLLBACK

 In Oracle, the ROLLBACK command is used to undo changes made during the current transaction. It ensures that any modifications made to the database are reverted, and the data is restored to its previous state before the transaction began. Below are the details and important notes regarding the ROLLBACK command in Oracle:

1. Purpose of ROLLBACK

• Undo Changes: The primary purpose of ROLLBACK is to undo or discard changes made by the INSERT, UPDATE, or DELETE commands within a transaction. It effectively rolls back any changes made since the last commit or the start of the transaction.
• Transactional Integrity: It is used to maintain data integrity by ensuring that incomplete or erroneous transactions do not permanently affect the database.

2. Syntax

The basic syntax for the ROLLBACK command is:

ROLLBACK;

You can also use it with a SAVEPOINT (more on that later):

ROLLBACK TO SAVEPOINT savepoint_name;

3. How ROLLBACK Works

• Transaction Boundaries: A transaction begins when a SQL command modifies data (such as an INSERT, UPDATE, or DELETE statement) and ends when either a COMMIT or ROLLBACK is issued.
• Implicit Commit Points: A ROLLBACK undoes all operations since the beginning of the transaction or since the last SAVEPOINT. It does not require specifying any object or table since it affects the whole transaction.
• Data Reversion: Any data that has been changed or added is returned to its original state prior to the start of the transaction.

4. ROLLBACK and COMMIT

• COMMIT: While ROLLBACK undoes changes, the COMMIT command makes all changes permanent. Once a COMMIT is issued, no further ROLLBACK can undo those changes.
• Inconsistent States: If you issue a ROLLBACK, any modifications or changes that have not been committed to the database will be discarded, ensuring that the database is consistent with the last committed state.

5. Effects of ROLLBACK

• On Data: All changes made since the last COMMIT are reversed.
• On Locks: Locks that were placed during the transaction are released.
• On Temporary Tables: Data in global temporary tables will also be rolled back (depending on the session behavior).

6. Using SAVEPOINT with ROLLBACK

A SAVEPOINT allows you to set a point in the middle of a transaction to which you can roll back later. This allows partial rollbacks rather than undoing the entire transaction.

• Syntax for SAVEPOINT:

·        SAVEPOINT savepoint_name;

• Rolling Back to a SAVEPOINT:

·        ROLLBACK TO SAVEPOINT savepoint_name;

When you issue a ROLLBACK to a savepoint, the changes after the specified SAVEPOINT are undone, while changes before the savepoint are preserved.

7. Impact on DDL Statements

• Data Definition Language (DDL): Commands like CREATE, ALTER, and DROP are implicitly committed. This means you cannot roll back a DDL statement. Once a DDL command is executed, the changes are permanent, and any ROLLBACK will not affect these commands.

8. Nested Transactions

• Oracle does not support true nested transactions. When you issue a ROLLBACK, it applies to the entire transaction, and you cannot roll back changes made by a nested transaction or a sub-transaction.

9. Automatic Rollback in Case of Errors

• In Case of Errors: Oracle automatically performs a ROLLBACK if a fatal error occurs during a transaction. This helps maintain the integrity of the database by ensuring no partial changes are left behind if something goes wrong.

10. Example

Here’s a basic example of how ROLLBACK is used:

-- Begin a transaction (implicit with DML)

INSERT INTO employees (employee_id, name, salary)

VALUES (1001, 'John Doe', 60000);

 

-- Oops! We realized this was a mistake

ROLLBACK;  -- Undo the insertion

After issuing the ROLLBACK, the insertion of the employee is undone.

11. ROLLBACK in PL/SQL

In PL/SQL (Oracle’s procedural extension to SQL), ROLLBACK works in the same way as in regular SQL. You can use it to revert changes made during a PL/SQL block execution.

BEGIN

    INSERT INTO employees (employee_id, name) VALUES (2001, 'Jane Smith');

    -- Some error occurs

    ROLLBACK;  -- Undo the insertion

END;

12. ROLLBACK and Undo Segments

• Oracle uses undo segments to store information about the changes made during a transaction. When a ROLLBACK is executed, Oracle uses the undo segments to restore the original data.
• Undo segments store information about changes such as insertions, deletions, and updates. These segments are automatically managed by Oracle, and you do not need to interact with them directly.

13. Best Practices

• Frequent COMMITs: Use COMMIT frequently to make changes permanent and reduce the need for ROLLBACK. Frequent commits help manage transaction size and performance.
• Transaction Size: Avoid making transactions too large as they might require large amounts of undo space. Breaking large transactions into smaller ones reduces the risk of rolling back large sets of data.
• Error Handling: Always have appropriate error handling in your PL/SQL blocks to ensure that a ROLLBACK happens if something goes wrong during the transaction.

14. Use Cases for ROLLBACK

• Mistakes in Data Entry: If you realize that the data you entered was incorrect and haven't yet committed it, you can use ROLLBACK to revert the changes.
• Business Logic Errors: If a business rule fails during a transaction, such as a failed validation, you can roll back the transaction to maintain data consistency.
• Partial Updates: If you need to partially undo changes made during a long-running transaction, you can set savepoints and roll back to the appropriate point.

15. ROLLBACK in Distributed Transactions

In the context of distributed transactions, ROLLBACK ensures that all changes across all participating databases are rolled back, ensuring consistency.

Summary of Key Points:

• ROLLBACK undoes all changes made in a transaction since the last COMMIT or SAVEPOINT.
• It ensures data integrity by restoring the database to its previous state.
• A ROLLBACK can be issued without specifying a table, and it affects the entire transaction.
• SAVEPOINTs can be used to roll back only part of the transaction.
• DDL commands (like CREATE, DROP, ALTER) cannot be rolled back.
• Automatic Rollback happens when errors occur during a transaction, ensuring database consistency.

In short, ROLLBACK is a critical tool in Oracle for handling transactions and ensuring that invalid or incomplete changes do not get committed to the database.