Transaction Control Language (TCL) commands in SQL

Realize: What are the Transaction Control Language (TCL) Commands in SQL? Here, we will find out about COMMIT, ROLLBACK, SAVEPOINT orders in SQL.

There are 3 Transaction Control Language (TCL) order in SQL,

• Submit
• ROLLBACK
• SAVEPOINT

We have a table named “emp”

    Id	Full_name
    3	Anny sharma
    4	Ayesha jain
    5	Preeti jain
    6	Rahul jain

1) COMMIT

• Submit is a value-based order.
• It spares all exchange to the database since the last COMMIT or ROLLBACK.

    COMMIT;

2) ROLLBACK

• ROLLBACK is a value-based order.
• It fix the exchange that have not been adjusted in the database.

Syntax:

    ROLLBACK;

Example:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update emp set full_name = 'Tanya jain' where id = 3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from emp;

Output

    Id	Full_name
    3	Tanya jain
    4	Ayesha jain
    5	Preeti jain
    6	Rahul jain

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from emp;

Output

    Id	Full_name
    3	Anny sharma
    4	Ayesha jain
    5	Preeti jain
    6	Rahul jain

3) SAVEPOINT

• SAVEPOINT is a value-based order.
• It rollback the exchange from a certain SAVEPOINT without rollback the whole exchange.

Syntax1:

 SAVEPOINT SAVEPOINT_NAME;

Syntax2:

 ROLLBACK TO SAVEPOINT_NAME;

Example:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> savepoint sp;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into emp values(9,'raj jain');
Query OK, 1 row affected (0.00 sec)

mysql> insert into emp values(10,'arpit jain');
Query OK, 1 row affected (0.00 sec)

mysql> select * from emp;

Output

    Id	Full_name
    3	Anny sharma
    4	Ayesha jain
    5	Preeti jain
    6	Rahul jain
    9	Raj jain
    10	Arpit jain

mysql> rollback to savepoint sp;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from emp;

Output

    Id	Full_name
    3	Anny Sharma
    4	Ayesha jain
    5	Preeti jain
    6	Rahul jain