Transaction Control Language provides different commands that can be used to control a transaction.
As the first step, we will first try to understand the necessity of using this language.
Suppose you are buying a watch online.
You will browse through the internet and will select a watch. Suppose , before you had selected it, only 1 watch of same model was available. Now after you have added it to your cart, it becomes 0. Now you are doing online payment for it. But suddenly your system crashes.
In the meantime some other user browses for the same watch. Here in this case, you have not done the payment so the system must revert back the value of watch available to 1 so that other user can see the item in available list.
For reverting back the value t 1, system must have knowledge that previously the value was 1.All these details before and after some action is done(selection of the watch for suppose) is kept stored i a system log. This is done to ensure that if the transaction is not completed, the states can be reverted to its original self. For carrying out all these processes(storing, reverting back), Transaction control language is used.
The following are the different commands used for Transaction control:
1 .Rollback command
2. Savepoint command
3. Commit
Rollback Command:
When we want to rollback the transaction to previous phase(In the above example when the value of watches available were 1), we use this command.
Syntax:
Rollback to savepoint_name;
Now the question comes what this save point is. In the above example, we have taken only a simple example where the prevuious value of the item available was 1.And it becomes 0. But it May happen that maybe at first the items available were 10 .Some user bought some item and it became 8 .Again some one bought 4 item and the remaining items became 4.So,how the system will know how far it has to rollback?? Do es it has to rollback to the point where the items available were 4 or to a point when the items available were 8. For this confusion, several savepoint are established so as to guarantee that system can rollback to any point desired.
2. Savepoint command
Savepoint command is used to establish a point uptil when all the transaction data are saved temporarily. Whenever needed, the data can be retrieved for the point.
Syntax:
Savepoint savepoint_name;
3.Commit:
Commit command is used to end a transaction permanently. After a commit is done, no changes further can be made to the transaction done.
Syntax: Commit;
TechieNutzz 