A transaction is a collection of statements between specific client and server.These transactions can be controlled efficiently by using SQL Transaction Control Language statements. SQL Transaction Control Language 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 rollback or exit or directly closing the windows applications.

If commit command is executed then all the work done in the last transaction will be made permanent in the server.

By viewing the below example, the concept of commit command can be easily understood. [c] sql> select * from employee; +--------+----------+-------+ | emp_id | emp_name |salary | +--------+----------+-------+ | 1001 | mike | 12000 | | 1002 | maze | 13000 | | 1003 | jack | 14000 | +--------+----------+-------+ 3 rows in set (0.00 sec) sql> commit;[/c] 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 database.

If roll-back command is performed then oracle server will cancel all the modifications of SQL statements in the last transactions.

By viewing the below example, the concept of rollback command can be easily understood. [c]sql> 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> update employee set sal=sal+1000 where emp_id=1001; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 sql> select * from employee; +--------+----------+-------+ | emp_id | emp_name |salary | +--------+----------+-------+ | 1001 | mad | 16500 | | 1002 | maddi | 16000 | | 1003 | maddie | 16000 | +--------+----------+-------+ 3 rows in set (0.00 sec) sql> rollback; Query OK, 0 rows affected (0.39 sec) sql> select * from employee; +--------+----------+-------+ | emp_id | emp_name |salary | +--------+----------+-------+ | 1001 | mad | 15500 | | 1002 | maddi | 16000 | | 1003 | maddie | 16000 | +--------+----------+-------+ 3 rows in set (0.00 sec)[/c] 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 are performed, roll back operation will be performed on a part of transaction. The save points commands sets a name transaction save-point by the  name of identifier. On the off chance present transaction has a save point with the similar name, then the old save point is deleted and a new one is assigned.

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

• SQL Transaction Control Language - Is a collection of statements between specific client and server.
• Commit command - Will store the data permanently in the data base.
• Rollback - Will cancel all modifications.
• Save points - Will rollback the transaction.