The COMMIT statement in Oracle PL/SQL is a crucial command used in transaction control. It finalizes changes made by a transaction, making them permanent in the database. Without a COMMIT, changes remain temporary and can be rolled back. Understanding how and when to use COMMIT effectively ensures data integrity and prevents unintended data loss.
What is the COMMIT Statement?
In Oracle PL/SQL, the COMMIT statement is used to save all changes made during the current transaction. Once a COMMIT is executed:
The changes become permanent.
Other database users can see the changes.
Savepoints (temporary markers within a transaction) are erased.
Locks on the modified rows are released.
Syntax
COMMIT;
Oracle also provides an optional WRITE clause for performance tuning:
COMMIT WRITE IMMEDIATE WAIT;
IMMEDIATE ensures changes are immediately written to disk.
WAIT makes the COMMIT operation wait for confirmation before proceeding.
How COMMIT Works in PL/SQL
When a transaction begins, any DML (Data Manipulation Language) operation such as INSERT, UPDATE, or DELETE does not immediately reflect in the database. These changes remain temporary until a COMMIT is issued.
Example 1: Basic COMMIT Usage
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
COMMIT;
END;
In this example, the salary increase for employees in department 10 is made permanent only after the COMMIT statement.
Example 2: Using SAVEPOINT and COMMIT
BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;
SAVEPOINT before_bonus;
UPDATE employees SET salary = salary * 1.2 WHERE department_id = 20;
COMMIT;
END;
SAVEPOINT before_bonus; allows rolling back to that specific point if needed before the final COMMIT.
When to Use COMMIT
After successful completion of a logical transaction to make changes permanent.
Before performing operations that might fail, ensuring prior operations remain unaffected.
To improve concurrency by releasing row locks that might be blocking other transactions.
Implicit vs. Explicit COMMIT
Implicit COMMIT occurs automatically in certain cases, such as when executing DDL (Data Definition Language) commands like CREATE, DROP, or ALTER.
Explicit COMMIT is necessary for DML operations to confirm changes.
Conclusion
The COMMIT statement in Oracle PL/SQL is a fundamental part of transaction control. Proper use ensures data consistency and prevents accidental data loss. Always use COMMIT thoughtfully, particularly in multi-step transactions, to maintain database integrity.