In SQL, SAVEPOINT, ROLLBACK, and COMMIT are essential components of Transaction Control Language (TCL).
TCL in SQL helps in managing transactions within a database effectively.
SQL SAVEPOINT
The SAVEPOINT command in SQL allows us to set a point within a transaction to which we can roll back without affecting the entire transaction.
This is particularly useful in managing long or complex transactions.
Consider a scenario where we are updating records in the Customers table and want to create a savepoint after each update. Here's how we do it:
-- start transaction
BEGIN TRANSACTION;
-- update customer 1's age
UPDATE Customers SET age = 32 WHERE customer_id = 1;
-- create a savepoint named SP1
SAVEPOINT SP1;
-- update customer 2's country
UPDATE Customers SET country = 'Canada' WHERE customer_id = 2;
-- create a savepoint named SP2
SAVEPOINT SP2;
-- If an error occurs, we can rollback to SP1 or SP2
Here, SAVEPOINT SP1 and SAVEPOINT SP2 allow us to roll back to those points without undoing all previous changes.
ROLLBACK in SQL
The ROLLBACK command in SQL is used to undo transactions that have not been committed to the database. It's a way to revert the state of the database to the last committed state.
Imagine a scenario where we need to delete a record from the Orders table but decide to rollback the transaction.
-- start transaction
BEGIN TRANSACTION;
-- delete order with order_id 5
DELETE FROM Orders WHERE order_id = 5;
-- after some operations, decide to rollback
ROLLBACK;
Here, the ROLLBACK statement here undoes the deletion of the order.
COMMIT in SQL
The COMMIT command in SQL is used to save all changes made during the current transaction permanently.
Let's update a record in the Shippings table and then commit the transaction. Here's the query:
-- start transaction
BEGIN TRANSACTION;
-- update the status of shipping_id 1
UPDATE Shippings SET status = 'Shipped' WHERE shipping_id = 1;
-- commit the transaction
COMMIT;
Here, the COMMIT statement permanently saves the changes made to the Shippings table.