In SQL COMMIT, ROLLBACK, and SAVEPOINT are transaction control 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 UPDATE, DELETE or INSERT 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 save all the changes made during the current transaction. Once a transaction is committed, the changes become permanent in the database. It will commit the transaction that means all the changes saved to the database. Use COMMIT to save changes.
Syntax:
COMMIT;
Example: COMMIT with Insert:
BEGIN;
INSERT INTO student (rollno, name) VALUES (1, ‘Bishal’);
INSERT INTO student (rollno, name) VALUES (2, ‘Aryan’);
COMMIT;
In this example, both insertions will be saved to the student table once the COMMIT command is executed.
Example: COMMIT with Delete :
BEGIN;
DELETE FROM student where rollno=5;
COMMIT;
ROLLBACK: It will rollback the complete transaction. The ROLLBACK command is used to undo changes made during the transaction. If a transaction is rolled back, all changes made since the last COMMIT or SAVEPOINT are discarded. ROLLBACK is useful when an error occurs. Use ROLLBACK to undo changes.
Syntax:
ROLLBACK;
Example: ROLLBACK with Insert:
BEGIN;
INSERT INTO student (rollno, name) VALUES (1, ‘Bishal’);
INSERT INTO student (rollno, name) VALUES (2, ‘Aryan’);
ROLLBACK;
In this case, neither of the insertions will be saved to the student table because the transaction was rolled back.
Example: ROLLBACK with Delete:
BEGIN;
DELETE FROM student where rollno=3;
ROLLBACK;
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;
Example: SAVEPOINT with Insert:
BEGIN;
INSERT INTO student (rollno, name) VALUES (1, ‘Bishal’);
SAVEPOINT sp1;
INSERT INTO student (rollno, name) VALUES (2, ‘Aryan’);
ROLLBACK TO sp1;
COMMIT;
In this example, the insertion of ‘Aryan’ will be undone because we rolled back to the savepoint sp1. However, the insertion of ‘Aryan’ will be committed to the student table.
Example: SAVEPOINT with Update:
BEGIN;
update employee set name=’Rishi’ where empid=1;
SAVEPOINT sp1;
update employee set name=’Soni’ where empid=4;
savepoint sp2;
ROLLBACK TO sp1;
COMMIT;
Example: SAVEPOINT with Delete:
begin;
delete from employee where empid=2;
savepoint x;
delete from employee where empid=3;
savepoint y;
delete from employee where empid=4;
rollback to y;
release savepoint y;
commit;
- rollback to y reverts only the work done after that savepoint.
- We can create multiple savepoints and rollback to any previous one.
- release savepoint (optional) deletes a savepoint, does not affect changes.
Example: Using COMMIT, ROLLBACK, and SAVEPOINT
1. Start transaction
BEGIN;
2. Insert a new student
insert into student(rollno,name) values(4,’Amisha’);
3. Create a savepoint
SAVEPOINT sp1;
4. Insert another student
insert into student(rollno,name) values(5,’Baby’);
5. rollback to savepoint
ROLLBACK TO sp1;
6. Insert one more student record.
insert into student(rollno,name) values(6,’Junmoni’);
7. Commit all changes
COMMIT;
Explanation:
| Step | Action |
| BEGIN | Starts a transaction |
| INSERT | Adds students |
| SAVEPOINT | Marks a point to roll back to if needed |
| ROLLBACK TO sp1 | Undoes only the 2nd insert |
| COMMIT | Saves final changes to the database |
Practice Example-I:
1. Create a table STUDENT with 2 fields: rollno and name.
create table student(rollno int(2),name varchar(20));
2. Insert two rows into the table.
insert into student values(1,’Priya’);
insert into student values(2,’Rimpi’);
3. Set a savepoint
SAVEPOINT sp1;
4. Insert a row into the table STUDENT.
insert into student values(3,’Bijit’)
5. Rollback the current transaction to savepoint sp1.
ROLLBACK to sp1;
6. Select the data from the table STUDENT.
select * from Student;
The SELECT command display rows one and two but not three.
Practice Example-II:
1. Create a table employee with 2 attributes empid and name.
create table employee(empid int(3),name varchar(20));
2. Insert five records into the table.
begin;
insert into employee values(1,’Rishi’);
insert into employee values(2,’Nikhil’);
insert into employee values(3,’Jutish’);
insert into employee values(4,’Karandeep’);
insert into employee values(5,’Deepjyoti’);
3. Commit the transaction.
commit;
4. Update employee name “Riyan” of empid 1.
update employee set name=’Riyan’ where empid=1;
5. Set a savepoint.
savepoint a;
6. Insert a new record into the table.
insert into employee values(6,’Roktim’);
7. Set a savepoint.
savepoint b;
8. Insert one more records into the table.
insert into employee values(7,’Suraj’);
9. Set a savepoint.
savepoint c;
10. Rollback the current transaction to savepoint b.
rollback to b;
11. Rollback the current transaction to savepoint a.
rollback to a;
