ROLLBACK FAQS

1. What is ROLLBACK in Oracle?

ROLLBACK is a Transaction Control Language (TCL) statement that:

• Undoes all uncommitted changes in the current transaction
• Restores data to its previous committed state
• Releases row-level locks
• Ends the current transaction

It ensures transaction atomicity (A in ACID).

2. When is ROLLBACK used?

ROLLBACK is used when:

• An error occurs during transaction processing
• Business validation fails
• User cancels an operation
• Exception handling requires undoing changes

Example:

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

ROLLBACK;

All updates are undone.

3. What happens internally when ROLLBACK is executed?

When you issue ROLLBACK, Oracle:

1.    Uses undo segments to reverse changes

2.    Restores data blocks to their previous committed state

3.    Releases row-level locks

4.    Marks transaction as rolled back

5.    Ends the transaction

Oracle reads undo records in reverse order and applies them.

4. What is the role of UNDO in ROLLBACK?

Undo data:

• Stores old versions of modified rows
• Is generated during DML operations
• Is used to:
• Roll back transactions
• Provide read consistency
• Support Flashback features

Without undo, rollback would not be possible.

5. What is the difference between ROLLBACK and COMMIT?

Feature	COMMIT	ROLLBACK
Saves changes	Yes	No
Undoes changes	No	Yes
Releases locks	Yes	Yes
Ends transaction	Yes	Yes
Uses undo	Marks as committed	Applies undo to reverse

6. What is partial rollback?

You can rollback to a SAVEPOINT instead of the entire transaction.

Example:

SAVEPOINT before_update;

 

UPDATE employees SET salary = salary + 1000 WHERE department_id = 10;

 

ROLLBACK TO before_update;

This undoes changes after the savepoint but keeps earlier changes.

7. What is SAVEPOINT?

SAVEPOINT creates a marker within a transaction.

• Allows partial rollback
• Does not end the transaction
• Multiple savepoints can exist in a transaction

8. What happens if a session crashes?

If a session crashes before commit:

• Oracle automatically performs rollback during recovery
• Uncommitted changes are undone
• Database consistency is maintained

This is part of Oracle’s crash recovery mechanism.

9. Does ROLLBACK free undo space immediately?

Not immediately.

• Undo data remains available for read consistency
• Eventually reused when space is required
• Large rollbacks may take time depending on undo volume

10. Can ROLLBACK be used inside a trigger?

No.

Oracle does not allow:

• COMMIT
• ROLLBACK
• SAVEPOINT

inside triggers (unless using autonomous transactions).

11. What is ROLLBACK FORCE?

Used by DBAs to rollback in-doubt distributed transactions.

Example:

ROLLBACK FORCE 'transaction_id';

Used in distributed database environments when a two-phase commit fails.

12. How does ROLLBACK affect performance?

Large rollbacks:

• Consume CPU and I/O
• Require reading undo data
• Can take significant time
• May impact system performance

Best practice:

• Keep transactions reasonably sized
• Avoid very large uncommitted operations

13. What is statement-level rollback?

If a single SQL statement fails:

• Only that statement is rolled back
• The overall transaction remains active

Example:

UPDATE employees SET salary = salary * 1.1;

If this fails, only that failed statement is undone, not previous successful statements.

14. What are common mistakes with ROLLBACK?

• Forgetting to commit before closing session
• Very large transactions causing long rollback times
• Not using savepoints in complex transactions
• Assuming rollback undoes committed changes (it does not)

15. What happens after ROLLBACK?

After rollback:

• Transaction ends
• All locks are released
• A new transaction starts with the next DML statement
• Data returns to last committed state

16. Best Practices for ROLLBACK

• Use savepoints for complex transactions
• Handle exceptions properly in PL/SQL

Example:

BEGIN

   UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

EXCEPTION

   WHEN OTHERS THEN

      ROLLBACK;

END;

• Keep transactions short
• Avoid massive uncommitted changes
• Monitor undo tablespace usage