In Oracle databases, the COMMIT, ROLLBACK, and SAVEPOINT commands are used in transaction management to ensure data consistency and integrity even in the presence of errors or exceptions.
- COMMIT: The COMMIT command is used to save all changes made to the database since the start of a transaction. Once a COMMIT command is executed, all the changes become permanent and cannot be rolled back. The COMMIT command is typically used when a transaction is completed successfully and the changes need to be made permanent, such as when a user submits a form in a web application.
Example:
BEGIN
INSERT INTO employees (id, name, salary) VALUES (1, 'John', 5000);
INSERT INTO employees (id, name, salary) VALUES (2, 'Jane', 6000);
COMMIT;
END;In this example, the transaction begins with the ‘BEGIN‘ statement followed by two insert statements. The ‘COMMIT‘ statement is used to make the changes made in the inserts permanent.
- ROLLBACK: The ROLLBACK command is used to undo all the changes made to the database since the start of a transaction. When a ROLLBACK command is executed, all the changes made in the transaction are lost, and the database is restored to its previous state. The ROLLBACK command is typically used when a transaction encounters an error or fails to complete.
Example:
BEGIN
INSERT INTO employees (id, name, salary) VALUES (1, 'John', 5000);
INSERT INTO employees (id, name, salary) VALUES (2, 'Jane', 'invalid-salary');
COMMIT;
END;In this example, the first insert statement is successful, but the second insert statement fails due to an invalid salary value. Since there was an error, the transaction is rolled back, and both changes are undone.
- SAVEPOINT: The SAVEPOINT command is used to divide a transaction into smaller parts that can be rolled back independently. When a SAVEPOINT command is executed, a specific point in the transaction is marked, and subsequent changes can be rolled back to that point without affecting the earlier changes made in the transaction.
Example:
BEGIN
INSERT INTO employees (id, name, salary) VALUES (1, 'John', 5000);
SAVEPOINT mysavepoint;
INSERT INTO employees (id, name, salary) VALUES (2, 'Jane', 'invalid-salary');
ROLLBACK TO mysavepoint;
INSERT INTO employees (id, name, salary) VALUES (3, 'Jack', 7000);
COMMIT;
END;In this example, the first insert statement is successful, and a SAVEPOINT named ‘mysavepoint‘ is created. The second insert statement fails due to an invalid salary value, and a ROLLBACK command is issued to undo that change. The third insert statement is successful, and the transaction is committed, making both the first and third insert statements permanent.
Overall, the COMMIT, ROLLBACK, and SAVEPOINT commands provide a robust and flexible transaction management system that enables Oracle databases to handle complex operations while maintaining data consistency and integrity.