Transaction Control Language (TCL Commands)

COMMIT : This command is used to save the users action  until that particular point of time. Until and less commit is performed , the changes made by transaction are not made permanent.

Syntax : Commit

ROLLBACK : is used to undo the changes to previously successfully saved status.

Syntax : Rollback;.

What happens internally when COMMIT and ROLLBACK are executed ??

Whenever any updation occurs in the database , then following three activities takes place.

The Undo i.e. the original image of the data before the changes were made is saved in the UNDO segment ,

A Redo entry which actually maintains information about the changes being made, is saved in the REDO buffer segment of system global area (SGA).

The data in the data block is modified as per the new changes.

Now when the COMMIT is fired , then the log writer  LGWR immediately writes the Redo entries from the redo log buffer into both the Online active REDO LOG file and the Archived REDO log file and a system generated number (SGN) is generated. Then from the redo log files , LGWR writes the Redo entries containing information about the changes into the disk. the transaction waits until the redo entries are copied from Redo log file into the disk. Once this is done , a completion message is sent to the user and the Undo blocks are marked as committed. The Database writer DBWR may or may not change the data block on the disk  immediately after commit. There is an separate internal process for that. The data blocks in the buffer are marked as completed only after they are written to the disk.

When , Rollback is fired , then the changes made are rolled back to original segment using the undo block.

Questions/Suggestions
Have any question or suggestion for us?Please feel free to post in Q&A Forum
Print Friendly, PDF & Email
Data Manipulation Language ( DML Commands)
Deadlocks in SQL
Shikha Katariya

Shikha Katariya

Shikha Katariya ,the Blog author is QA Engineer by profession,Currently serving in MNC, She has more than 4 years of experience in software industry and has worked for domains like Insurance , Core & retail Banking. Always keen to learn new technologies , she has working experience in mainframes,informatica ,and ETL Testing.

You may also like...

Leave a Reply

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