A Transaction is a collection of statements between specific client and server. These transactions can be controlled efficiently by using MySQL TCL (Transaction Control Language). Transaction Control Statements are Whenever a client gets a connection with the server, a new transaction will be started, and the transaction ends by executing commit or roll-back or exit or directly closing the windows applications.

If commit command of MySQL TCL Commands, is executed then all the work done in the last transaction will be made permanent in the server.

commit => Is a type of command which used to stores the previous data.

By viewing the below example, the concept of commit command can be easily understood. [sql] mysql> select * from employee; +--------+----------+-------+ | emp_id | emp_name | salary| +--------+----------+-------+ | 1001 | mike | 12000 | | 1002 | maze | 13000 | | 1003 | jack | 14000 | +--------+----------+-------+ 3 rows in set (0.00 sec) mysql> commit; [/sql] In the above example, after creating a table and inserting all the values inside the table, then enter commit command. It will permanently store the table values inside the Databases.

If roll-back command MySQL TCL Commands, is performed then Oracle Server will cancel all the modifications of MySQL statements in the last transactions.

Roll-back => Is a type of command which used to restores the previous deleted data or a tables.

By viewing the below example, the concept of rollback command can be easily understood. [sql]mysql> select * from employee; +--------+----------+-------+ | emp_id | emp_name | salary| +--------+----------+-------+ | 1001 | mad | 15500 | | 1002 | maddi | 16000 | | 1003 | maddie | 16000 | +--------+----------+-------+ 3 rows in set (0.00 sec) mysql> update employee set salary=salary+1000 where emp_id=1001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee; +--------+----------+-------+ | emp_id | emp_name |salary | +--------+----------+-------+ | 1001 | mad | 16500 | | 1002 | maddi | 16000 | | 1003 | maddie | 16000 | +--------+----------+-------+ 3 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.39 sec) mysql> select * from employee; +--------+----------+-------+ | emp_id | emp_name | salary| +--------+----------+-------+ | 1001 | mad | 15500 | | 1002 | maddi | 16000 | | 1003 | maddie | 16000 | +--------+----------+-------+ 3 rows in set (0.00 sec) [/sql] In the above example, the roll-back command will cancel all the modifications(the salary of mad will be retained from 16500 to 15500).

If Save Points commands MySQL TCL Commands are performed, rollback operation will be performed on a part of transaction. The save points commands sets a name transaction save-point by the name of identifier. At the same time the present transaction has a save point with the similar name, then the old save point is deleted and a new one is assigned.

save points => Is a command to roll-back the transaction.

By viewing the below example, the concept of save points command can be easily understood. [sql] mysql> CREATE TABLE test(test_id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; Query OK, 0 rows affected (0.39 sec) mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO TEST VALUES(1); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM TEST; +---------+ | test_id | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> SAVEPOINT TRAN2; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO TEST VALUES(2); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM TEST; +---------+ | test_id | +---------+ | 1 | | 2 | +---------+ 2 rows in set (0.00 sec) mysql> ROLLBACK TO TRAN2; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM TEST; +---------+ | test_id | +---------+ | 1 | +---------+ 1 row in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.09 sec) mysql> SELECT * FROM TEST; Empty set (0.00 sec) [/sql] In the above example, the roll back command will rollback the save points command and no data can seen. i.e, when performed roll-back on tran_id-2, the tran_id- 2 will be removed from table, and again roll-back command is performed on tran_id-1, then tran_id-1 will be removed from the table.

• MySQL TCL is a collection of statements between specific client and server.
• Commit command will store data permanently in the data base.
• Roll-back will cancel all modifications.
• Save points will roll back the transaction.