TCL Commands in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
Topics Covered

Overview

TCL stands for Transaction Control Language in SQL. Transaction Control Language (TCL) is a set of special commands that deal with the transactions within the database. Basically, they are used to manage transactions within the database. TCL commands are also used for maintaining the consistency of the database.

We formally defined TCL, but you must be wondering what transactions are? Or, what do we mean by maintaining consistency of the database? Let us look at each of them.

Introduction

A transaction is a unit of work that is performed against a database in SQL. In other words, a transaction is a single, indivisible database action. If the transaction contains multiple statements, it is called a multi-statement transaction (MST). By default, all transactions are multi-statement transactions.

For example, suppose we are creating a new record or updating or deleting any record from a table (in general, performing any changes on the table). In that case, we are performing a transaction on the table.

In SQL, each transaction begins with a particular set of task and ends only when all the tasks in the set is completed successfully. However, if any (or a single) task fails, the transaction is said to fail.

Transactions in SQL

What is Meant by Maintaining the Consistency of a Database?

As a part of database systems, consistency refers to ensuring that any particular database transaction makes changes only in ways that are permitted or allowed. There are a set of rules that must be met to ensure that the database is consistent. The rules might include different constraints, cascades, triggers, or anything else in general.

Before learning more about maintaining consistency in the databases, let us first look at the general terminologies used in transactions.

Types of TCL Commands with Examples

Examples of TCL commands

As we learned above, Transaction Control Language (TCL) commands are used to manage transactions in the database; let us see the types of TCL commands in SQL.

Quick Recap:

SQL commands are traditionally divided into four categories:

  1. Data Definition Language (DDL Commands in SQL)
  2. Data Manipulation Language (DML Commands in SQL)
  3. Data Control Language (DCL Commands in SQL)
  4. Transaction Control Language(TCS Command in SQL)

Types of TCL Commands

Before moving ahead with the article, it is strongly recommended that you learn about the DML commands in SQL as a pre-requisite. You can learn them from here on Scaler Topics -- Database Languages.

Transactional control commands (TCL commands) are used mostly in the DML Commands in SQL, such as INSERT, UPDATE, and DELETE.

In general, the TCL commands consist of the below commands:

1. Commit 2. RollBack 3. SavePoint

Transaction control languages

Let us learn about each of them in brief.

COMMIT Command in TCL

The COMMIT command in SQL is used to permanently save any transaction into the database. Generally, whenever we use any DML command such as INSERT, UPDATE, or DELETE, the changes made by these commands are , not permanent. Hence, before closing the current session, we may roll back any changes made through these commands.

Due to the above reason, it is mandatory to use the COMMIT command to mark any changes made as permanent.

Syntax:

Below given is the syntax of the COMMIT TCL command in SQL.

After writing our query, we write "COMMIT" to save and make permanent all the modifications that are done by our DML commands in the database.

Also, please note that once we have done a COMMIT, we cannot undo it. We can, however, rollback, but doing a rollback will undo the entire transaction. Anyway, we will learn about this in brief later in the article.

Major Highlights:

  • COMMIT save all the modifications done (all the transactions) by the DML commands in the database.
  • We must write the COMMIT command before and after every DDL command to save the change permanently in the database.
  • Once COMMIT is performed, it cannot be undone unless it is rolled back.

ROLLBACK Command in TCL

The rollback command in TCL is used for restoring the database to the last committed state. In other words, the rollback command restores the database to its original state since the last command that was committed.

For example, suppose we have used the UPDATE command at any point to make certain changes to our database and later realize that those changes need to be reverted(or undone), in that case, we can use the ROLLBACK command. The rollback command will basically revert or roll back any changes that were not committed during our transaction using the COMMIT command.

Syntax:

Below given is the syntax of the ROLLBACK TCL command in SQL.

After writing our query, we can write "ROLLBACK;" to roll back or undo a group of transactions since the last COMMIT.

Interestingly, the rollback command can also be used with the SAVEPOINT command to jump to a savepoint in any ongoing transaction. The savepoints are like checkpoints, they temporarily save a transaction up to where the transaction can be rolled back. We will learn more about the savepoints later in the article.

Syntax:

Below given is the syntax of the ROLLBACK command with savepoint :

To undo a group of transactions to a certain point we can use the above syntax: ROLLBACK TO savepoint_name;

Major Highlights:

  • ROLLBACK is used to undo the transactions that have not already been permanently saved (or committed) to the database.
  • The ROLLBACK command restores the previously stored value, that is, the data present before the execution of the transactions.

SAVEPOINT

The SAVEPOINT command in TCL is basically used to temporarily save a transaction so that we can roll back to that point (saved point) whenever required.

Syntax:

Below given is the syntax of the SAVEPOINT TCL command in SQL.

After writing our query, we can write "SAVEPOINT ;" followed by the savepoint_name savepoint name, to create a save point and save the transaction to that point.

Also, we can release a savepoint, meaning, remove any particular savepoint. The syntax to release a savepoint is given below :

Major Highlights:

  • SAVEPOINT is used to create a point within the groups of transactions to save or roll back later.
  • SAVEPOINT is highly beneficial when we are willing to roll the transactions back to a certain point without rolling back the whole group of transactions.

Examples of TCL Commands in SQL

We have briefly discussed all the TCL commands in SQL. Now, it's time for us to learn about them briefly with examples.

COMMIT TCL Command Example

As we already know, the COMMIT command is used in TCL to make any changes permanent to the database; let us see an example of using commit in our code.

For instance, suppose we want to change the country name for one of our tables, say "Customers". Sample Customers table is given below for your reference :

Commit TCL Command

Now, suppose we want to change the country name for a particular customer and then commit the transaction. For that, we will write the below piece of code:

Code:

Explanation:

After executing the above command, the update transaction is completed with the commit command as above. And through the above statements, the location of the customer "David" will be set to "INDIA", and this change will be saved in the database permanently.

Now, if we want to look at our Customer's table again, it will be displayed as below:

Code:

Output:

Commit TCL Command Example

Explanation:

The above output is our updated table after running the "update" query. You may observe that the country name for the customer with the first name "David" is changed to "India".

ROLLBACK TCL Command Example

As we learned above, the rollback command is generally used to undo any changes we have made till our last commit; let us now see an example to use the rollback command.

Suppose, in our customers' table, while performing some operations, we mistakenly updated the first name of a customer as "JOHN", whereas it should be "PETER". Hence, we can roll back our transaction to its original state. The code for the same is given below.

Before the Rollback we have the following table:

Rollback TCL Command

Code:

Once we perform the rollback, the below will be the output.

Output: Rollback TCL Command Example

We get the above output after the rollback is performed.

Example using Commit, Rollback, and Savepoint

Let us see an example where we make use of all of these 3 TCL commands together.

Suppose we have some Employee table as given below:

Employee

Emp IDName
110Bruce
111Mark
112Blair

Let us perform some SQL queries on the above table using the Commit, Rollback, and Savepoint commands and see the results --

Code:

Output:

Employee

Emp IDNames
110Oliver
111Mark
112Blair
113Rose
114Prince
115Smith

Explanation:

So, in the above example, we wrote a bunch of code, adding some values to our table and also creating some savepoints that will save our data till that point.

After having done this, now let us use the ROLLBACK command to roll back to the state of data to savepoint B. The code for the same will be:

Code:

Output:

Employee

Emp IDNames
110Oliver
111Mark
112Blair
113Rose
114Prince

Explanation:

So, you can see in the above output that, we got back to our data till savepoint B. Hence we rolled back till savepoint B. Because, after savepoint B we have inserted another value with emp ID as 115. But, since we have rolled back till savepoint B, the data with emp ID as 115 is not reflected.

After having done this, now let us use the ROLLBACK command to roll back to the state of data to the savepoint A. The code for the same will be:

Code:

Output:

Employee

Emp IDNames
110Oliver
111Mark
112Blair
113Rose

Explanation:

In the above example, we rolled back to our last savepoint, that is, A. You can refer to our initial code, where we specified the savepoint after entering each data into our table. Every time we are rolling back to any of our savepoints, we can see the table to be modified till that particular savepoint.

After this example, you might have hopefully understood the usage of commit, rollback, and savepoint commands in TCL.

Conclusion

  • Transaction Control Language (TCL) commands are used to manage transactions in the database. These are used to manage the changes made by DML statements.
  • A transaction is a single unit of work performed against a database. If it is successful, the data are committed and becomes permanent. But if the transaction fails, then all the data modifications are erased.
  • There are basically 3 TCL commands: Commit, Rollback, and Savepoint.
  • The COMMIT command is used to permanently save any transaction into the database.
  • The ROLLBACK command basically restores the database to the last committed state. It is also used along with the savepoint command to jump to a save point in a transaction.
  • The SAVEPOINT command is used to temporarily save a transaction so that we can roll back to the saved point whenever necessary.

See Also: