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