Transaction Control Language

• TCL
• Introduction
• Types of TCL commands
• Commit
• Rollback
• Savepoint
• Example of TCL commands
 Transaction Control Language:

• 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.
• 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.
• Types of TCL Commands:
1) Commit

2) Rollback

3) Savepoint
1) Commit:
• 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.

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

• Syntax:
Commit;
2) Rollback:
• 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.

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

• The rollback command can also be used with the SAVEPOINT command to jump
to a savepoint in any ongoing transaction.
• 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.

• Syntax:

ROLLBACK;

• To undo a group of transactions to a certain point we can use the below

syntax:

ROLLBACK TO savepoint_name;
3) Savepoint:
• The SAVEPOINT command is used to temporarily save a transaction so that we
can roll back to the saved point whenever necessary.

• Syntax:

SAVEPOINT Savepoint_name;
• Example using COMMIT, ROLLBACK & 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:

• Let us perform some SQL queries on the above table using the Commit,
Rollback, and Savepoint commands and see the results --
• Code:
• INSERT INTO Employee VALUES(113, 'Rose');

• COMMIT;

• UPDATE Employee SET name = 'Oliver' WHERE id = '110';

• SAVEPOINT A;

• INSERT INTO Employee VALUES(114, 'Prince');

• SAVEPOINT B;

• INSERT INTO Employee VALUES(115, 'Smith');

• SAVEPOINT C;

• SELECT * FROM Employee;

• Output:

• 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.
• 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:
• ROLLBACK TO B;

• SELECT * FROM Employee;

• Output:

• 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.
• 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:
• ROLLBACK TO A;

• SELECT * FROM Employee;

• Output:
• 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.
 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.