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.