COMMIT

 In Oracle, a COMMIT operation is used to save changes made during a transaction. Once a COMMIT statement is executed, all changes made to the database (such as inserts, updates, or deletes) are permanently saved. The transaction is considered complete, and the changes become visible to other users or sessions.

Here’s a detailed breakdown of COMMIT in Oracle:

1. What is a Transaction?

A transaction is a sequence of one or more SQL operations that are executed as a single unit. These operations are either all committed or none of them are committed (if rolled back).

Example Operations: INSERT, UPDATE, DELETE

Example of Transaction:

BEGIN

  INSERT INTO employees (id, name) VALUES (1, 'John');

  UPDATE employees SET salary = 6000 WHERE id = 1;

COMMIT;

2. COMMIT and Transaction Management

• Begin Transaction: A transaction starts implicitly with the first SQL statement.
• Commit the Transaction: The COMMIT command saves the changes to the database.
• Rollback the Transaction: You can undo all changes since the last COMMIT with the ROLLBACK statement.

3. The Commit Process

• When a COMMIT is executed, all changes made during the current transaction are saved.
• Once committed, data is physically written to the database.
• A COMMIT releases all locks held on the data, making it available for other users.
• The transaction is marked as complete, and a new transaction starts after the commit.

4. Syntax

The syntax of the COMMIT command is simple:

COMMIT;

There is also a variant using COMMIT WITH COMMENT, which allows adding a note or comment along with the commit:

COMMIT COMMENT 'Description of the commit';

This can be useful for tracking changes or associating commits with particular operations in the database.

5. Behavior of Commit

• Persistence: Once committed, the changes are permanent, and there is no way to roll back.
• Transaction Completion: The commit ends the current transaction and starts a new one.
• Visibility: After a commit, changes are visible to other users or sessions who are accessing the same database.

6. Commit in Distributed Transactions

Oracle supports distributed transactions where data is updated in multiple databases. A COMMIT can be issued to apply changes across these systems, ensuring that all related changes are applied together. This is typically handled through two-phase commit (2PC) protocol, ensuring consistency across distributed systems.

7. Autonomous Transactions

In Oracle, you can perform autonomous transactions in a subroutine using the PRAGMA AUTONOMOUS_TRANSACTION directive. In such a case, the changes made within this autonomous transaction can be committed or rolled back separately from the main transaction.

8. COMMIT and Locking

• When you perform a DML operation (INSERT, UPDATE, DELETE), Oracle automatically locks the affected rows to prevent other sessions from modifying them.
• A COMMIT releases the locks, making the data available for others.
• Without committing, the changes will not be visible to other users, and the locks will be held.

9. COMMIT and Performance

• Frequent commits (especially in high-transaction environments) can result in increased I/O operations due to writing to disk. It’s important to balance committing with performance needs.
• Too few commits can cause long-running transactions that hold locks for extended periods, potentially affecting the overall database performance.

10. COMMIT in PL/SQL

In PL/SQL, COMMIT can be used to end a transaction explicitly. However, it’s important to note that when working within PL/SQL, the commit might affect larger units of work. Sometimes, you might need to commit or rollback within specific sections of your PL/SQL block.

BEGIN

  -- DML operations

  COMMIT;  -- Committing within a PL/SQL block

END;

11. Commit with SAVEPOINT

Oracle allows for savepoints in a transaction, which lets you roll back to a specific point within a transaction instead of the entire transaction. After executing a savepoint, you can commit the transaction up to that point.

SAVEPOINT my_savepoint;

-- Some operations

COMMIT;

12. COMMIT and Triggers

• After a COMMIT, any associated AFTER COMMIT triggers will be fired, if defined. These triggers are useful for performing actions after changes are permanently saved to the database.

13. Transaction Isolation

Oracle’s transaction isolation ensures that the changes made in one transaction are isolated from other transactions until they are committed. This ensures consistency, and the ACID properties (Atomicity, Consistency, Isolation, Durability) are maintained.

• Isolation Levels: Oracle supports different isolation levels (READ COMMITTED, SERIALIZABLE, etc.), and the commit operation plays a key role in determining how data is visible to other sessions.

14. Rollback and Commit

The ROLLBACK operation allows you to undo a transaction. Any uncommitted changes are reverted to their original state.

• COMMIT makes changes permanent.
• ROLLBACK undoes any changes made since the last COMMIT.

15. Examples

Simple Example:

BEGIN;

UPDATE employees SET salary = 5000 WHERE id = 1;

COMMIT;

With Comment:

COMMIT COMMENT 'Updated salary for employee 1';

16. Commit Notes (Audit and Documentation)

In complex systems, the COMMENT feature in the COMMIT statement is useful for providing context about the change. It’s helpful for:

• Tracking changes made by different users
• Linking commits to specific business processes or requirements
• Auditing purposes