What is the difference between COMMIT and ROLLBACK?

Feature	COMMIT	ROLLBACK
Purpose	Saves all changes permanently	Undoes all changes since the last COMMIT or SAVEPOINT
Effect	Makes changes visible to other users	Discards uncommitted changes
Undo Possible?	❌ No, changes are permanent	✅ Yes, changes are undone
Locks	Released after commit	Released after rollback
Transaction State	Ends the current transaction	Ends the current transaction
SAVEPOINT Support	Removes all savepoints	Can roll back to a specific SAVEPOINT
Data Integrity	Ensures durability of changes	Ensures recovery from mistakes
Used When	You're sure the transaction is correct	Something went wrong or needs to be canceled

 

COMMIT Example

INSERT INTO employees (id, name) VALUES (101, 'Alice');

COMMIT;

-- The insert is now permanent and visible to others.

 

ROLLBACK Example

INSERT INTO employees (id, name) VALUES (102, 'Bob');

ROLLBACK;

-- The insert is undone, and 'Bob' will not appear in the table.

 

🔸 Using SAVEPOINT with ROLLBACK

SAVEPOINT before_update;

UPDATE employees SET salary = salary * 1.1 WHERE department_id = 50;

ROLLBACK TO before_update;

-- Only the salary update is undone; other changes remain.

 

Summary:

• Use COMMIT when you're sure all changes are correct and should be saved permanently.
• Use ROLLBACK when you want to undo changes, either due to errors, validation failure, or user cancellation.

Let me know if you'd like a real-world use case or PL/SQL example involving both!

 

Can a COMMIT be rolled back?

 No, a COMMIT cannot be rolled back in Oracle.

Once a COMMIT is issued, the changes made during the transaction are permanent and cannot be undone by a ROLLBACK.

Why COMMIT Is Irreversible:

Oracle follows the ACID properties of transactions, and COMMIT ensures Durability:

• Once changes are committed:
• They are written to the redo logs.
• The transaction is marked as complete.
• Undo data is eventually discarded or overwritten.
• A new transaction begins implicitly.

 You cannot:

• Roll back a committed transaction.
• Use ROLLBACK to undo changes after a COMMIT.
• Undo a COMMIT using any SQL statement.

You can:

• Roll back changes before issuing a COMMIT.
• Use SAVEPOINT and ROLLBACK TO SAVEPOINT to undo part of a transaction before committing.

Workaround: Flashback (If Enabled)

Oracle provides Flashback features (if properly configured) that can help in recovering from committed changes:

Examples:

• FLASHBACK TABLE to restore a table to a past state.
• Querying past data using AS OF TIMESTAMP.

🔸 These require:

• Flashback features to be enabled.
• Sufficient undo/retention time.
• Proper privileges.

Bottom Line:

Once you issue a COMMIT, there is no standard way to reverse or roll back that transaction. Be certain before committing.

 

What happens when a COMMIT is issued?

When a COMMIT is issued in Oracle, it performs a series of important actions to make your transaction permanent, release resources, and notify other users that the changes are finalized.

What Happens When COMMIT is Issued

1. All Changes Become Permanent

• All DML operations (INSERT, UPDATE, DELETE) performed in the current transaction are saved to the database permanently.
• These changes are now durable (according to the "D" in ACID).

2. Changes Become Visible to Other Users

• Any other users or sessions querying the database will now be able to see the committed changes.
• Before COMMIT, those changes were only visible to your session.

3. Locks Are Released

• All row-level or table-level locks acquired during the transaction are released.
• This allows other sessions to access or modify the affected rows.

4. Savepoints Are Erased

• Any SAVEPOINTs created during the transaction are cleared.
• You can no longer roll back to them after the COMMIT.

5. Transaction Ends

• The current transaction is closed.
• A new transaction begins implicitly, so you're in a new, clean transactional state.

6. Redo Log Buffers Are Flushed to Disk

• Oracle writes the redo information (used for recovery) from memory to the online redo logs.
• This ensures the database can recover your changes in case of a crash.

7. Undo Information May Be Discarded Later

• Oracle maintains undo data temporarily to support features like read consistency and flashback.
• After a COMMIT, undo data may eventually be overwritten or reused, depending on undo retention settings.

Summary of Actions After COMMIT

Action	Description
🔒 Lock Release	All row/table locks are released
👀 Visibility	Changes become visible to other users
🧾 Redo Log Flush	Redo log buffers are written to disk
💾 Data Persistence	Changes are permanently stored
🧹 Savepoints	All savepoints are cleared
🔄 New Transaction	A new transaction begins implicitly

 

🧠 Behind the Scenes (Internals)

Oracle uses several background processes to manage this:

• LGWR (Log Writer): Writes redo entries to disk.
• DBWn (Database Writer): May eventually write dirty buffers to datafiles (not necessarily at commit).
• SMON: Handles cleanup of completed transactions.

 

 

When should I use COMMIT?

 1. After a Successful Transaction

When you've finished a series of related DML operations (INSERT, UPDATE, DELETE) and confirmed everything is correct:

-- Example

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

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

COMMIT;

This ensures both operations are saved together — ensuring transactional integrity.

2. After a Batch Job or Data Load

When you're inserting or updating large volumes of data (e.g., from a script or batch job), you may:

• Commit periodically (e.g., every 1,000 rows), or
• Commit at the end of the load, once all data has been verified.

3. After Validation

Only issue a COMMIT after:

• You’ve validated data manually or programmatically.
• Business rules and constraints have been met.

4. To Release Locks

If your transaction is holding locks on rows/tables, other users may be blocked. COMMIT releases those locks:

• Especially important in multi-user environments.
• Prevents long-running uncommitted transactions from causing contention.

5. At Logical Boundaries

In long-running processes or scripts, commit at logical checkpoints:

FORALL i IN 1..10000

    INSERT INTO sales VALUES (...);

-- Commit after every 1000 records

IF MOD(i, 1000) = 0 THEN

    COMMIT;

END IF;

When NOT to Use COMMIT

🔸 After Every Statement (Unless Required)

• Avoid committing after every DML unless it's a completely independent change.
• Doing so breaks transaction consistency and makes rollback impossible.

🔸 Before Validating Data

• Don’t commit if you haven't verified the correctness of your changes.

🔸 Inside Triggers

• Oracle does not allow COMMIT inside triggers, as it would break transaction integrity.

Summary: Use COMMIT when...

Scenario	Use COMMIT?
All DML is successful and verified	✅ Yes
Before ROLLBACK possibility is needed	❌ No
After critical error or uncertainty	❌ No
To release locks after successful operation	✅ Yes
Inside a trigger	❌ Not allowed

 

What is a COMMIT in Oracle?

In Oracle Database, a COMMIT is a SQL statement used to make all changes made during the current transaction permanent. Once you issue a COMMIT, the changes to the database (such as inserts, updates, or deletes) become visible to other users and cannot be rolled back.

 Key Points about COMMIT in Oracle:

1. Makes Changes Permanent:
• All the DML operations (INSERT, UPDATE, DELETE) performed in the transaction are saved permanently to the database.
2. Releases Locks:
• After a COMMIT, any locks held on the affected rows/tables are released, allowing other transactions to access them.
3. Ends the Transaction:
• COMMIT marks the successful end of a transaction. After it, a new transaction begins automatically.
4. Visible to Other Sessions:
• Changes are now visible to all other users and sessions once the COMMIT is executed.

Syntax:

COMMIT;

Example:

UPDATE employees

SET salary = salary * 1.10

WHERE department_id = 50;

COMMIT;

• The salary update will be permanently stored in the database after the COMMIT.
• Before the COMMIT, other users won’t see the changes, and you could still roll them back using ROLLBACK.

Auto-Commit Behavior:

• In some tools (like SQL Developer or SQL*Plus with autocommit on), every DML statement may be automatically committed.
• In PL/SQL blocks or transactions without autocommit, you need to explicitly use COMMIT.

Comparison: COMMIT vs ROLLBACK

Feature	COMMIT	ROLLBACK
Purpose	Save changes permanently	Undo all changes since last COMMIT
Reversibility	Irreversible	Reversible
Locks	Released	Released