In Oracle Database, COMMIT and ROLLBACK are two essential SQL commands used to manage transaction boundaries. A transaction is a logical unit of work that consists of one or more SQL statements. These commands are crucial for maintaining data integrity and consistency.
COMMIT
The COMMIT command is used to permanently save changes made within a transaction. Once a transaction is committed, the changes become permanent and cannot be undone.
Syntax:
COMMIT;
Example:
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (101, 'John', 'Doe', 50000);
UPDATE employees
SET salary = 60000
WHERE employee_id = 101;
COMMIT;
In this example, the INSERT and UPDATE statements are part of a single transaction. When the COMMIT command is executed, the changes to the employees table are permanently saved to the database.
ROLLBACK
The ROLLBACK command is used to undo changes made within a transaction. If a transaction is rolled back, the database is restored to its state before the transaction began.
Syntax:
ROLLBACK;
Example:
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (102, 'Jane', 'Smith', 45000);
-- Some error occurs here
ROLLBACK;
In this example, if an error occurs after the INSERT statement, the ROLLBACK command can be used to undo the insertion, leaving the employees table unchanged.
Key Points:
Implicit Commit: Oracle automatically commits a transaction when the SQL session ends or when an explicit COMMIT is issued.
Explicit Commit: You can explicitly commit a transaction using the COMMIT command.
Rollback Segment: Oracle uses rollback segments to store information about uncommitted transactions.
Transaction Isolation Levels: Oracle supports different transaction isolation levels (READ COMMITTED, SERIALIZABLE, etc.) to control how transactions interact with each other.
By understanding and effectively using COMMIT and ROLLBACK, you can ensure data integrity and recover from errors in your Oracle database.
Comments