Transaction Control Language – TCL

Suppose we have inserted some records in to Employee table. Now we need to save them. How? Similarly, we have updated something wrong on the table. After updating we realized that its wrong. Now we need to unsave the changes that have been done. How? We have deleted something or inserted something which is not correct. It has to be undone. All these saving and undoing the tasks can be done by TCL. Some of the commands in TCLs are as below:

Table of Contents

COMMIT

COMMIT saves the transaction on the database. The transaction can be insert, delete or update. Once the COMMIT is issued, the changes are saved permanently in the database. It cannot be undone.

UPDATE STUDENT SET STUDENT_NAME = ‘Mathew’ WHERE STUDENT_NAME = ‘Mahtwe’;
COMMIT;

Above set of transactions, updates the wrong student name to the correct one and saves the changes permanently in the database. Update transaction is complete only when commit is issued, else there will be lock on ‘Mahtwe’ record till the commit or rollback is issued.

Below diagram shows that ‘Mahtwe’ is updated to ‘Mathew’ and still there will be a lock on his record. Once Commit is issued, updated value is permanently saved to database and lock is released.

ROLLBACK

ROLLBACK command is used to undo the insert, delete or update transaction in the database. It undoes the transaction performed on the table and restores the previous stored value.

UPDATE STUDENT SET STUDENT_NAME = ‘Stewart’ WHERE STUDENT_NAME = ‘Mathew;
ROLLBACK;

Here, after updating the student name, user realizes that he has updated the wrong record and he wants to undo his update. What he does is, he issues ROLLBACK command and undoes his update. When he issues update statement Mathew’s record will be locked for update and will be updated to ‘Stewart’. But lock will not be released – meaning update is not saved fully into the database and transaction is not complete. Once the rollback is issued, it undoes the update and restores the value to ‘Mathew’ and save the changes permanently. Hence there will not be any changes done to Mathew.

SAVEPOINT

Suppose there are set of update, delete transactions performed on the tables. But there are some transactions which we are very sure about correctness. After that set of transactions we are uncertain about the correctness. So what we can do here is we can set a SAVEPOINT at the correct transaction telling the database that, in case of rollback, rollback till the savepoint marked. Hence the changes done till savepoint will be unchanged and all the transactions after that will be rolled back.

Have look at below transactions.

    • It updates ‘Mahtwe’ to ‘Mathew’. Hence we have lock on Mathew record.
    • It also updates Joseph’s record for his Age to 15.

Here, say we have set the SAVEPOINT after first transaction. Then second transaction for age update is issued and we see that it is a wrong update, but the name update is correct. Here we have to rollback only the last transaction and retain the first transaction. So we issue Rollback till the savepoint. What it does is, it reverts all the transaction till the savepoint. Although there is no commit, the transactions till savepoint is retained and saved later upon commit. Hence you can see the lock on Mathew’s record.

UPDATE STUDENT SET STUDENT_NAME = ‘Mathew’ WHERE STUDENT_NAME = ‘Mahtwe’;
SAVEPOINT S1;
UPDATE STUDENT SET AGE = 15 WHERE STUDENT_ID = 100;
ROLLBACK to S1;

In the case of multiple transactions, savepoint can be given after each transactions and transaction can be rolled back to any of the transactions.

TRANSACTION T1;  

— Transaction can be insert, update or delete

 
SAVEPOINT S1;
TRANSACTION T2;
SAVEPOINT S2;
TRANSACTION T3;
SAVEPOINT S3;
TRANSACTION T4;
ROLLBACK TO S1; -- This will rollback all the changes by T1 and T2 and will have only the changes done on T1.

AUTOCOMMIT

AUTOCOMMIT command automatically commits each transaction after its execution. If this command is set, then no need to explicitly issue commit. We cannot rollback our transactions, if AUTOCOMMIT is on. This needs to be set /unset before we begin any transactions.

SET AUTOCOMMIT ON; -- Sets AUTOCOMMIT to ON
SET AUTOCOMMIT ON; -- Sets AUTOCOMMIT to OFF
Translate »