TCL Commands in SQL
- In SQL, TCL stands for Transaction control language.
- A single unit of work in a database is formed after the consecutive execution of commands is known as a transaction.
- There are certain commands present in SQL known as TCL commands that help the user manage the transactions that take place in a database.
- COMMIT. ROLLBACK and SAVEPOINT are the most commonly used TCL commands in SQL.
Now let us take a deeper dive into the TCL commands of SQL with the help of examples. All the queries in the examples will be written using the MySQL database.
1. COMMIT
COMMIT command in SQL is used to save all the transaction-related changes permanently to the disk. Whenever DDL commands such as INSERT, UPDATE and DELETE are used, the changes made by these commands are permanent only after closing the current session. So before closing the session, one can easily roll back the changes made by the DDL commands. Hence, if we want the changes to be saved permanently to the disk without closing the session, we will use the commit command.
Syntax:
- COMMIT;
Example:
We will select an existing database, i.e., school.
- mysql> USE school;
To create a table named t_school, we will execute the following query:
- mysql> CREATE TABLE t_school(ID INT, School_Name VARCHAR(40), Number_Of_Students INT, Number_Of_Teachers INT, Number_Of_Classrooms INT, EmailID VARCHAR(40));
BEGIN / START TRANSACTION command is used to start the transaction.
- mysql> START TRANSACTION;
Now, we will execute the following query to insert multiple records at the same time in the t_school table.
- mysql> INSERT INTO t_school(ID, School_Name, Number_Of_Students, Number_Of_Teachers, Number_Of_Classrooms, EmailID) VALUES(1, "Boys Town Public School", 1000, 80, 12, "btps15@gmail.com"), (2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25@gmail.com"), (3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"), (4, "Ashoka Universal School", 1110, 40, 40, "aus17@gmail.com"), (5, "Calibers English Medium School", 9000, 31, 50, "cems@gmail.com");
We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.
- mysql> SELECT *FROM t_school;
After executing the SELECT query on the t_school table, you will get the following output:
| ID | School_Name | Number_Of_Students | Number_Of_Teachers | Number_Of_Classrooms | EmailID |
|---|---|---|---|---|---|
| 1 | Boys Town Public School | 1000 | 80 | 12 | btps15@gmail.com |
| 2 | Guru Govind Singh Public School | 800 | 35 | 15 | ggps25@gmail.com |
| 3 | Delhi Public School | 1200 | 30 | 10 | dps101@gmail.com |
| 4 | Ashoka Universal School | 1110 | 40 | 40 | aus17@gmail.com |
| 5 | Calibers English Medium School | 9000 | 31 | 50 | cems@gmail.com |
The output of the SELECT query shows that all the records are inserted successfully.
We will execute the COMMIT command to save the results of the operations carried on the t_school table.
- mysql> COMMIT;
Autocommit is by default enabled in MySQL. To turn it off, we will set the value of autocommit as 0.
- mysql> SET autocommit = 0;
MySQL, by default, commits every query the user executes. But if the user wishes to commit only the specific queries instead of committing every query, then turning off the autocommit is useful.
2. SAVEPOINT
We can divide the database operations into parts. For example, we can consider all the insert related queries that we will execute consecutively as one part of the transaction and the delete command as the other part of the transaction. Using the SAVEPOINT command in SQL, we can save these different parts of the same transaction using different names. For example, we can save all the insert related queries with the savepoint named INS. To save all the insert related queries in one savepoint, we have to execute the SAVEPOINT query followed by the savepoint name after finishing the insert command execution.
Syntax:
- SAVEPOINT savepoint_name;
3. ROLLBACK
While carrying a transaction, we must create savepoints to save different parts of the transaction. According to the user's changing requirements, he/she can roll back the transaction to different savepoints. Consider a scenario: We have initiated a transaction followed by the table creation and record insertion into the table. After inserting records, we have created a savepoint INS. Then we executed a delete query, but later we thought that mistakenly we had removed the useful record. Therefore in such situations, we have an option of rolling back our transaction. In this case, we have to roll back our transaction using the ROLLBACK command to the savepoint INS, which we have created before executing the DELETE query.
Syntax:
- ROLLBACK TO savepoint_name;
Examples to understand the SAVEPOINT and ROLLBACK commands:
Example 1:
We will select an existing database, i.e., school.
- mysql> USE school;
To create a table named t_school, we will execute the following query:
- mysql> CREATE TABLE t_school(ID INT, School_Name VARCHAR(40), Number_Of_Students INT, Number_Of_Teachers INT, Number_Of_Classrooms INT, EmailID VARCHAR(40));
Now, we will execute the following query to insert multiple records at the same time in the t_school table.
- mysql> INSERT INTO t_school(ID, School_Name, Number_Of_Students, Number_Of_Teachers, Number_Of_Classrooms, EmailID) VALUES(1, "Boys Town Public School", 1000, 80, 12, "btps15@gmail.com"), (2, "Guru Govind Singh Public School", 800, 35, 15, "ggps25@gmail.com"), (3, "Delhi Public School", 1200, 30, 10, "dps101@gmail.com"), (4, "Ashoka Universal School", 1110, 40, 40, "aus17@gmail.com"), (5, "Calibers English Medium School", 9000, 31, 50, "cems@gmail.com");
We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.
- mysql> SELECT *FROM t_school;
After executing the SELECT query on the t_school table, you will get the following output:
| ID | School_Name | Number_Of_Students | Number_Of_Teachers | Number_Of_Classrooms | EmailID |
|---|---|---|---|---|---|
| 1 | Boys Town Public School 1000 | 80 | 12 | btps15@gmail.com | |
| 2 | Guru Govind Singh Public School | 800 | 35 | 15 | ggps25@gmail.com |
| 3 | Delhi Public School | 1200 | 30 | 10 | dps101@gmail.com |
| 4 | Ashoka Universal School | 1110 | 40 | 40 | aus17@gmail.com |
| 5 | Calibers English Medium School | 9000 | 31 | 50 | cems@gmail.com |
The output of the SELECT query shows that all the records are inserted successfully.
BEGIN / START TRANSACTION command is used to start the transaction.
- mysql> START TRANSACTION;
As we know, the SAVEPOINT command in SQL is used to save the different parts of the same transaction using different names. Consider till this point as one part of our transaction. We will save this part using a savepoint named Insertion.
- mysql> SAVEPOINT Insertion;
Now, we will execute the update command on the t_school table to set the Number_Of_Students as 9050 for the record with ID 5.
- mysql> UPDATE t_school SET Number_Of_Students = 9050 WHERE ID = 5;
To verify that the record with ID 5 now has the Number_Of_Students as 9050, we will execute the SELECT query.
- mysql> SELECT *FROM t_school;
After executing the SELECT query on the t_school table, you will get the following output:
| ID | School_Name | Number_Of_Students | Number_Of_Teachers | Number_Of_Classrooms | EmailID |
|---|---|---|---|---|---|
| 1 | Boys Town Public School | 1000 | 80 | 12 | btps15@gmail.com |
| 2 | Guru Govind Singh Public School | 800 | 35 | 15 | ggps25@gmail.com |
| 3 | Delhi Public School | 1200 | 30 | 10 | dps101@gmail.com |
| 4 | Ashoka Universal School | 1110 | 40 | 40 | aus17@gmail.com |
| 5 | Calibers English Medium School | 9050 | 31 | 50 | cems@gmail.com |
The output of the SELECT query shows that the record with ID 5 is updated successfully.
Consider the update operation as one part of our transaction. We will save this part using a savepoint named Updation.
- mysql> SAVEPOINT Updation;
Suddenly, our requirement changed, and we realized that we had updated a record that was not supposed to be. In such a scenario, we need to roll back our transaction to the savepoint, which was created prior to the execution of the UPDATE command.
- mysql> ROLLBACK TO Insertion;
We didn't need the updation carried on the record. Hence, we have rolled back to the savepoint named Insertion.
For confirming that we have got the same t_school table that we had before carrying out the updation operation, we will again execute the SELECT query.
- mysql> SELECT *FROM t_school;
| ID | School_Name | Number_Of_Students | Number_Of_Teachers | Number_Of_Classrooms | EmailID |
|---|---|---|---|---|---|
| 1 | Boys Town Public School | 1000 | 80 | 12 | btps15@gmail.comm |
| 2 | Guru Govind Singh Public School | 800 | 35 | 15 | ggps25@gmail.comm |
| 3 | Delhi Public School | 1200 | 30 | 10 | dps101@gmail.comm |
| 4 | Ashoka Universal School | 1110 | 40 | 40 | aus17@gmail.comm |
| 5 | Calibers English Medium School | 9000 | 31 | 50 | cems@gmail.com |
The SELECT query output confirms that the transaction is now successfully rolled back to the savepoint 'Insertion'.
Example 2:
We will select an existing database, i.e., bank.
- mysql> USE bank;
To create a table named customer, we will execute the following query:
- mysql> CREATE TABLE customer(Customer_ID INT PRIMARY KEY, Name VARCHAR(20), Age INT, Salary INT, Salary_BankAccount VARCHAR(20));
Now, we will execute the following query to insert multiple records at the same time in the customer table.
- mysql> INSERT INTO customer(Customer_ID, Name, Age, Salary, Salary_BankAccount) VALUES(1, "Aryan Jain", 51, 56000, "SBI"), (2, "Arohi Dixit", 21, 25000, "Axis"), (3, "Vineet Garg", 24, 31000, "ICICI"), (4, "Anuja Sharma", 26, 49000, "HDFC"), (5, "Deepak Kohli", 28, 65000, "SBI");
We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.
- mysql> SELECT *FROM customer;
After executing the SELECT query on the t_school table, you will get the following output:
| Customer_ID | Name | Age | Salary | Salary_BankAccount |
|---|---|---|---|---|
| 1 | Aryan Jain | 51 | 56000 | SBI |
| 2 | Arohi Dixit | 21 | 25000 | Axis |
| 3 | Vineet Garg | 24 | 31000 | ICICI |
| 4 | Anuja Sharma | 26 | 49000 | HDFC |
| 5 | Deepak Kohli | 28 | 65000 | SBI |
The output of the SELECT query shows that all the records are inserted successfully.
BEGIN / START TRANSACTION command is used to start the transaction.
- mysql> START TRANSACTION;
As we know, the SAVEPOINT command in SQL is used to save the different parts of the same transaction using different names. Consider till this point as one part of our transaction. We will save this part using a savepoint named Insertion.
- mysql> SAVEPOINT Insertion;
We will execute the delete command on the customer table to remove the record with ID 5.
- mysql> DELETE FROM customer WHERE Customer_ID = 5;
We will execute the SELECT query to verify that the record with ID 5 has been removed.
- mysql> SELECT *FROM customer;
| Customer_ID | Name | Age | Salary | Salary_BankAccount |
|---|---|---|---|---|
| 1 | Aryan Jain | 51 | 56000 | SBI |
| 2 | Arohi Dixit | 21 | 25000 | Axis |
| 3 | Vineet Garg | 24 | 31000 | ICICI |
| 4 | Anuja Sharma | 26 | 49000 | HDFC |
The output of the SELECT query shows that the record with ID 5 is removed successfully.
Consider the delete operation as one part of our transaction. We will save this part using a savepoint named Deletion.
- mysql> SAVEPOINT Deletion;
Suddenly, our requirement changed, and we realized that we had deleted a record that was not supposed to be. In such a scenario, we need to roll back our transaction to the savepoint, which was created prior to the execution of the DELETE command.
- mysql> ROLLBACK TO Insertion;
We didn't need the deletion carried on the record. Hence, we have rolled back to the savepoint named Insertion.
For confirming that we have got the same customer table that we had before carrying out the deletion operation, we will again execute the SELECT query.
- mysql> SELECT *FROM customer;
| Customer_ID | Name | Age | Salary | Salary_BankAccount |
|---|---|---|---|---|
| 1 | Aryan Jain | 51 | 56000 | SBI |
| 2 | Arohi Dixit | 21 | 25000 | Axis |
| 3 | Vineet Garg | 24 | 31000 | ICICI |
| 4 | Anuja Sharma | 26 | 49000 | HDFC |
| 5 | Deepak Kohli | 28 | 65000 | SBI |
The SELECT query output confirms that the transaction is now successfully rolled back to the savepoint 'Insertion'.