DCL Statements In DBMS

Ask For Raise In Salary

DCL Statements:

 Defining a transaction, Making Changes Permanent with COMMIT, Undoing Changes with ROLLBACK, Undoing Partial Changes with SAVEPOINT and ROLLBACK

DCL(Data Control Language) :

DCL includes commands such as GRANT and REVOKE which mainly deals with the rights, permissions and other controls of the database system.

Examples of DCL commands:

  • GRANT-gives user’s access privileges to database
  • REVOKE-withdraw user’s access privileges given by using the GRANT command.

Defining A Transaction:

A transaction is an action, or series of actions that are being performed by a single user or application program, which reads or updates the contents of the database. A transaction can be defined as a logical unit of work on the database.

Also Read: What Is Transaction? What Are The Properties Of Transaction?

Introduction to TCL

  • TCL stands for Transaction Control Language.
  • This command is used to manage the changes made by DML statements.
  • TCL allows the statements to be grouped together into logical transactions.

TCL Commands

TCL commands are as follows:
1. COMMIT
2. SAVEPOINT
3. ROLLBACK

COMMIT COMMAND

  • COMMIT command saves all the work done.
  • It ends the current transaction and makes permanent changes during the transaction.

Syntax:
commit;

SAVEPOINT COMMAND

  • SAVEPOINT command is used for saving all the current point in the processing of a transaction.
  • It marks and saves the current point in the processing of a transaction.

Syntax:
SAVEPOINT <savepoint_name>

Example:
SAVEPOINT no_update;

  • It is used to temporarily save a transaction, so that you can rollback to that point whenever necessary.

ROLLBACK COMMAND

  • ROLLBACK command restores database to original since the last COMMIT.
  • It is used to restores the database to last committed state.

Syntax:
ROLLBACK TO SAVEPOINT <savepoint_name>;

Example:
ROLLBACK TO SAVEPOINT no_update;

Making Changes Permanent with 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.

Undoing Changes with 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.

Undoing Partial Changes with SAVEPOINT and ROLLBACK

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.

About Robin Steve

X_Skull-Bleed Is his favorite nickname. He is Gaming Addict wanted to beat Shroud and Ninja. Shares Knowledge by Writing Content For Friends. ;) Loves to read love stories.

View all posts by Robin Steve →

Leave a Reply

Your email address will not be published. Required fields are marked *