ORACLE SQL PLSQL TCL commands training institutes in Kukatpally Hyderabad

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:

  1. COMMIT;

Example:

We will select an existing database, i.e., school.

  1. mysql> USE school;

TCL Commands in SQL

To create a table named t_school, we will execute the following query:

  1. 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));

TCL Commands in SQL

BEGIN / START TRANSACTION command is used to start the transaction.

  1. mysql> START TRANSACTION;

TCL Commands in SQL

Now, we will execute the following query to insert multiple records at the same time in the t_school table.

  1. 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");

TCL Commands in SQL

We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.

  1. 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.

  1. mysql> COMMIT;

TCL Commands in SQL

Autocommit is by default enabled in MySQL. To turn it off, we will set the value of autocommit as 0.

  1. mysql> SET autocommit = 0;

TCL Commands in SQL

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:

  1. 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:

  1. ROLLBACK TO savepoint_name;

Examples to understand the SAVEPOINT and ROLLBACK commands:

Example 1:

We will select an existing database, i.e., school.

  1. mysql> USE school;

TCL Commands in SQL

To create a table named t_school, we will execute the following query:

  1. 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));

TCL Commands in SQL

Now, we will execute the following query to insert multiple records at the same time in the t_school table.

  1. 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");

TCL Commands in SQL

We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.

  1. 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.

  1. mysql> START TRANSACTION;

TCL Commands in SQL

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.

  1. mysql> SAVEPOINT Insertion;

TCL Commands in SQL

Advertisement

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.

  1. mysql> UPDATE t_school SET Number_Of_Students = 9050 WHERE ID = 5;

TCL Commands in SQL

To verify that the record with ID 5 now has the Number_Of_Students as 9050, we will execute the SELECT query.

  1. 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.

  1. mysql> SAVEPOINT Updation;

TCL Commands in SQL

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.

  1. mysql> ROLLBACK TO Insertion;

TCL Commands in SQL

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.

  1. 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.

  1. mysql> USE bank;

TCL Commands in SQL

To create a table named customer, we will execute the following query:

  1. mysql> CREATE TABLE customer(Customer_ID INT PRIMARY KEYName VARCHAR(20), Age INT, Salary INT, Salary_BankAccount VARCHAR(20));

TCL Commands in SQL

Now, we will execute the following query to insert multiple records at the same time in the customer table.

  1. 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");

TCL Commands in SQL

We will now execute the SELECT query to verify the execution of the INSERT INTO query executed above.

  1. 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.

  1. mysql> START TRANSACTION;

TCL Commands in SQL

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.

  1. mysql> SAVEPOINT Insertion;

TCL Commands in SQL

We will execute the delete command on the customer table to remove the record with ID 5.

  1. mysql> DELETE FROM customer WHERE Customer_ID = 5;

TCL Commands in SQL

We will execute the SELECT query to verify that the record with ID 5 has been removed.

  1. 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.

  1. mysql> SAVEPOINT Deletion;

TCL Commands in SQL

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.

  1. 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.

  1. 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'.