Transaction Control Language (TCL) commands in SQL

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

DURGESH

I Love python, so I like machine learning a Lot and on the other hand, I like building apps and fun games I post blogs on my website for Tech enthusiast to learn and Share Information With The World.

Leave a Reply

Your email address will not be published. Required fields are marked *

one × 3 =

Recent Content