Transaction Control Language (TCL)

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.