Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is TCL?
3.
What does Transaction in SQL mean?
3.1.
Features of Transaction
4.
What are TCL Commands in SQL
5.
Types of TCL Commands in SQL
5.1.
COMMIT
5.2.
ROLLBACK
5.3.
SAVEPOINT
6.
Examples of TCL Commands in SQL
6.1.
Online Banking System
6.2.
E-commerce Website
7.
Advantages of TCL Commands in SQL
8.
Disadvantages of TCL Commands in SQL
9.
Frequently Asked Questions
9.1.
What is TCL in SQL example? 
9.2.
What is TCL command used for?
9.3.
What are the common TCL commands?
9.4.
What are the 5 commands of SQL?
10.
Conclusion
Last Updated: Mar 30, 2024
Easy

TCL Commands in SQL

Author Komal
0 upvote
gp-icon
DBMS - Database management systems
Free guided path
12 chapters
93+ problems
gp-badge
Earn badges and level up

Introduction

Welcome, Ninjas! In this blog, we will look into the meaning of Transactions in SQL. Further, we will discuss the Transaction Control Language followed by various tcl commands in SQL. There will be specific examples of each of the TCL commands in SQL. 

tcl commands in sql

Let us get started.

What is TCL?

TCL stands for Transaction Control Language. As the name suggests, Transaction control language controls and manages the Transactions. Transactions execute a series of commands, considering them a single unit of work. The tcl maintains the integrity of Databases, i.e., if any command in the transaction fails, the whole piece of work fails. 

In the next section of the blog, we will be looking into the meaning of Transactions in SQL in detail.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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;
output

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;
error output

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)

output

 

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

output

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)

output

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

output

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

output

Table after the final commit.

output

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:

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!

Guided path
Free
gridgp-icon
DBMS - Database management systems
12 chapters
93+ Problems
gp-badge
Earn badges and level up
Live masterclass