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





Was this page helpful? Please share with your friends...

Are you a blogger? Join our Blogging forum.




Comments and Discussions





Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing » Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com (2015-2018), Some rights reserved.




close Like other websites, this site uses cookies to deliver relevant ads based on your interest, by using our website, you acknowledge that you have read our privacy policy.