TCL Commands in SQL- Transaction Control Language Examples: 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. In this tutorial, you will learn different TCL Commands in SQL with examples and differences between them.

  1. Commit Command
  2. Rollback Command
  3. Savepoint Command
  4. Difference Between Commit, rollback and Savepoint tcl commands.

Also See: Explain Data Manipulation Language (DML) with Examples in SQL

TCL Commands in SQL- Transaction Control Language Examples

The modifications made by the DML commands are managed by using TCL commands. Additionally, it makes the statements grouped together into logical transactions.

TCL Commands

There are three commands that come under the TCL:

  1. 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. Here is the general syntax for the Commit command:

COMMIT;

For Example

UPDATE STUDENT SET STUDENT_NAME = ‘Maria’ WHERE STUDENT_NAME = ‘Meena’;

COMMIT;

By using the above set of instructions, you can update the wrong student name by the correct one and save it permanently in the database. The update transaction gets completed when commit is used. If commit is not used, then there will be lock on ‘Meena’ record till the rollback or commit is issued.

Now have a look at the below diagram where ‘Meena’ is updated and there is a lock on her record. The updated value is permanently saved in the database after the use of commit and lock is released.

   Also See: Explain Data Control Language (DCL) with Examples in SQL

  1. 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.

The general syntax for the Rollback command is mentioned below:

Rollback to savepoint-name;

For example

UPDATE STUDENT SET STUDENT_NAME = ‘Manish’ WHERE STUDENT_NAME = ‘Meena’; ROLLBACK;

This command is used when the user realizes that he/she has updated the wrong information after the student name and wants to undo this update. The users can issues ROLLBACK command and then undo the update. Have a look at the below tables to know better about the implementation of this command.

  1. 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.

The general syntax for the savepoint command is mentioned below:

savepoint savepoint-name;

For Example

Following is the table of a school class

Use some SQL queries on the above table and then watch the results

INSERT into CLASS VALUES (101, ‘Rahul);

Commit;

UPDATE CLASS SET NAME= ‘Tyler’ where id= 101

SAVEPOINT A;

INSERT INTO CLASS VALUES (102, ‘Zack’);

Savepoint B;

INSERT INTO CLASS VALUES (103, ‘Bruno’)

Savepoint C;

Select * from Class;

The result will look like

Now rollback to savepoint B

Rollback to B;

SELECT * from Class;

Now rollback to savepoint A

rollback to A;

SELECT * from class;

Difference between rollback, commit and savepoint tcl commands in SQL.

Rollback Commit Savepoint
1. Database can be restored to the last committed state Saves modification made by DML Commands and it permanently saves the transaction. It saves the transaction temporarily.
2. Syntax- ROLLBACK [To SAVEPOINT_NAME]; Syntax- COMMIT; Syntax- SAVEPOINT [savepoint_name;]
3. Example- ROLLBACK Insert3; Example- SQL> COMMIT; Example- SAVEPOINT table_create;

So It was all about TCL commands in SQL (transaction control language) with examples and differences between rollback, commit, and savepoint tcl commands.

TCL Commands in SQL- Transaction Control Language ExamplesSumit ThakurUncategorizedWhat Is DBMSTCL Commands in SQL- Transaction Control Language Examples: 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...Let's Define DBMS