Transaction Control Language (TCL)

TRANSACTION CONTROL LANGUAGE
------------------------------------------

TRANSACTION:-
Collection of operation that forms a single logical unit of work are called Transactions.
In other words, Transactions are units or sequences of work accomplished in logical order, whether in a manual fashion by a user or automatically by some sort of a database program. In a relational database using SQL, transactions are accomplished using the DML commands, which are already discussed.
A transaction can either be one DML statement or a group of statements. When managing groups of transactions, each designated group of transactions must be successful as one entity or none of them will be successful.

The Following list describes the nature of transactions:
->All transactions have a begining and an end.
->A transaction can be saved or undone.
->If a transaction fails in the middle, no part of the transaction can be saved to the database.

TRANSACTIONAL CONTROL
Transactional control is the ability to manage various transactions that may occur within a relational database management system. (Note keep in mind that transaction is group of DML statements).
When a transaction is executed and completes successfully, the target table is not immediately changed, although it may appear so according to the output. When a transaction successfully completes, there are transactional control commands that are used to finalize the transaction.
There are three commands used to control transactions:
1) COMMIT
2) ROLLBACK
3) SAVEPOINT
When transaction has completed, it is not actually taken changes on database, the changes reflects are temporary and are discarded or saved by issuing transaction control commands. Explanatory figure is drawn as under.



TRANSACTIONAL CONTROL COMMANDS
1) COMMIT
->The commit command saves all transactions to the database since the last COMMIT or ROLLBACK command.
Syntax
commit [work];
The keyword commit is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.
example
SQL>delete from emp
where emp_age > 75;

->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To allow changes permanently on database commit command is used.
SQL> COMMIT WORK;

->The above command will made changes permanently on database, since last commit or rollback command was issued.

NOTE: here work is totally optional, it is just to make command more user friendly.

2) ROLLBACK
->The rollback command is the transactional control command used to undo transactions that have not already been saved to the database. The rollback command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Syntax
SQL>rollback [work];
The keyword rollback is the only mandatory part of the syntax. Keyword work is optional; its only purpose is to make the command more user-friendly.
example
SQL>delete from emp
where emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area. To discards changes made on database rollback command is used.
SQL> ROLLBACK WORK;
->The above command will discards changes made on database,since last commit or rollback command was issued.
note here work is totally optional, it is just to make command more user friendly.

3) SAVEPOINT
->A savepoint is a point in a transaction that you can roll the transaction back to without rolling back the entire transaction.
->Practical example
consider that a person walking and after passing some distance the road is split into two tracks. The person were not sure to choose which track, so before randomly selecting one track he make a signal flag, so that if the track was not the right one he can rollback to signal flag and select the right track. In this example the signal flag becomes the savepoint. figure is as under.

Syntax
SQL>SAVEPOINT
->Savepoint name should be explanatory.
example
->Before deleting the records of employee whose age is above 75, we are not sure that whether we are given work to actually delete the records of employee whose age is above 75yrs or 80yrs. So before proceding further we should create savepoint here if we are been order later than it might create loss of information.
SQL>savepoint orignally;
SQL>delete from emp
where emp_age > 75;
->The above command deletes the records of those employee whose age is above 75 yrs. Though the changes are reflected on database they are not actually save as explained above they are stored in temporary area.
->After some time we are given order to increase employee salary to 10%. We can increase by generating following command. But before that we will make savepoint to our data so incase if the top level management change their mind and order's no increment should be given than we have can simply pass rollback entry achieve present state.
SQL>savepoint increm_sal;
SQL>update emp
set salary=salary*10;
->It will Increase salary of employee by 10%.
->After sometime top level management decided that salary of only programmer should be increased by 10% than only we have to do is just to pass entry of rollback before salary is updated.
SQL>rollback to increm_sal;
->It will rollback the changes made to emp_salary now we can update salary records for employee who is programmer. If we have dout than we can put savepoint, otherwise savepoint is not compulsory.
SQL>update emp
set salary=salary*10;
where emp_status='PROGRAMMER';
->It will increase salary of only programmers.
If all the changes have been taken place and now we have decided that no further changes require and we have to made changes to apply on database permanently than we can simply generate commit command to reflect changes permanently on database.
SQL>commit work;