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

Oracle Rollback Faqs With Examples

1. What is ROLLBACK?

ROLLBACK is a TCL (Transaction Control Language) command used to undo changes made in the current transaction that have not been committed.

Syntax:

ROLLBACK;

Example:

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK;

The salary change is undone.

2. Why do we use ROLLBACK?

ROLLBACK is used when you want to cancel changes made during the current transaction.

Example:

DELETE FROM employees
WHERE employee_id = 101;

ROLLBACK;

The deleted row is restored because the deletion had not been committed.

3. Is ROLLBACK DDL, DML, or TCL?

ROLLBACK is a TCL command. Common TCL commands are:

• COMMIT
• ROLLBACK
• SAVEPOINT

4. What happens when ROLLBACK is executed?

When you execute ROLLBACK, Oracle:

1. Undoes the uncommitted changes in the current transaction.
2. Ends the current transaction.
3. Releases transaction-related locks.
4. Removes the transaction's savepoints.

5. ROLLBACK with INSERT

Suppose:

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

Before COMMIT, execute:

ROLLBACK;

The inserted row is undone.

Flow:

INSERT
↓
Uncommitted change
↓
ROLLBACK
↓
INSERT is undone

6. ROLLBACK with UPDATE

UPDATE employees
SET salary = 80000
WHERE employee_id = 101;

ROLLBACK;

The employee's salary returns to its previous value.

7. ROLLBACK with DELETE

DELETE FROM employees
WHERE employee_id = 101;

ROLLBACK;

The deleted row is restored.

8. COMMIT vs ROLLBACK

COMMIT	ROLLBACK
Saves changes	Undoes uncommitted changes
Makes changes permanent	Cancels changes
Ends the transaction	Ends the transaction
Releases transaction-related locks	Releases transaction-related locks

Easy memory trick:

• COMMIT = Save
• ROLLBACK = Undo

9. Can ROLLBACK undo a COMMIT?

No.

Example:

UPDATE employees
SET salary = 90000
WHERE employee_id = 101;

COMMIT;

ROLLBACK;

The ROLLBACK cannot undo the already committed update. ROLLBACK can undo uncommitted changes, not already committed changes.

10. What happens if we execute ROLLBACK without making changes?

ROLLBACK;

If there are no uncommitted changes, there is simply nothing to undo.

11. Can ROLLBACK undo multiple statements?

Yes.

Example:

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

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK;

The uncommitted changes from all three statements are undone.

12. ROLLBACK with SAVEPOINT

A SAVEPOINT allows you to undo only part of a transaction.

Example:

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

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

What happens?

INSERT
↓
SAVEPOINT sp1
↓
UPDATE
↓
ROLLBACK TO sp1
↓
UPDATE is undone
↓
INSERT remains

So ROLLBACK TO sp1 does not roll back the entire transaction.

13. What is ROLLBACK TO SAVEPOINT?

Syntax:

ROLLBACK TO savepoint_name;

Example:

SAVEPOINT before_update;

UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 101;

ROLLBACK TO before_update;

Only the changes made after before_update are undone.

14. Does ROLLBACK TO SAVEPOINT end the transaction?

No. This is an important exam point.

Example:

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

SAVEPOINT sp1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO sp1;

The transaction is still active after ROLLBACK TO sp1. You can still perform more operations and eventually COMMIT or ROLLBACK.

15. ROLLBACK vs ROLLBACK TO

ROLLBACK	ROLLBACK TO SAVEPOINT
Undoes the entire current transaction	Undoes changes after a savepoint
Ends the transaction	Does not end the transaction
Removes transaction savepoints	The specified savepoint remains usable

Example:

ROLLBACK;

versus

ROLLBACK TO sp1;

16. Example with Multiple SAVEPOINTs

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

SAVEPOINT sp1;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

SAVEPOINT sp2;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK TO sp2;

The DELETE is undone, while the earlier changes remain.

17. What happens to SAVEPOINTs after ROLLBACK?

With a complete ROLLBACK, the current transaction ends and its savepoints are removed. With ROLLBACK TO sp1, the specified savepoint remains available, while savepoints created after it are removed.

18. Does ROLLBACK release locks?

Yes. A complete ROLLBACK ends the transaction and releases transaction-related locks.

Example:

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

ROLLBACK;

The transaction is ended and the locks associated with that transaction are released.

19. Does ROLLBACK make changes visible to other users?

ROLLBACK does not make your changes visible as committed changes. Instead, it removes the uncommitted changes from the transaction. Other sessions will continue to see data according to Oracle's transaction and read-consistency rules.

20. Does ROLLBACK work with DML?

Yes. DML includes:

• INSERT
• UPDATE
• DELETE
• MERGE

Example:

UPDATE employees
SET salary = 75000
WHERE employee_id = 101;

ROLLBACK;

The update is undone.

21. Can ROLLBACK undo DDL?

This is a very important Oracle question. Generally, no. Oracle performs implicit commits around DDL statements.

Example:

CREATE TABLE test (
    id NUMBER
);

You cannot use a later ROLLBACK to treat that DDL as an ordinary uncommitted DML change and undo it.

22. What happens when a DDL statement is executed?

Oracle performs an implicit commit before the DDL and, if the DDL succeeds, another implicit commit after it.

Example:

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

CREATE TABLE test (
id NUMBER
);

The CREATE TABLE causes implicit transaction boundaries, so you should not expect a later ROLLBACK to undo the earlier INSERT.

Exam point ⭐ DDL causes implicit commits in Oracle.

23. Does ROLLBACK undo a TRUNCATE?

No. TRUNCATE is DDL in Oracle and causes an implicit commit.

Example:

TRUNCATE TABLE employees;

ROLLBACK;

The ROLLBACK does not restore the rows removed by TRUNCATE.

Important comparison:

• DELETE + ROLLBACK → DELETE can be undone before COMMIT
• TRUNCATE + ROLLBACK → TRUNCATE cannot be undone by ROLLBACK

24. ROLLBACK after COMMIT

Consider:

DELETE FROM employees
WHERE employee_id = 101;

COMMIT;

ROLLBACK;

The ROLLBACK cannot restore employee 101 because the deletion was already committed.

25. What happens if a statement fails?

Suppose:

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

INSERT INTO employees
VALUES (101, 'Kiran', 60000);

If the second statement fails because of a constraint violation, the first successful statement is not automatically rolled back merely because the second statement failed. You can decide what to do: COMMIT or ROLLBACK depending on the desired transaction outcome.

26. ROLLBACK in PL/SQL

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

```
ROLLBACK;
```

END;
/

The update is undone.

27. ROLLBACK Example — Complete Transaction

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

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

DELETE FROM employees
WHERE employee_id = 102;

ROLLBACK;

All three uncommitted DML changes are rolled back.

28. ROLLBACK Example — Partial Undo

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

SAVEPOINT s1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO s1;

COMMIT;

Result:

• The INSERT remains.
• The UPDATE is undone.
• COMMIT saves the remaining transaction.

29. Frequently Asked Interview Questions

Question	Answer
What is ROLLBACK?	A TCL command that undoes uncommitted changes in the current transaction.
What is the syntax?	ROLLBACK;
Can ROLLBACK undo INSERT?	Yes, if the insert has not been committed.
Can ROLLBACK undo UPDATE?	Yes, if the update has not been committed.
Can ROLLBACK undo DELETE?	Yes, if the delete has not been committed.
Can ROLLBACK undo COMMIT?	No.
Does ROLLBACK end the transaction?	Yes, a complete ROLLBACK ends the current transaction.
Does ROLLBACK TO SAVEPOINT end the transaction?	No.
Does ROLLBACK release locks?	Yes, a complete rollback releases transaction-related locks.
Can ROLLBACK undo TRUNCATE?	No.
Can ROLLBACK undo CREATE TABLE?	No, not as an ordinary uncommitted DML operation, because DDL causes implicit commits.
What is the difference between ROLLBACK and ROLLBACK TO?	ROLLBACK undoes the entire current transaction, while ROLLBACK TO savepoint undoes only changes after the savepoint.

30. Important Exam Points

1. ROLLBACK is a TCL command.
2. It undoes uncommitted changes.
3. ROLLBACK ends the current transaction.
4. It releases transaction-related locks.
5. ROLLBACK can undo INSERT, UPDATE, DELETE, and MERGE changes before commit.
6. ROLLBACK cannot undo an already executed COMMIT.
7. ROLLBACK TO SAVEPOINT performs a partial rollback.
8. ROLLBACK TO SAVEPOINT does not end the transaction.
9. DDL causes implicit commits in Oracle.
10. TRUNCATE cannot be undone using normal ROLLBACK.

31. One-line Memory Trick

ROLLBACK = Undo uncommitted changes
ROLLBACK TO = Undo changes after a savepoint
COMMIT = Save changes permanently

Oracle Commit Faqs With Examples

1. What is COMMIT?

Answer: COMMIT permanently saves all changes made by DML statements such as:

• INSERT
• UPDATE
• DELETE
• MERGE

Syntax:

COMMIT;

Example:

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

COMMIT;

After COMMIT, the inserted row becomes permanent.

2. What is a Transaction?

A transaction is a logical unit of work consisting of one or more SQL statements.

Example:

UPDATE employees
SET salary = salary + 500
WHERE employee_id = 101;

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

COMMIT;

The two UPDATE statements are part of the same transaction until the transaction is committed or rolled back.

3. Why do we use COMMIT?

We use COMMIT to:

1. Permanently save changes.
2. End the current transaction.
3. Make the changes visible to other database sessions.
4. Release transaction-related locks.

Example:

DELETE FROM employees
WHERE employee_id = 101;

COMMIT;

The deletion is now permanently saved.

4. Does COMMIT save INSERT?

Yes.

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

COMMIT;

The inserted row is permanently saved.

5. Does COMMIT save UPDATE?

Yes.

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

COMMIT;

The salary change is permanently saved.

6. Does COMMIT save DELETE?

Yes.

DELETE FROM employees
WHERE employee_id = 101;

COMMIT;

The deletion is permanently saved.

7. What happens before COMMIT?

Before COMMIT, the changes made by your transaction are not yet permanent.

UPDATE employees
SET salary = 70000
WHERE employee_id = 101;

At this point, you can still undo the change using:

ROLLBACK;

Memory flow:

UPDATE
  ↓
Changes made
  ↓
COMMIT → Save permanently
ROLLBACK → Undo changes

8. What happens after COMMIT?

Once you execute COMMIT, the transaction is completed.

DELETE FROM employees
WHERE employee_id = 101;

COMMIT;

After the COMMIT, you cannot use a normal ROLLBACK to undo that deletion.

9. COMMIT vs ROLLBACK

COMMIT	ROLLBACK
Saves changes	Undoes uncommitted changes
Ends the transaction	Ends the transaction
Makes changes permanent	Restores previous state
Cannot normally be undone with ROLLBACK	Can undo DML changes before COMMIT

Example:

UPDATE employees
SET salary = 90000
WHERE employee_id = 101;

ROLLBACK;

The salary change is undone.

But:

UPDATE employees
SET salary = 90000
WHERE employee_id = 101;

COMMIT;

The change is saved.

10. Can COMMIT undo changes?

No. COMMIT saves changes.

UPDATE employees
SET salary = 100000
WHERE employee_id = 101;

COMMIT;

You cannot then do:

ROLLBACK;

and expect the previous salary to be restored by that rollback.

11. What happens if we close the SQL session without COMMIT?

This depends on how the client/session is closed. In general, you should not rely on closing a client application to manage transactions. If the transaction is not committed, uncommitted changes may be rolled back when the session terminates. Therefore, explicitly use COMMIT when you want to save your transaction.

12. Does COMMIT affect SELECT?

COMMIT is primarily used to complete transactions containing DML changes. A SELECT statement by itself does not modify data.

SELECT *
FROM employees;

There is normally nothing to commit from that SELECT.

13. What happens to locks after COMMIT?

When a transaction is committed, Oracle releases the transaction's locks associated with the completed transaction.

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

COMMIT;

The transaction is completed and its relevant locks are released.

14. Does COMMIT make changes visible to other users?

Yes. A transaction's changes become visible to other database sessions after the transaction is committed, subject to Oracle's transaction and isolation behavior.

Example:

Session 1:
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

Before COMMIT, another session does not normally see Session 1's uncommitted change.

COMMIT;

The committed change can now be seen by other sessions according to Oracle's read-consistency rules.

15. Can we COMMIT multiple statements?

Yes.

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

UPDATE employees
SET salary = 55000
WHERE employee_id = 101;

DELETE FROM employees
WHERE employee_id = 102;

COMMIT;

All three DML operations are committed together.

16. What if one statement fails before COMMIT?

Suppose:

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

INSERT INTO employees
VALUES (101, 'Kiran', 60000);

If the second statement fails because 101 violates a primary-key constraint, Oracle does not automatically roll back the first successful statement just because the second statement failed. The transaction can still contain the successful first change. You can then decide whether to COMMIT or ROLLBACK depending on what you want.

17. What is COMMIT WORK?

You may also see:

COMMIT WORK;

WORK is optional. These are equivalent forms:

COMMIT;
COMMIT WORK;

18. What is COMMIT AND CHAIN?

Oracle supports transaction control options such as:

COMMIT WORK AND CHAIN;

This commits the current transaction and starts a new transaction with certain transaction characteristics carried forward. This is an advanced feature and is less commonly used in basic SQL programming.

19. What is COMMIT AND RELEASE?

You may encounter:

COMMIT WORK RELEASE;

It commits the current transaction and releases the current session from the database.

20. Does DDL automatically COMMIT?

Yes, Oracle issues an implicit commit around DDL statements.

Examples of DDL include:

• CREATE
• ALTER
• DROP
• TRUNCATE

Example:

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

CREATE TABLE test (
id NUMBER
);

The DDL statement causes an implicit commit according to Oracle's transaction rules.

21. Does DML automatically COMMIT?

Normally, no. DML includes INSERT, UPDATE, DELETE, and MERGE.

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

You generally need COMMIT to explicitly save the transaction.

22. COMMIT and SAVEPOINT

A SAVEPOINT allows you to mark a point within a transaction.

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

SAVEPOINT point1;

UPDATE employees
SET salary = 60000
WHERE employee_id = 101;

ROLLBACK TO point1;

COMMIT;

What happens?

1. Employee is inserted.
2. point1 is created.
3. Salary is updated.
4. ROLLBACK TO point1 undoes the update.
5. The insert remains.
6. COMMIT saves the remaining transaction.

23. Does COMMIT remove SAVEPOINTs?

Yes. When you issue COMMIT, the current transaction ends and its savepoints are released.

SAVEPOINT A;

UPDATE employees
SET salary = 50000
WHERE employee_id = 101;

COMMIT;

After the commit, A is no longer available as a savepoint for that completed transaction.

24. COMMIT inside PL/SQL

You can use COMMIT in a PL/SQL block.

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

```
COMMIT;
```

END;
/

The update is committed.

25. COMMIT inside a Procedure

A stored procedure can contain COMMIT.

CREATE OR REPLACE PROCEDURE update_salary
IS
BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;

```
COMMIT;
```

END;
/

Calling the procedure:

EXEC update_salary;

It commits the update because the procedure explicitly contains COMMIT.

26. What is an Implicit Commit?

An implicit commit is a commit performed automatically by Oracle rather than by explicitly writing COMMIT.

For example, Oracle performs implicit commits around DDL statements.

27. Explicit vs Implicit COMMIT

Explicit Commit	Implicit Commit
Programmer executes COMMIT	Oracle performs the commit automatically
Example: COMMIT;	Example: DDL transaction behavior
Common with DML	Common around DDL

28. Simple Real-Time Example

Suppose a bank transfer involves two updates:

UPDATE accounts
SET balance = balance - 1000
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000
WHERE account_id = 2;

These operations should logically succeed together. Then:

COMMIT;

If something goes wrong before the commit, you can use ROLLBACK. This illustrates why transactions are important.

29. Frequently Asked Interview Questions

Question	Answer
What is COMMIT?	A TCL command that permanently saves the current transaction's changes.
Is COMMIT DDL, DML, or TCL?	TCL — Transaction Control Language.
Which commands are commonly associated with COMMIT?	COMMIT, ROLLBACK, SAVEPOINT.
Can we rollback after COMMIT?	No, not using a normal ROLLBACK of that completed transaction.
Does COMMIT release transaction locks?	Yes, the locks associated with the completed transaction are released.
Does COMMIT make changes visible to other sessions?	Yes, committed changes become available to other sessions according to Oracle's transaction visibility rules.
Does INSERT require COMMIT?	If you want to explicitly end and save the transaction, use COMMIT.
Does DELETE require COMMIT?	Same principle: DELETE ...; COMMIT;
Does UPDATE require COMMIT?	Yes, if you want to explicitly commit the transaction.
Does DDL cause an implicit commit?	Yes. This is a key Oracle exam question.

30. COMMIT — Important Points for Exams

1. COMMIT is a TCL command.
2. It permanently saves the current transaction.
3. It ends the current transaction.
4. It makes committed changes visible to other sessions.
5. It releases transaction locks.
6. ROLLBACK cannot normally undo changes after they have been committed.
7. INSERT, UPDATE, DELETE, and MERGE are DML operations whose changes can be committed.
8. Oracle performs implicit commits around DDL statements.
9. COMMIT also releases transaction savepoints.
10. COMMIT WORK is equivalent to COMMIT.
11. COMMIT can be used in PL/SQL.
12. Use transactions carefully when multiple operations must succeed or fail as a unit.

31. Easy Diagram to Remember

START TRANSACTION
       |
       v
INSERT / UPDATE / DELETE / MERGE
       |
   +---+---+
   |       |
   v       v
COMMIT   ROLLBACK
   |       |
   v       v
Save      Undo changes
permanently

One-line memory trick:

• COMMIT = Save permanently
• ROLLBACK = Undo uncommitted changes
• SAVEPOINT = Mark a point inside a transaction