TCL Commands in SQL

What does Transaction in SQL mean?

In a Database Management System (DBMS), a transaction is a unit of work that represents a single logical operation or a sequence of operations on a database. Transactions are used to ensure data integrity and consistency in a multi-user environment where multiple users or applications can access and modify the database concurrently.

Features of Transaction

The features of Transaction are described as ACID Properties.

• Atomic: It ensures that all statements combined in a transaction are executed as a single unit of work.
   
• Consistent: It ensures that the database's integrity is in a consistent state and remains the same before and after the transaction.
   
• Isolation: Concurrent transactions occur simultaneously without being affected by each other. One transaction cannot affect another.
   
• Durability: The changes after the execution of a transaction become permanent. Alternatively, the effects of the transaction are permanent. 

What are TCL Commands in SQL

In SQL, TCL (Transaction Control Language) commands are used to manage transactions within a database. These commands are responsible for controlling the beginning and ending of transactions, as well as for ensuring data integrity and consistency. In the next section, we will look at the different types of TCL commands in SQL.

Types of TCL Commands in SQL

There are three commands in TCL - Commit, Rollback, and Savepoint. TCL commands are important for maintaining ACID properties. These commands allow you to commit or discard changes, manage savepoints, and control the overall flow of data modifications. Let's take a look at all of them.

COMMIT

When we make changes in the database using the DML commands, it's important to save them. The command which saves the changes is one of the tcl commands in sql, the COMMIT command. Once we commit in the database, we cannot change it back to the previous state (we cannot ROLLBACK).

Syntax: COMMIT;

Example:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);

-- start the transaction
BEGIN TRANSACTION;
INSERT INTO STUDENT VALUES(6,'Komal',20,'Faridabad',97);
COMMIT;
SELECT* FROM STUDENT;

Let’s have a look at an example where we try to ROLLBACK after COMMIT is issued. It shows an error. The error is because once we commit a transaction, it cannot be rolled back if there’s no save point in between the COMMIT & ROLLBACK.

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);

-- start the transaction
BEGIN TRANSACTION;
INSERT INTO STUDENT VALUES(6,'Komal',20,'Faridabad',97);
SAVEPOINT save_1;
COMMIT;
SELECT* FROM STUDENT;
ROLLBACK to save_1;
SELECT* FROM STUDENT;

ROLLBACK

What if we want to undo the changes made by the DML commands? In this scenario, the ROLLBACK command comes handy. This command undoes the changes made by DML statements. Only those changes would be ‘undo’ed before the last COMMIT was issued. 

Syntax: ROLLBACK to savepnt_name; 

Example:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);

-- start the transaction
BEGIN TRANSACTION;

INSERT INTO STUDENT VALUES(6,'Komal',20,'Faridabad',97);
--creating savepoint 1
SAVEPOINT save1;

INSERT INTO STUDENT VALUES(7,'Preeti',19,'UP',91);
--creating savepoint 2
SAVEPOINT save2;
UPDATE STUDENT SET MARKS=95 WHERE NAME='Komal';
--creating savepoint 3
SAVEPOINT save3;

--checking the database before performing rollback
SELECT* FROM STUDENT;

ROLLBACK to save1;

--After rollback
SELECT* FROM STUDENT;

Result before ROLLBACK (the changes made by save1, save2, and save3 are displayed)

Result after ROLLBACK(the changes made by save1 are there as we have undo-ed all the changes done by save2 & save3)

SAVEPOINT

Let's consider a scenario. We are executing the commands in a transaction. Let's say we have a total of 6 commands in that transaction. Now, we have executed till the 4th command without any error, but we are not sure if the 5th or 6th command will work correctly, and if they fail, the whole piece of work will fail as it is yet to be committed. In this case, we issue a SAVEPOINT till the 4th point so that if the further commands fail, no ROLLBACK would be possible beyond the 4th command. SAVEPOINT command creates a savepoint within a transaction that allows partial rollbacks. A savepoint is a point we can roll back to without undoing all the changes made since the start of the transaction.

Syntax: SAVEPOINT savepnt_name;  

Example:

CREATE TABLE STUDENT(RNO NUMBER(10),NAME CHAR(20),AGE NUMBER(2),CITY CHAR(10),MARKS NUMBER(3));
INSERT INTO STUDENT VALUES(1, 'Sasha',17,'Faridabad',80);
INSERT INTO STUDENT VALUES(2,'John',18,'Agra',91);
INSERT INTO STUDENT VALUES(3,'Sara',17,'Hisar',86);
INSERT INTO STUDENT VALUES(4,'Rohan',16,'Faridabad',79);
INSERT INTO STUDENT VALUES(5,'Virat',18,'Delhi',80);

-- start the transaction
BEGIN TRANSACTION;

INSERT INTO STUDENT VALUES(6,'Komal',20,'Faridabad',97);
--creating savepoint 1
SAVEPOINT save1;
--displaying changes after savepoint1
SELECT* FROM STUDENT;

INSERT INTO STUDENT VALUES(7,'Preeti',19,'UP',91);
--creating savepoint 2
SAVEPOINT save2;
--displaying changes after savepoint2
SELECT* FROM STUDENT;

UPDATE STUDENT SET MARKS=95 WHERE NAME='Komal';
--creating savepoint 3
SAVEPOINT save3;
--displaying changes after savepoint3
SELECT* FROM STUDENT;

COMMIT;
--displaying the changes
SELECT* FROM STUDENT;

Table after savepoint 1 (save1) - Details of another student added(RNO=6)

Table after savepoint 2 (save2) - Details of another student added(RNO=7)

Table after savepoint 3 (save3) - Marks of student with NAME=’Komal’ updated.

Table after the final commit.

Also read, Natural Join in SQL

Examples of TCL Commands in SQL

Let us look at some of the examples of writing TCL commands in SQL.

Online Banking System

In this example, we will look into the online banking system and use TCL commands to transfer money from one bank to another.

BEGIN TRANSACTION;
-- Deduct amount from Account A
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountNumber = 'A';
-- Credit amount to Account B
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountNumber = 'B';
-- Commit the transaction if no errors occurred
COMMIT;
-- Rollback the transaction if any errors occurred
ROLLBACK;

E-commerce Website

In this example, we will use TCL commands to update the inventory and order history of an E-commerce website.

BEGIN TRANSACTION;
-- Deduct quantity from Inventory
UPDATE Inventory SET Quantity = Quantity - 1 WHERE ProductID = '123';
-- Update Order History
INSERT INTO OrderHistory (CustomerID, ProductID, Quantity) VALUES ('456', '123', 1);
-- Commit the transaction if no errors occurred
COMMIT;
-- Rollback the transaction if any errors occurred
ROLLBACK;

Advantages of TCL Commands in SQL

The following are some advantages of TCL commands in SQL:-

• Data Integrity and Consistency: TCL commands, such as COMMIT and ROLLBACK, helps you maintain data integrity and consistency by allowing you to ensure that changes to the database are correctly applied.
• Atomicity: The COMMIT and ROLLBACK commands ensure the atomicity of transactions. A transaction is treated as a single, indivisible unit of work.
• Selective Undo: Savepoints and the ROLLBACK TO SAVEPOINT command provides the advantage of selectively undoing specific parts of a transaction without rolling back the entire transaction.
• Concurrency Control: TCL commands help manage concurrency and isolation between multiple transactions.
• Recovery and Fault Tolerance: TCL commands contribute to the fault tolerance of the database system. In the event of system failures or crashes, the COMMIT and ROLLBACK commands ensure that the database can be restored to a consistent state.

Disadvantages of TCL Commands in SQL

• No Control: Transaction Control Language (TCL) commands lack the ability to control the transactions, they can only manage them.
• Limited Scope: TCL commands can only manage transactions within a single session and not across multiple sessions.
• Performance Impact: Frequent use of TCL commands, like ROLLBACK or COMMIT, can impact database performance.
• No Partial Commit: TCL doesn’t support partial commit. If a transaction is started, it has to be either fully committed or rolled back.
• Complexity: Using TCL commands can complicate the code, especially in large database systems.

Frequently Asked Questions

What is TCL in SQL example? 

TCL stands for Transaction control language. It is a set of commands that allows you to manage transactions in your database. Some examples of TCL commands are ROLLBACK, COMMIT, and SAVEPOINT.

What is TCL command used for?

TCL commands in SQL are used for defining transactions and maintaining ACID properties in your database. They allow you to commit or discard changes, manage savepoints, and control the overall flow of data modifications.

What are the common TCL commands?

Some commonly used TCL commands in SQL are COMMIT, ROLLBACK, SAVEPOINT, ROLLBACK TO SAVEPOINT, RELEASE SAVEPOINT. These commands help you maintain consistency and data integrity in your databases. 

What are the 5 commands of SQL?

DDL or Data Definition Language, DQL or Data Query Language, DML or Data Manipulation Language, DCL or Data Control Language and TCL or Transaction Control Language are 5 commands of SQL.

Conclusion

In this blog, we discussed the Transactions and the Transaction Control Language. We looked into various types of TCL commands in SQL and their examples.

If you found this blog interesting and insightful, refer to similar blogs:

• SQL Introduction
   
• DCL Commands in SQL 
   
• Transactions & ACID Properties 
   
• Update Query in MySQL
   
• gzip command in linux
   

Check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job. Give your career an edge over others by considering our premium courses!

Happy Learning!