TCL Commands in SQL

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.

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

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)

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

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 :

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:

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:

Code:

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

Output:

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

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

Code:

Output:

Employee

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

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

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.