SAVEPOINT FAQS

1. What is SAVEPOINT in Oracle?

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

• Creates a marker within a transaction
• Allows partial rollback
• Does NOT end the transaction
• Does NOT release locks

It helps control complex transactions.

2. Why do we use SAVEPOINT?

SAVEPOINT is useful when:

• A transaction has multiple logical steps
• You want to undo only part of the work
• Error occurs in the middle of processing
• Complex business logic requires partial rollback

3. Basic Syntax

SAVEPOINT savepoint_name;

To rollback to it:

ROLLBACK TO savepoint_name;

4. Example of SAVEPOINT usage

BEGIN

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

 

   SAVEPOINT before_bonus;

 

   UPDATE employees SET salary = salary + 500 WHERE department_id = 20;

 

   ROLLBACK TO before_bonus;

 

   COMMIT;

END;

Explanation:

• First update remains
• Second update is undone
• Transaction continues after rollback
• Final COMMIT saves remaining changes

5. What happens internally when SAVEPOINT is created?

When you create a SAVEPOINT:

• Oracle records the current transaction state
• No data is written to disk
• No locks are released
• It simply marks a position in undo records

It is lightweight and does not generate significant overhead.

6. What happens during ROLLBACK TO SAVEPOINT?

When executing:

ROLLBACK TO savepoint_name;

Oracle:

• Uses undo data to reverse changes made after the savepoint
• Keeps earlier changes intact
• Keeps the transaction active
• Does NOT release locks acquired before the savepoint

7. Does SAVEPOINT end the transaction?

No.

• Transaction continues after SAVEPOINT
• Only COMMIT or full ROLLBACK ends a transaction

8. Can you have multiple SAVEPOINTs?

Yes.

Example:

SAVEPOINT sp1;

SAVEPOINT sp2;

SAVEPOINT sp3;

You can rollback to:

• sp3 (undo after sp3)
• sp2 (undo after sp2)
• sp1 (undo after sp1)

If you rollback to sp2, savepoints created after it (like sp3) are erased.

9. What happens to SAVEPOINT after COMMIT?

After COMMIT:

• All savepoints are erased
• Transaction ends
• You cannot rollback to any previous savepoint

10. What happens after full ROLLBACK?

After full ROLLBACK:

• Entire transaction is undone
• All savepoints are removed
• Locks are released
• Transaction ends

11. Can SAVEPOINT be used in PL/SQL blocks?

Yes.

Example with exception handling:

BEGIN

   SAVEPOINT before_update;

 

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

 

EXCEPTION

   WHEN OTHERS THEN

      ROLLBACK TO before_update;

END;

Useful for controlled error handling.

12. Does SAVEPOINT affect performance?

SAVEPOINT itself:

• Is lightweight
• Does not generate redo
• Has minimal overhead

However:

• Large rollback operations may consume undo resources
• Excessive savepoints in tight loops can affect performance

13. Can SAVEPOINT be used inside triggers?

No.

Triggers cannot contain:

• COMMIT
• ROLLBACK
• SAVEPOINT

Except when using autonomous transactions.

14. What are common mistakes with SAVEPOINT?

• Assuming it releases locks (it does not)
• Forgetting that savepoints are cleared after COMMIT
• Creating too many savepoints unnecessarily
• Not handling exceptions properly

15. Real-world scenario example

Scenario:

A banking transaction performs:

1.    Deduct from Account A

2.    Add to Account B

3.    Insert audit record

If audit insertion fails:

• Rollback to savepoint after step 2
• Retry audit
• Avoid reversing account updates

This ensures business continuity without losing valid updates.

16. SAVEPOINT vs COMMIT vs ROLLBACK

Feature	SAVEPOINT	ROLLBACK	COMMIT
Ends transaction	No	Yes	Yes
Partial undo	Yes	No (full undo)	No
Releases locks	No	Yes	Yes
Removes savepoints	No	Yes	Yes

17. Best Practices for SAVEPOINT

• Use in long or complex transactions
• Use for logical checkpoints
• Avoid excessive savepoints in loops
• Combine with proper exception handling
• Keep transactions reasonably small