COMMIT FAQS

1) What does COMMIT do in Oracle?

COMMIT permanently saves all changes made in the current transaction (DML like INSERT, UPDATE, DELETE, MERGE) to the database and makes them visible to other sessions.

2) What is a transaction in Oracle?

A transaction is a sequence of SQL statements treated as a single logical unit of work.
It begins with the first DML statement and ends with:

• COMMIT
• ROLLBACK
• or an implicit commit

3) Does COMMIT release locks?

Yes. After COMMIT, Oracle releases all row-level locks held by that transaction.

4) Is COMMIT automatic in Oracle?

Not usually. Oracle does not auto-commit by default.
Implicit commits occur:

• Before and after DDL statements (CREATE, ALTER, DROP, TRUNCATE)
• When using some client tools with auto-commit enabled

Example:

CREATE TABLE test (id NUMBER);

5) Can I undo after COMMIT?

No. After COMMIT, changes are permanent and cannot be rolled back (except using Flashback features if configured).

6) What is the syntax of COMMIT?

Basic:

COMMIT;

With options:

COMMIT WRITE IMMEDIATE WAIT;

Common options:

• WAIT (default) – Waits for redo to be written to disk
• NOWAIT – Does not wait for redo write
• IMMEDIATE – Writes redo immediately
• BATCH – Allows batching redo writes

7) What is the difference between COMMIT and ROLLBACK?

COMMIT	ROLLBACK
Saves changes permanently	Undoes changes
Releases locks	Releases locks
Ends transaction	Ends transaction

8) What happens if the session crashes before COMMIT?

All uncommitted changes are automatically rolled back by Oracle during recovery.

9) Does SELECT require COMMIT?

No. SELECT does not modify data and does not require commit.

10) Can I partially commit?

No. COMMIT saves the entire transaction.
For partial rollback, use savepoints:

SAVEPOINT sp1;

UPDATE employees SET salary = salary + 1000;

ROLLBACK TO sp1;

11) Does COMMIT improve performance?

Frequent commits can:

• Reduce undo usage
• Reduce locking

But too frequent commits can:

• Increase redo overhead
• Hurt performance in batch operations

Best practice: Commit logically (e.g., every 5,000–10,000 rows in batch jobs).

12) Does COMMIT affect other sessions?

Yes. After commit:

• Other users can see the changes
• Locks are released

Before commit:

• Other users cannot see uncommitted changes (read consistency)

13) Can COMMIT be used inside PL/SQL?

Yes, but:

• Avoid committing inside reusable procedures unless required
• Let the calling program control the transaction when possible

14) What is Two-Phase Commit?

Used in distributed transactions (e.g., across multiple databases).
Oracle ensures all systems either commit or rollback together.

15) Does TRUNCATE require COMMIT?

No. TRUNCATE is DDL and performs an implicit commit.