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).

1. 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.