Commit, Rollback and Savepoint SQL commands
Transaction Control Language(TCL) commands are used to manage transactions in the database.
Before moving forward with TCL commands, check these topics out first:
These are used to manage the changes made to the data in a table by DML statements. It also allows statements to be grouped together into logical transactions.
COMMIT
command
COMMIT
command is used to permanently save any transaction into the database.
When we use any DML command like INSERT
, UPDATE
or DELETE
, the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back.
To avoid that, we use the COMMIT
command to mark the changes as permanent.
Following is commit command's syntax,
COMMIT;
ROLLBACK
command
This command restores the database to last commited state. It is also used with SAVEPOINT
command to jump to a savepoint in an ongoing transaction.
If we have used the UPDATE
command to make some changes into the database, and realise that those changes were not required, then we can use the ROLLBACK
command to rollback those changes, if they were not commited using the COMMIT
command.
Following is rollback command's syntax,
ROLLBACK TO savepoint_name;
SAVEPOINT
command
SAVEPOINT
command is used to temporarily save a transaction so that you can rollback to that point whenever required.
Following is savepoint command's syntax,
SAVEPOINT savepoint_name;
In short, using this command we can name the different states of our data in any table and then rollback to that state using the ROLLBACK
command whenever required.
Using Savepoint and Rollback
Following is the table class,
Lets use some SQL queries on the above table and see the results.
INSERT INTO class VALUES(5, 'Rahul');
COMMIT;
UPDATE class SET name = 'Abhijit' WHERE id = '5';
SAVEPOINT A;
INSERT INTO class VALUES(6, 'Chris');
SAVEPOINT B;
INSERT INTO class VALUES(7, 'Bravo');
SAVEPOINT C;
SELECT * FROM class;
NOTE: SELECT
statement is used to show the data stored in the table.
The resultant table will look like,
id | name |
1 | Abhi |
2 | Adam |
4 | Alex |
5 | Abhijit |
6 | Chris |
7 | Bravo |
Now let's use the ROLLBACK
command to roll back the state of data to the savepoint B.
ROLLBACK TO B;
SELECT * FROM class;
Now our class table will look like,
id | name |
1 | Abhi |
2 | Adam |
4 | Alex |
5 | Abhijit |
6 | Chris |
Now let's again use the ROLLBACK
command to roll back the state of data to the savepoint A
ROLLBACK TO A;
SELECT * FROM class;
Now the table will look like,
id | name |
1 | Abhi |
2 | Adam |
4 | Alex |
5 | Abhijit |
So now you know how the commands COMMIT
, ROLLBACK
and SAVEPOINT
works.