Sunday, 11 January 2015

What is TCL? / List of operations that can be performed by Transaction Control Language / TCL examples / Different commands of TCL / What is Transaction Control Language? / Define TCL

Transaction Control Language (TCL)

The Transaction Control Language (TCL)
is yet another component of SQL which is used to control/manage transactions in
a database. The data stored in a database can be manipulated differently as and
when required. The manipulation could be smaller or larger, i.e, may involve
one or more SQL statements. Hence, we need a mechanism to differentiate one
manipulation from other. A transaction is a unit which is used to mention the
boundary of every manipulation.

A transaction is logical unit of work
that comprises one or more SQL statements, usually a group of Data Manipulation
Language (DML) statements.

The following list shows the major
operations that are performed as part of every transaction;

Insertion of
a record in a table/multiple tables

Modification of
the values in a table

Deletion of
records in a table/multiple tables

The each of the following examples is
considered as individual transactions;

When you deposit some money in your
account, your deposit will modify
the data in your record. This is treated as single transaction.

If you would like to withdraw some money
from your account from the bank, your withdrawal performs one modification in your record. In some
cases, the operation that you performed might by logged in another table for
maintaining a history of transaction. This involves an insertion. Here, both modification and insertion put together
considered as single transaction.

If you would like to transfer some money
from your account to your friend’s then your transfer involves modification in your record and your
friend’s record. Here, modification of values in both the records treated as
single transaction.

Hence, transactions are units or
sequences of work performed in a logical order.

The TCL commands include the following;

Commit – to permanently save the changes
that are executed as part of a transaction in a database.

The syntax for using commit;

COMMIT;

Rollback – to undo the changes that are
made on a database through a transaction.

The syntax for using rollback;

ROLLBACK;

Savepoint - A SAVEPOINT is a point in a
transaction that you can use to roll the transaction back to a certain point
without rolling back the entire transaction.

The syntax for SAVEPOINT is;

SAVEPOINT Savepoint_Name;

If you have declared a savepoint as part
of your transaction, then you can use rollback command to rollback the
transaction upto that point. For example,

ROLLBACK TO svpoint1;

This command will rollback the
transaction by undoing changes upto the savepoint svpoint.