Oracle ROLLBACK Statement: Behind the Scenes

When you execute ROLLBACK;, Oracle discards all uncommitted changes and restores data to its previous state. Understanding ROLLBACK is essential for transaction management and error handling in Oracle Database.

The Complete ROLLBACK Execution Flow

┌─────────────────────────────────────────────────────────────────┐
│                    USER EXECUTES: ROLLBACK;                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 1: LOCATE UNDO DATA                      │
│  • Find transaction's undo segment                              │
│  • Read undo records (before images)                            │
│  • Identify all changes to reverse                              │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 2: APPLY UNDO DATA                       │
│  • Read before images from undo                                 │
│  • Restore original values to buffer cache                      │
│  • Reverse changes in reverse order                             │
│  • UPDATE: Restore old values                                   │
│  • DELETE: Re-insert deleted rows                               │
│  • INSERT: Remove inserted rows                                 │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 3: GENERATE REDO FOR ROLLBACK            │
│  • Yes! ROLLBACK generates redo                                 │
│  • Log the undo application                                     │
│  • Ensure rollback is recoverable                               │
│  • Write to redo log buffer                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 4: UPDATE INDEXES                        │
│  • Reverse index changes                                        │
│  • UPDATE: Restore old index entries                            │
│  • DELETE: Re-add deleted index entries                         │
│  • INSERT: Remove new index entries                             │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 5: MARK TRANSACTION ABORTED              │
│  • Update transaction table                                     │
│  • Mark as rolled back                                          │
│  • Free transaction slot                                        │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 6: RELEASE ALL LOCKS                     │
│  • Release row locks (TX)                                       │
│  • Release table locks (TM)                                     │
│  • Wake up waiting sessions                                     │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 7: FREE UNDO SPACE                       │
│  • Mark undo segment as available                               │
│  • Space can be reused immediately                              │
│  • No retention needed (transaction cancelled)                  │
└──────────────────────────┬──────────────────────────────────────┘
                           │
                           ▼
┌─────────────────────────────────────────────────────────────────┐
│                   STEP 8: RETURN SUCCESS                        │
│  • Return "Rollback complete"                                   │
│  • All changes discarded                                        │
│  • Data restored to pre-transaction state                       │
└─────────────────────────────────────────────────────────────────┘

Key Concepts: ROLLBACK vs COMMIT

How ROLLBACK Works: Step-by-Step

Step 1-2: Locate and Apply Undo Data

Oracle reads undo segments and restores original values.

Undo Application Examples:

-- INSERT followed by ROLLBACK
INSERT INTO employees (employee_id, first_name, salary)
VALUES (101, 'John', 50000);

-- Undo contains: "Delete row with ROWID=xyz"

ROLLBACK;
-- Applies undo: Removes the inserted row
-- Buffer cache updated: Row deleted

-- UPDATE followed by ROLLBACK
UPDATE employees 
SET salary = 60000 
WHERE employee_id = 100;
-- Old value: 50000
-- Undo contains: "Restore salary to 50000 for ROWID=abc"

ROLLBACK;
-- Applies undo: Restores salary to 50000
-- Buffer cache updated: Old value restored

-- DELETE followed by ROLLBACK
DELETE FROM employees WHERE employee_id = 99;
-- Undo contains: Complete row data

ROLLBACK;
-- Applies undo: Re-inserts the entire row
-- Buffer cache updated: Row restored with all columns

Undo Segment Structure:

┌────────────────────────────────────────┐
│     UNDO SEGMENT (Before Rollback)     │
├────────────────────────────────────────┤
│  Transaction ID: 0x0a0b0c              │
│  SCN: 1234567                          │
│                                        │
│  Undo Record 1: DELETE row             │
│  Undo Record 2: Restore salary=50000   │
│  Undo Record 3: Restore dept_id=10     │
│  Undo Record 4: Remove inserted row    │
└────────────────────────────────────────┘
         │
         │ ROLLBACK applies in reverse order
         ▼
┌────────────────────────────────────────┐
│     BUFFER CACHE (After Rollback)      │
├────────────────────────────────────────┤
│  All changes reversed                  │
│  Data restored to original state       │
│  Changes never visible to others       │
└────────────────────────────────────────┘

Monitor Undo Usage:

-- View active transactions and undo usage
SELECT s.username,
       s.sid,
       t.used_ublk * 8192/1024/1024 undo_mb,
       t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr;

Step 3: Generate Redo for ROLLBACK

Important:** ROLLBACK generates redo to log the undo application!

Why ROLLBACK Generates Redo?

┌────────────────────────────────────────┐
│  Scenario: ROLLBACK in progress        │
│  Database crashes during ROLLBACK      │
│                                        │
│  On restart:                           │
│  • Oracle replays redo                 │
│  • Completes the ROLLBACK operation    │
│  • Ensures data consistency            │
│                                        │
│  Without redo:                         │
│  • Partial rollback = corrupted data   │
│  • Database inconsistent               │
└────────────────────────────────────────┘

View Redo Generation:

-- Check redo size for operations
SELECT name, value
FROM v$sysstat
WHERE name IN ('redo size', 'transaction rollbacks');

Step 4-6: Update Indexes and Release Locks

All indexes are updated and locks released, just like COMMIT.

-- Transaction with locks
UPDATE employees SET salary = 60000 WHERE employee_id = 101;
-- Row locked

-- Session 2 tries to modify same row
UPDATE employees SET department_id = 20 WHERE employee_id = 101;
-- WAITS

-- Session 1 rolls back
ROLLBACK;
-- Locks released immediately

-- Session 2 proceeds immediately
-- No longer waiting

Check Locks:

-- View locked objects
SELECT s.username,
       o.object_name,
       s.sid,
       s.serial#
FROM v$locked_object lo
JOIN dba_objects o ON lo.object_id = o.object_id
JOIN v$session s ON lo.session_id = s.sid;

ROLLBACK Variations

1. Complete ROLLBACK

-- Rollback entire transaction
INSERT INTO employees VALUES (101, 'John', 50000);
UPDATE employees SET salary = 55000 WHERE employee_id = 100;
DELETE FROM employees WHERE employee_id = 99;

ROLLBACK;
-- All three operations reversed

2. ROLLBACK TO SAVEPOINT

-- Partial rollback using savepoints
INSERT INTO employees VALUES (101, 'John', 50000);
SAVEPOINT sp1;

UPDATE employees SET salary = 55000 WHERE employee_id = 100;
SAVEPOINT sp2;

DELETE FROM employees WHERE employee_id = 99;

-- Rollback only the DELETE
ROLLBACK TO SAVEPOINT sp2;
-- DELETE reversed, UPDATE and INSERT remain

-- Rollback UPDATE and DELETE
ROLLBACK TO SAVEPOINT sp1;
-- UPDATE and DELETE reversed, INSERT remains

-- Rollback everything
ROLLBACK;
-- All operations reversed

Savepoint Example:

BEGIN
    INSERT INTO orders VALUES (1001, SYSDATE);
    SAVEPOINT order_created;
    
    INSERT INTO order_items VALUES (1, 1001, 'Product X', 10);
    INSERT INTO order_items VALUES (2, 1001, 'Product Y', 5);
    
    -- Error occurs
    IF some_validation_fails THEN
        ROLLBACK TO SAVEPOINT order_created;
        -- Order items removed, order remains
        -- Can retry or handle error
    ELSE
        COMMIT;
    END IF;
END;
/

3. Implicit ROLLBACK

-- Session disconnect without COMMIT
UPDATE employees SET salary = 60000;
-- Session terminates (network failure, killed, etc.)
-- Oracle automatically issues ROLLBACK

-- Exception in PL/SQL
BEGIN
    UPDATE employees SET salary = 60000;
    -- Error occurs
    RAISE_APPLICATION_ERROR(-20001, 'Error occurred');
    -- No explicit ROLLBACK needed
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Good practice but optional
END;
/

4. Transaction-Level ROLLBACK

-- DDL statements cannot be rolled back
CREATE TABLE test (id NUMBER);
-- Auto-commits, cannot rollback

INSERT INTO test VALUES (1);
ROLLBACK;
-- Table still exists, only INSERT is rolled back

ROLLBACK Performance

Why ROLLBACK Can Be Slow

-- Large transaction
BEGIN
    FOR i IN 1..1000000 LOOP
        INSERT INTO large_table VALUES (i, 'Data'||i);
    END LOOP;
    -- No commit yet
END;
/

-- Rollback must reverse 1 million inserts
ROLLBACK;
-- Can take considerable time
-- Must read and apply 1 million undo records

Performance Factors:

Monitor Long-Running ROLLBACK:

-- View rollback progress
SELECT sid,
       serial#,
       context,
       sofar,
       totalwork,
       ROUND(sofar/totalwork*100, 2) pct_complete
FROM v$session_longops
WHERE opname LIKE '%rollback%'
  AND sofar <> totalwork;

Common ROLLBACK Scenarios

Scenario 1: Error Handling

-- Proper error handling with ROLLBACK
DECLARE
    v_error VARCHAR2(200);
BEGIN
    UPDATE accounts SET balance = balance - 1000 WHERE account_id = 101;
    UPDATE accounts SET balance = balance + 1000 WHERE account_id = 102;
    
    -- Validation
    IF (SELECT balance FROM accounts WHERE account_id = 101) < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    END IF;
    
    COMMIT;
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        v_error := SQLERRM;
        DBMS_OUTPUT.PUT_LINE('Transaction failed: ' || v_error);
        RAISE;
END;
/

Scenario 2: Batch Processing with Validation

-- Rollback on validation failure
DECLARE
    v_count NUMBER;
BEGIN
    -- Process batch
    FOR rec IN (SELECT * FROM staging_table) LOOP
        INSERT INTO production_table VALUES (rec.col1, rec.col2);
    END LOOP;
    
    -- Validate
    SELECT COUNT(*) INTO v_count 
    FROM production_table 
    WHERE created_date = TRUNC(SYSDATE);
    
    IF v_count < 1000 THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20002, 'Insufficient records processed');
    ELSE
        COMMIT;
    END IF;
END;
/

Scenario 3: Multi-Step Operation

-- Complex operation with rollback safety
BEGIN
    -- Step 1: Archive old data
    INSERT INTO employees_archive 
    SELECT * FROM employees WHERE status = 'TERMINATED';
    SAVEPOINT archived;
    
    -- Step 2: Delete from main table
    DELETE FROM employees WHERE status = 'TERMINATED';
    SAVEPOINT deleted;
    
    -- Step 3: Update statistics
    UPDATE department_stats SET employee_count = employee_count - SQL%ROWCOUNT;
    
    -- Validation
    IF SQL%ROWCOUNT = 0 THEN
        ROLLBACK TO SAVEPOINT deleted;
        -- Restore employees, keep archive
    END IF;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;  -- Rollback everything
        RAISE;
END;
/

ROLLBACK Best Practices

1. Always Handle Errors

-- Bad: No error handling
BEGIN
    UPDATE employees SET salary = 60000;
    INSERT INTO audit_log VALUES (USER, SYSDATE);
    -- If INSERT fails, UPDATE remains uncommitted but not rolled back
END;

-- Good: Explicit error handling
BEGIN
    UPDATE employees SET salary = 60000;
    INSERT INTO audit_log VALUES (USER, SYSDATE);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;

2. Use Savepoints for Complex Transactions

-- Good: Savepoints allow partial rollback
BEGIN
    INSERT INTO orders VALUES (1001, SYSDATE);
    SAVEPOINT order_inserted;
    
    BEGIN
        INSERT INTO order_items VALUES (1, 1001, 'Product', 10);
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK TO SAVEPOINT order_inserted;
            -- Order preserved, can retry items
    END;
    
    COMMIT;
END;
/

3. Avoid Large Transactions

-- Bad: Huge transaction
UPDATE large_table SET status = 'PROCESSED';  -- 10 million rows
-- If rollback needed, takes very long

-- Good: Batch processing
LOOP
    UPDATE large_table 
    SET status = 'PROCESSED' 
    WHERE ROWNUM <= 10000;
    
    EXIT WHEN SQL%ROWCOUNT = 0;
    COMMIT;  -- Smaller units, faster rollback if needed
END LOOP;

4. Test Rollback Scenarios

-- Test your rollback logic
BEGIN
    -- Simulate error condition
    UPDATE employees SET salary = 60000;
    
    -- Force error for testing
    RAISE_APPLICATION_ERROR(-20001, 'Test error');
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Rollback successful: ' || SQLERRM);
END;
/

-- Verify data unchanged
SELECT salary FROM employees WHERE employee_id = 101;

Monitoring ROLLBACK Operations

-- View rollback statistics
SELECT name, value
FROM v$sysstat
WHERE name LIKE '%rollback%'
ORDER BY name;

-- Active rollback operations
SELECT s.username,
       s.sid,
       s.status,
       t.used_ublk,
       t.start_time
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
WHERE s.username IS NOT NULL;

-- Wait events related to rollback
SELECT event,
       total_waits,
       time_waited/100 time_waited_sec
FROM v$system_event
WHERE event LIKE '%undo%'
   OR event LIKE '%rollback%'
ORDER BY time_waited DESC;

Quick Reference

ROLLBACK Syntax

-- Complete rollback
ROLLBACK;

-- Rollback to savepoint
ROLLBACK TO SAVEPOINT savepoint_name;

-- Rollback distributed transaction (requires privileges)
ROLLBACK FORCE 'transaction_id';

Key Queries

-- Check for uncommitted transactions
SELECT COUNT(*) FROM v$transaction;

-- View undo usage
SELECT tablespace_name, status, COUNT(*) segments
FROM dba_undo_extents
GROUP BY tablespace_name, status;

-- Check undo retention
SHOW PARAMETER undo_retention;

Conclusion

ROLLBACK is essential for maintaining data integrity by discarding unwanted changes. Understanding its execution helps in proper error handling and transaction management.

Key Takeaways:

1. ROLLBACK applies undo data – Restores original values
2. Generates redo – Ensures rollback itself is recoverable
3. Can be slow – Proportional to transaction size
4. Releases locks – Like COMMIT, frees resources
5. Use savepoints – For partial rollback capability
6. Always handle errors – Explicit ROLLBACK in exceptions
7. Avoid large transactions – Makes rollback faster if needed
8. Undo freed immediately – No retention after rollback

Remember: ROLLBACK is your safety net – use it wisely to handle errors and maintain data consistency!