Skip to content

Latest commit

 

History

History
154 lines (121 loc) · 4.23 KB

020-TCL-commands.md

File metadata and controls

154 lines (121 loc) · 4.23 KB

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.