TCL Full Form

TCL stands for Transaction Control Languages. These commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands.

A Transaction is a set of SQL statements that are executed on the data stored in DBMS. Whenever any transaction is made these transactions are temporarily happen in database.So to make the changes permanent, we use TCL commands.

The TCL commands are:

  1. COMMIT
  2. ROLLBACK
  3. SAVEPOINT

1. COMMIT :
This command is used to save the data permanently.
Whenever we perform any of the DDL command like -INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So in order to be at the safer side COMMIT command is used.

Syntax:



commit; 

2. ROLLBACK :
This command is used to get the data or restore the data to the last savepoint or last commited state. If due to some reasons the data inserted, deleted or updated is not correct, you can rollback the data to a particular savepoint or if savepoint is not done, then to the last commited state.

Syntax:

rollback;

3. SAVEPOINT :
This command is used to save the data at a particular point tempoararily, so that whenever needed can be rollback to that particular point.

Syntax:

Savepoint A; 

Consider the following Table Student:

Name Marks
John 79
Jolly 65
Shuzan 70

UPDATE STUDENT 
SET NAME = ‘Sherlock’ 
WHERE NAME = ‘Jolly’;

COMMIT;
ROLLBACK; 

By using this command you can update the record and save it permanently by using COMMIT command.



Now after COMMIT :

Name Marks
John 79
Sherlock 65
Shuzan 70

If commit was not performed then the changes made by the update command can be rollback.

Now if no COMMIT is performed.

UPDATE STUDENT 
SET NAME = ‘Sherlock’ 
WHERE STUDENT_NAME = ‘Jolly’; 

After update command the table will be:

Name Marks
John 79
Sherlock 65
Shuzan 70

Now if ROLLBACK is performed on the above table:

rollback; 

After Rollback:



Name Marks
John 79
Jolly 65
Shuzan 70

If on the above table savepoint is performed:

INSERT into STUDENT 
VALUES ('Jack', 95);

Commit;

UPDATE NAME 
SET NAME= ‘Rossie’ 
WHERE marks= 70;

SAVEPOINT A;

INSERT INTO STUDENT 
VALUES (‘Zack’, 76);

Savepoint B;

INSERT INTO STUDENT 
VALUES (‘Bruno’, 85);

Savepoint C;

SELECT * 
FROM STUDENT; 

Name Marks
John 79
Jolly 65
Rossie 70
Jack 95
Zack 76
Bruno 85

Now if we Rollback to Savepoint B:

Rollback to B; 

The resulting Table will be-

Name Marks
John 79
Jolly 65
Rossie 70
Jack 95
Zack 76

Now if we Rollback to Savepoint A:

Rollback to A; 

The resulting Table will be-

Name Marks
John 79
Jolly 65
Rossie 70
Jack 95

So It was all about TCL commands in SQL (transaction control language) with examples.

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.