Commit FAQS

1. What is COMMIT in Oracle?

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

• Permanently saves all changes made during the current transaction
• Makes changes visible to other users
• Ends the current transaction
• Releases row-level locks

Once committed, changes cannot be rolled back.

2. What is a transaction in Oracle?

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

A transaction:

• Begins with the first DML statement (INSERT, UPDATE, DELETE, MERGE)
• Ends with:
• COMMIT
• ROLLBACK
• DDL statement (auto-commit before and after)
• Session termination (implicit commit in most tools)

Example:

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

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

COMMIT;

Both updates are saved together as one transaction.

3. What happens internally when COMMIT is executed?

When you issue COMMIT, Oracle:

1.    Assigns a System Change Number (SCN) to the transaction

2.    Writes redo entries to the redo log buffer

3.    LGWR (Log Writer) flushes redo to online redo log files

4.    Marks transaction as committed in undo segments

5.    Releases row locks

6.    Makes changes visible to other sessions

This ensures durability (D in ACID properties).

4. What is SCN in COMMIT?

SCN (System Change Number) is a logical timestamp Oracle uses to maintain read consistency.

• Each COMMIT generates a new SCN
• Helps Oracle determine which data version to show to other sessions

5. Does COMMIT free undo space?

No, not immediately.

• Undo data remains available for:
• Read consistency
• Flashback queries
• Undo is reused only after it becomes inactive and needed.

6. What is auto-commit?

Some tools (like SQL Developer or JDBC connections with auto-commit enabled):

• Automatically issue COMMIT after each DML statement
• This means each statement becomes its own transaction

Best practice: Disable auto-commit in production applications for better transaction control.

7. What happens if you don’t COMMIT?

If you do not commit:

• Changes are visible only to your session
• Locks remain active
• Other sessions may be blocked
• On session crash → Oracle performs automatic rollback

8. What is implicit COMMIT?

Oracle performs an automatic commit:

• Before and after DDL statements (CREATE, DROP, ALTER, TRUNCATE)
• When session ends normally (depends on client tool)

Example:

CREATE TABLE test (id NUMBER);

Oracle commits before and after this statement.

9. Can you COMMIT inside a loop?

Yes, but it must be done carefully.

Example:

FOR rec IN (SELECT employee_id FROM employees) LOOP

   UPDATE employees SET salary = salary * 1.05

   WHERE employee_id = rec.employee_id;

 

   COMMIT; 

END LOOP;

Problems:

• Breaks transaction atomicity
• Hard to recover on failure
• Performance overhead

Better approach:

• Commit in batches (e.g., every 1000 rows)

10. What is COMMIT WRITE?

Oracle provides options to control redo writing behavior:

COMMIT WRITE IMMEDIATE WAIT;

COMMIT WRITE BATCH NOWAIT;

Options:

• IMMEDIATE – LGWR writes redo immediately
• BATCH – LGWR can group redo writes
• WAIT – Wait until redo is written to disk
• NOWAIT – Do not wait for redo write confirmation

Default: IMMEDIATE WAIT

Use carefully; NOWAIT may risk minimal data loss in rare crash scenarios.

11. How does COMMIT affect performance?

Frequent COMMITs:

• Increase redo generation overhead
• Increase LGWR activity
• Reduce performance

Very infrequent COMMITs:

• Hold locks longer
• Consume large undo space
• Risk long rollback time on failure

Best practice:

• Commit logical units of work
• Use batch commits for large data processing

12. What is the difference between COMMIT and ROLLBACK?

Feature	COMMIT	ROLLBACK
Saves changes	Yes	No
Ends transaction	Yes	Yes
Releases locks	Yes	Yes
Can undo changes later	No	Yes (before commit)

13. Can COMMIT be used inside triggers?

No.

Oracle does not allow:

• COMMIT
• ROLLBACK
• SAVEPOINT

inside triggers (except autonomous transactions).

14. What is an Autonomous Transaction?

An autonomous transaction:

• Has its own COMMIT/ROLLBACK
• Does not affect the parent transaction

Example:

PRAGMA AUTONOMOUS_TRANSACTION;

Used in:

• Logging
• Auditing
• Error tracking

15. What are common mistakes with COMMIT?

• Committing inside row-by-row loops
• Forgetting to commit → blocking sessions
• Using auto-commit unknowingly
• Mixing DDL and DML unintentionally
• Large transactions causing undo overflow

16. Best Practices for COMMIT

• Commit only after logical unit of work completes
• Avoid committing inside tight loops
• Use batch commits for bulk processing
• Disable auto-commit in applications
• Monitor undo and redo generation
• Design transactions to be short and efficient