SavePoint Best Explanation |

SavePoint best Explanation

A SAVEPOINT in PL/SQL is neither a partial commit nor a partial rollback by itself. Instead, it provides a checkpoint in a transaction to allow for a partial rollback (but not a commit).

To clarify:

• SAVEPOINT: Marks a point within a transaction, enabling you to rollback only to that point, undoing any changes made after it.
• COMMIT: Finalizes the transaction, making all changes permanent. Once committed, the savepoint becomes invalid.
• ROLLBACK TO SAVEPOINT: Reverts the transaction to the state it was in at the specified savepoint, effectively undoing changes made after the savepoint. This is the partial rollback part.

Key Concepts:

1. Savepoint is not a Partial Commit:

A savepoint does not commit any part of the transaction. It only marks a point in time, where you can decide whether to roll back or continue making changes.

• The COMMIT statement is what actually makes the transaction permanent (commits all changes made before it).

2. Savepoint enables Partial Rollback:

If you want to undo only part of your transaction (changes made after a specific point), you can use a SAVEPOINT to rollback selectively to that point.

• A ROLLBACK TO SAVEPOINT allows you to undo changes made after the savepoint, while keeping all changes made before it intact.

Example to demonstrate Partial Rollback:

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    -- Update salary for an employee
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;

    -- Set a savepoint after the update
    SAVEPOINT after_salary_update;

    -- Check the new salary
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;

    -- If salary is too high, roll back to the savepoint
    IF v_salary > 5000 THEN
        ROLLBACK TO SAVEPOINT after_salary_update;
        DBMS_OUTPUT.PUT_LINE('Salary too high, rolled back to savepoint.');
    ELSE
        -- If salary is fine, apply a bonus
        UPDATE employees
        SET salary = salary + 500
        WHERE employee_id = 101;

        -- Commit the changes if everything is fine
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Bonus applied and transaction committed.');
    END IF;
END;
/

Explanation:

• The SAVEPOINT marks the point where the salary update was made.
• If the salary after the update exceeds a threshold (e.g., 5000), the changes made after the savepoint are rolled back using ROLLBACK TO SAVEPOINT.
• If the salary is within an acceptable range, a bonus is added and the transaction is COMMITted.

Conclusion:

SAVEPOINT allows partial rollback, not partial commit. You can revert a transaction to a specific point, undoing all changes made after that point, but it does not commit anything on its own. COMMIT is used to finalize the transaction and make all changes permanent.