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.
Applications of TCL:-
- Committing Transactions: TCL statements can be used to commit a transaction, which means to permanently save the changes made during the transaction to the database.
- Rolling Back Transactions: TCL statements can be used to roll back a transaction, which means to undo the changes made during the transaction and restore the database to its previous state.
- Setting Transaction Isolation Levels: TCL statements can be used to set the transaction isolation level, which determines the level of concurrency and consistency in the database.
- Savepoints: TCL statements can be used to set savepoints within a transaction, allowing for partial rollback if needed.
- Managing Transactions in Stored Procedures: TCL statements can be used in stored procedures to manage transactions within the scope of the procedure.
Overall, TCL is an essential part of SQL and is used extensively in database management systems to control transactions and ensure data consistency. By using TCL statements, database administrators and developers can manage transactions effectively and maintain the integrity of their databases.
The TCL commands are:
- COMMIT
- ROLLBACK
- SAVEPOINT
1. COMMIT :
This command is used to save the data permanently.
Whenever we perform any of the DML 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 committed 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 committed state.
Syntax:
rollback;
3. SAVEPOINT :
This command is used to save the data at a particular point temporarily, 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.