Transaction Control Language

• Transaction Control Language can be defined as the portion of a database language used for maintaining consistency of the database and managing transactions in the database.

• A set of SQL statements that are co-related logically and executed on the data stored in the table is known as a transaction.

TCL Commands

• COMMIT Command
• ROLLBACK Command
• SAVEPOINT Command

COMMIT

The main use of COMMIT command is to make the transaction permanent. If there is a need for any transaction to be done in the database that transaction permanent through commit command.

Syntax

COMMIT;

ROLLBACK

Using this command, the database can be restored to the last committed state. Additionally, it is also used with savepoint command for jumping to a savepoint in a transaction.

Syntax

ROLLBACK TO savepoint-name;

SAVEPOINT

The main use of the Savepoint command is to save a transaction temporarily. This way users can rollback to the point whenever it is needed.

Syntax

SAVEPOINT savepoint-name;

Examples

This is purchase table that we are going to use through this tutorial

item	price	customer_name
Pen	10	Sanskriti
Bag	1000	Sanskriti
Vegetables	500	Sanskriti
Shoes	5000	Sanskriti
Water Bottle	800	XYZ
Mouse	120	ABC
Sun Glasses	1350	ABC

UPDATE purchase SET price = 20 WHERE item = "Pen";

O/P : Query OK, 1 row affected (3.02 sec) (Update the price of Pen set it from 10 to 20)

SELECT * FROM purchase;

O/P

item	price	customer_name
Pen	20	Sanskriti
Bag	1000	Sanskriti
Vegetables	500	Sanskriti
Shoes	5000	Sanskriti
Water Bottle	800	XYZ
Mouse	120	ABC
Sun Glasses	1350	ABC

START TRANSACTION;

Start transaction

COMMIT;

Saved/ Confirmed the transactions till this point

ROLLBACK;

Lets consider we tried to rollback above transaction

SELECT * FROM purchase;

O/P:

item	price	customer_name
Pen	20	Sanskriti
Bag	1000	Sanskriti
Vegetables	500	Sanskriti
Shoes	5000	Sanskriti
Water Bottle	800	XYZ
Mouse	120	ABC
Sun Glasses	1350	ABC
##### As we have committed the transactions the rollback will not affect anything

SAVEPOINT  sv_update;

Create the savepoint the transactions above this will not be rollbacked

UPDATE purchase SET price = 30 WHERE item = "Pen";

O/P : Query OK, 1 row affected (0.57 sec)

Rows matched: 1 Changed: 1 Warnings: 0

SELECT * FROM purchase;

item	price	customer_name
Pen	30	Sanskriti
Bag	1000	Sanskriti
Vegetables	500	Sanskriti
Shoes	5000	Sanskriti
Water Bottle	800	XYZ
Mouse	120	ABC
Sun Glasses	1350	ABC
##### price of pen is changed to 30 using the update command

ROLLBACK to sv_update;

Now if we rollback to the savepoint price should be 20 after rollback lets see

SELECT * FROM purchase;

item	price	customer_name
Pen	20	Sanskriti
Bag	1000	Sanskriti
Vegetables	500	Sanskriti
Shoes	5000	Sanskriti
Water Bottle	800	XYZ
Mouse	120	ABC
Sun Glasses	1350	ABC
Torch	850	ABC
##### As expected we can see update query is rollbacked to sv_update.

Conclusion

With this short tutorial we have learnt TCL commands.