Oracle Savepoint With Examples

1. What is SAVEPOINT?

SAVEPOINT is a TCL (Transaction Control Language) command used to create a named point inside a transaction. It allows you to roll back part of a transaction instead of rolling back the entire transaction.

Syntax:

SAVEPOINT savepoint_name;

Example:

SAVEPOINT sp1;

2. Why do we use SAVEPOINT?

A SAVEPOINT is useful when a transaction contains multiple operations and you want the ability to undo only some of them.

Example:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

The UPDATE is undone, but the INSERT remains.

3. What is the basic syntax?

Syntax:

SAVEPOINT savepoint_name;

Example:

SAVEPOINT salary_update;

The savepoint name identifies the point to which you can later roll back.

4. What is ROLLBACK TO SAVEPOINT?

It is used to undo changes made after a particular savepoint.

Syntax:

ROLLBACK TO savepoint_name;

Example:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

The UPDATE is undone.

5. Does ROLLBACK TO SAVEPOINT end the transaction?

No. This is one of the most important points.

Example:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

COMMIT;

The transaction continues after ROLLBACK TO sp1. It ends only when you execute COMMIT or ROLLBACK.

6. SAVEPOINT with INSERT

INSERT INTO students
VALUES (101, 'Ravi');

SAVEPOINT s1;

INSERT INTO students
VALUES (102, 'Kiran');

ROLLBACK TO s1;

Result:

  • Student 101 → remains
  • Student 102 → undone

7. SAVEPOINT with UPDATE

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

SAVEPOINT s1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO s1;

The second update is undone. The first update remains uncommitted.

8. SAVEPOINT with DELETE

DELETE FROM employees
WHERE employee_id = 101;

SAVEPOINT s1;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK TO s1;

Result:

  • Employee 101 → deletion remains
  • Employee 102 → deletion is undone

9. Can we create multiple SAVEPOINTs?

Yes.

Example:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT s1;

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

SAVEPOINT s2;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

SAVEPOINT s3;

Now there are multiple points in the transaction.

10. Example with Multiple SAVEPOINTs

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT s1;

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

SAVEPOINT s2;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO s2;

The last UPDATE is undone. The salary returns to the state established at s2.

11. What happens to SAVEPOINTs after ROLLBACK TO?

Suppose:

SAVEPOINT s1;
SAVEPOINT s2;
SAVEPOINT s3;

ROLLBACK TO s2;

The changes after s2 are rolled back. Savepoints created after s2, such as s3, are no longer available. The s2 savepoint remains available.

12. Can we reuse a SAVEPOINT name?

Yes. If you create a savepoint with the same name again, the new savepoint replaces the previous one with that name.

Example:

SAVEPOINT s1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

SAVEPOINT s1;

The second s1 becomes the current savepoint named s1. Reusing a savepoint name moves that savepoint to the new position.

13. Does COMMIT remove SAVEPOINTs?

Yes.

SAVEPOINT s1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

COMMIT;

After COMMIT, the transaction ends and the savepoint s1 is no longer available.

14. Does complete ROLLBACK remove SAVEPOINTs?

Yes.

SAVEPOINT s1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

ROLLBACK;

The transaction ends and the savepoint is removed.

15. SAVEPOINT vs ROLLBACK

SAVEPOINT ROLLBACK
Creates a point in a transaction Undoes changes
Does not undo anything by itself Undoes transaction changes
Transaction continues Complete rollback ends transaction
Used for partial rollback Used for complete rollback

16. SAVEPOINT vs ROLLBACK TO

SAVEPOINT ROLLBACK TO
Creates a recovery point Returns to a recovery point
Example: SAVEPOINT s1; Example: ROLLBACK TO s1;
Does not undo changes Undoes changes after the savepoint

17. SAVEPOINT vs COMMIT

SAVEPOINT COMMIT
Creates a point within transaction Ends the transaction
Does not permanently save changes Permanently saves changes
Allows partial rollback Cannot normally be undone by ROLLBACK
Transaction continues Transaction ends

18. Can SAVEPOINT be used after COMMIT?

A savepoint belongs to a transaction.

SAVEPOINT s1;

COMMIT;

After COMMIT, the old savepoint no longer exists. You can create a new savepoint in the new transaction:

SAVEPOINT s2;

19. Does SAVEPOINT lock data?

SAVEPOINT itself does not perform a DML change and does not itself acquire a new data lock. Locks are generally acquired by the DML statements that occur in the transaction.

Example:

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

SAVEPOINT s1;

The UPDATE is what causes the relevant locking behavior, not SAVEPOINT s1.

20. SAVEPOINT with a Complete Example

Consider this transaction:

INSERT INTO employees
VALUES (101, 'Ravi', 50000);

SAVEPOINT s1;

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

SAVEPOINT s2;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO s2;

COMMIT;

Step-by-step:

  1. Employee is inserted.
  2. A savepoint is created.
  3. Salary becomes 55000.
  4. Another savepoint is created.
  5. Salary becomes 60000.
  6. The last update is undone.
  7. The remaining transaction is permanently saved.

Final result: Employee 101 salary = 55000

21. What happens if SAVEPOINT doesn't exist?

Suppose:

SAVEPOINT s1;

ROLLBACK TO s2;

If s2 is not a valid savepoint in the current transaction, Oracle raises an error rather than performing the rollback you requested.

22. Can SAVEPOINT be used in PL/SQL?

Yes.

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;

    SAVEPOINT s1;

    UPDATE employees
    SET salary = salary + 2000
    WHERE employee_id = 102;

    ROLLBACK TO s1;

    COMMIT;
END;
/

The update for employee 102 is rolled back, while the earlier update remains and is committed.

23. Is SAVEPOINT a TCL command?

Yes. Transaction Control Language includes:

  • COMMIT
  • ROLLBACK
  • SAVEPOINT

24. Does SAVEPOINT permanently save data?

No. SAVEPOINT s1 does not permanently save your changes. Only COMMIT permanently commits the transaction.

Think:

  • SAVEPOINT → Mark a point
  • COMMIT → Permanently save
  • ROLLBACK → Undo

25. Can SAVEPOINT undo a COMMIT?

No. Once COMMIT has occurred, the previous transaction and its savepoints are finished. A later ROLLBACK TO s1 cannot go back to a savepoint from the previous committed transaction.

26. Does DDL affect SAVEPOINTs?

Yes. Oracle's DDL statements cause implicit commits.

Example:

SAVEPOINT s1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

CREATE TABLE test (
id NUMBER
);

The DDL causes transaction boundaries, so you cannot expect s1 to remain available for ordinary rollback after the DDL.

Exam point ⭐ Do not use SAVEPOINTs across DDL when you expect normal transactional rollback behavior.

27. Frequently Asked Interview Questions

Question Answer
What is SAVEPOINT? A named point within a transaction that allows partial rollback.
Syntax? SAVEPOINT s1;
How do you rollback to a savepoint? ROLLBACK TO s1;
Does ROLLBACK TO end the transaction? No.
Does COMMIT remove savepoints? Yes.
Does complete ROLLBACK remove savepoints? Yes.
Can we create multiple savepoints? Yes.
Can we reuse a savepoint name? Yes. The newer savepoint with that name replaces the previous one.
Does SAVEPOINT permanently save data? No.
Which command permanently saves data? COMMIT;
Which command performs partial rollback? ROLLBACK TO savepoint_name;
Which language does SAVEPOINT belong to? TCL — Transaction Control Language.

Important Exam Points

  1. SAVEPOINT is a TCL command.
  2. It creates a named point within a transaction.
  3. It is used for partial rollback.
  4. Syntax: SAVEPOINT name;
  5. Partial rollback: ROLLBACK TO name;
  6. ROLLBACK TO does not end the transaction.
  7. COMMIT removes the transaction's savepoints.
  8. Complete ROLLBACK removes the transaction's savepoints.
  9. Multiple savepoints can be created.
  10. SAVEPOINT does not permanently save data.

Easy Memory Trick

SAVEPOINT
↓
Create a checkpoint
↓
ROLLBACK TO
↓
Undo changes after checkpoint
↓
COMMIT
↓
Save remaining changes permanently

SAVEPOINT = Checkpoint
ROLLBACK TO = Go back to checkpoint
COMMIT = Permanently save
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)