Home » SQL

Transaction Control Language (TCL) commands in SQL

Learn: What are the Transaction Control Language (TCL) Commands in SQL? Here, we are going to learn about COMMIT, ROLLBACK, SAVEPOINT commands in SQL.
Submitted by Preeti Jain, on March 30, 2018

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

1. COMMIT
2. ROLLBACK
3. SAVEPOINT

We have a table named "emp"

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

1) COMMIT

• COMMIT is a transactional command.
• It saves all transaction to the database since the last COMMIT or ROLLBACK.

Syntax:

    COMMIT;

2) ROLLBACK

• ROLLBACK is a transactional command.
• It undo the transaction that have not been modified 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 transactional command.
• It rollback the transaction from a certain SAVEPOINT without rollback the entire transaction.

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