In SQL COMMIT, ROLLBACK, and SAVEPOINT are Transaction Control Language (TCL) commands that help to manage changes made by SQL statements. They ensure data integrity and allow control over data manipulation. We can use these commands only when we are performing INSERT, UPDATE and DELETE.
A transaction is a sequence of SQL statements that SQLite treats as a single unit. A set of changes made to a table using INSERT, UPDATE or DELETE are temporary. To make the changes permanent, COMMIT must be issued. Part or all of a transaction can be undo with the help of ROLLBACK statement. SAVEPOINT marks a point to roll back, if needed. A transaction begins with the execution of the first SQL statement in the transaction and ends with either the Rollback statement or the commit statement.
Here’s a brief overview of each, along with examples:
COMMIT:
The COMMIT command is used to permanently save the changes made during a transaction to the database. Once a transaction is committed, the changes become permanent in the database. Without a COMMIT, the changes are only temporary and can be rolled back. Use COMMIT to save changes.
Syntax:
COMMIT;
COMMIT with INSERT:
The INSERT statement adds data to a table, but these changes are not immediately made permanent and visible to other users. Without the COMMIT, the inserted data would remain in a temporary state and could be lost if a ROLLBACK operation is performed. To finalize the changes and make them persistent in the database, a COMMIT statement is required.
Example:
BEGIN;
INSERT INTO student VALUES (1, ‘Bishal’);
INSERT INTO student VALUES (2, ‘Aryan’);
COMMIT;
In this example, BEGIN; statement starts a transaction. We use BEGIN (or BEGIN TRANSACTION / START TRANSACTION) to start a transaction.
The COMMIT; statement finalizes the transaction. Both insertions will be permanently saved to the student table once the COMMIT command is executed.
COMMIT with UPDATE:
The UPDATE statement modifies existing records in a table, but these changes are not permanently stored in the database until a COMMIT command is executed. The COMMIT command finalizes the transaction, making all changes performed within it permanent.
Example:
BEGIN;
UPDATE student SET name=’Kishor’ WHERE rollno=1;
COMMIT;
COMMIT with DELETE:
The DELETE statement is used to removes records from a table. After executing a DELETE statement, the changes are not immediately permanent in the database. To make these changes permanent within the database, the COMMIT command is used. Once committed, the deletion cannot be undone using ROLLBACK command.
Example:
BEGIN;
DELETE FROM student where rollno=5;
COMMIT;
Practical Example:
ROLLBACK:
The ROLLBACK command is used to undo changes made within a transaction that have not yet been permanently saved to the database. If a transaction is rolled back, all changes made since the last COMMIT or SAVEPOINT are discarded. The primary purpose of ROLLBACK is to cancel all data modifications (like INSERT, UPDATE, DELETE) made within a transaction if an error occurs or the transactions are no longer desired. Use ROLLBACK to undo changes.
ROLLBACK only affects uncommitted changes within the current transaction. Once a COMMIT statement is executed, the changes are permanent and cannot be undone with ROLLBACK.
Syntax:
ROLLBACK;
Or, To roll back to a specific savepoint we have to write,
ROLLBACK TO savepoint_name;
ROLLBACK with INSERT:
A ROLLBACK operation when used with INSERT statement serves to undo any data insertions that have occurred within the current transaction. When used with an INSERT statement, ROLLBACK effectively reverses the insertion, removing the newly added record(s) as if they were never inserted.
Example:
BEGIN;
INSERT INTO student VALUES (5, ‘Utpal’);
INSERT INTO student VALUES (6, ‘Pritom’);
ROLLBACK;
In this case, neither of the insertions will be saved to the student table because the transaction was rolled back.
ROLLBACK with UPDATE:
If the UPDATE operation was incorrect, failed or needs to be undone for any reason, issues the ROLLBACK statement. This will undo all changes made within the current transaction.
Example:
BEGIN;
UPDATE student SET name=” Pankaj” WHERE rollno=2;
ROLLBACK;
Here, the name update will be undone and the database will revert to its state before the transaction started.
ROLLBACK with DELETE:
A DELETE statement can be rolled back if executed with a transaction. This allows for the undoing of changes made by the DELETE statement, effectively restoring the deleted records. If the DELETE operation was accidental or needs to be undine, use the ROLLBACK command. This will revert all changes made within the current transaction.
Example:
BEGIN;
DELETE FROM student where rollno=4;
ROLLBACK;
Practical Example:
SAVEPOINT:
The SAVEPOINT command is used to set a point within a transaction. SAVEPOINT save data till that point. It creates a temporary marker within a transaction, allowing for partial rollbacks. SAVEPOINT allows us to roll back to a specific point in a transaction without affecting earlier changes. It is used with ROLLBACK TO savepoint_name.
A SAVEPOINT is a special mark inside a transaction that allows all commands that are executed after it, was established to be rolled back, restoring the transaction.
Syntax:
SAVEPOINT savepoint_name;
SAVEPOINT with INSERT:
The following SQL demonstrates using SAVEPOINT with INSERT statements. Only the INSERT for “Riyan” will be permanently saved, while the one for “Gunjan” will be rolled back to the savepoint.
Example:
BEGIN;
INSERT INTO student VALUES (5, ‘Riyan’);
SAVEPOINT a;
INSERT INTO student VALUES (6, ‘Gunjan’);
SAVEPOINT b;
ROLLBACK TO a;
COMMIT;
SAVEPOINT with UPDATE:
The following example demonstrates how to use a savepoint around an UPDATE operation. Only the UPDATE of rollno 1 will be permanently saved, while the one for rollno 2 will be rolled back to the savepoint.
Example:
BEGIN;
UPDATE student SET name=’Rishi’ WHERE rollno=1;
SAVEPOINT x;
UPDATE student SET name=’Sneha’ WHERE rollno=2;
SAVEPOINT y;
ROLLBACK TO x;
COMMIT;
SAVEPOINT with DELETE:
Here is an example in SQL showing how a DELETE operation works with savepoints:
BEGIN;
DELETE FROM student WHERE rollno=2;
SAVEPOINT sp1;
DELETE FROM student WHERE rollno=4;
SAVEPOINT sp2;
DELETE FROM student WHERE rollno=5;
SAVEPOINT sp3;
ROLLBACK TO sp1;
COMMIT;
Practical Example:
Example for Practice:
Example – 1:
1. Create a table named STUDENT with fields roll_no, name and address.
CREATE TABLE student (roll_no int, name char, address char);
2. Insert 5 records into the table.
INSERT INTO student VALUES (1, ‘Rehan’,’Delhi);
INSERT INTO student VALUES (2, ‘Nency’, ‘Mumbai’);
INSERT INTO student VALUES (3, ‘Florina’, ‘Guwahati’);
INSERT INTO student VALUES (4, ‘Silpa’, ‘Delhi’);
INSERT INTO student VALUES (5, ‘Barsha’, ‘Mumbai’);
3. Start transaction
BEGIN;
4. Insert a new student into the STUDENT table.
INSERT INTO student VALUES (6,’Mamta’,’Tinsukia’);
5. Create a savepoint
SAVEPOINT X;
6. Insert another student into the table.
INSERT INTO student VALUES (7,’Baby’,’Guwahati’);
7. rollback to savepoint
ROLLBACK TO X;
8. Insert one more student record
INSERT INTO student VALUES (8,’Sabnam’,’Tinsukia’);
9. Create another savepoint.
SAVEPOINT Y;
10. Commit all changes
COMMIT;
Example – 2:
1. Create a table Employee with 3 attributes emp_id, emp_name and salary.
CREATE TABLE Employee(emp_id int, emp_name char, salary int);
2. Insert five records into the table.
BEGIN;
INSERT INTO Employee VALUES(1001,’Rishi Das’,40000);
INSERT INTO Employee VALUES(1002,’Riya Paul’,45000);
INSERT INTO Employee VALUES(1003,’Raja Gogoi’,30000);
INSERT INTO Employee VALUES(1004,’Deep Roy’,35000);
INSERT INTO Employee VALUES(1005,’Liza Deb’,32000);
3. Commit the transaction.
COMMIT;
4. Update employee name “Rajiv Das” of emp_id 1001.
UPDATE Employee SET emp_name=’Rajiv Das’ WHERE emp_id=1001;
5. Set a savepoint.
SAVEPOINT a;
6. Insert a new record into the table.
INSERT INTO Employee VALUES (1006,’Vidya Mehta’,42000);
7. Delete one record from the table.
DELETE FROM Employee WHERE emp_id=1004;
8. Set a savepoint.
SAVEPOINT b;
9. Rollback the current transaction to savepoint a.
ROLLBACK to a;
10. Commit the transaction.
COMMIT;
Example – 3
I am constantly searching online for articles that can benefit me. Thank you!