C C++ Java Data Structure Python JavaScript CSS Ajax PL/SQL PHP Puzzles C programs C++ programs Java programs



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






You may also be interested in...

C/C++ Tips and Tricks...

Was this page helpful? YES NO

Are you a blogger? Join our Blogging forum.


Comments and Discussions