Transaction Control Language(TCL) commands are used for maintaining consistency of the database and for the management of transactions made by the DML commands. Whenever DML operation is main in an SQL table, the transaction never be saved unless or until save command is issued or a discard is issued.
Transaction Control Language Commands
TCL provide the following statement.
- Commit (save)
- Rollback (discard)
Transaction Control Languages Mechanism
Whenever DML operation is made in SQL table just before a temporary memory is created in side in the sever, technically is known as ROLLBACK SEGEMENT and the table on which the DML operation is going to be made is copied temporary to the rollback segment is known as snapshot. If commit command is issued from a sql prompt a rollback segment along with the snapshot gets deleted and the records transfer to main table will be permanent. If rollback command is issued from after DML operation a snapshot available inside the rollback segment again recopied to the original location with the old data and rollback segment gets deleted.
Better to read the previous topic to get good knowledge about SQL.
Data Definition Language (DDL) in SQL
Data Manipulated Language (DML) in SQL
Data Control Language (DCL) in SQL
Note:
Data one committed cannot be rollback and rollback cannot be committed.
The basic difference between buffer memory and rollback segment is buffer is client side memory it holds statement for a temporary period on other hand rollback segment is a sever side temporary memory, which holds a table in the form of snapshot for a temporary period.
Transaction Control Language commands are used to handle transactions in the database. These are used to manage the changes made by DML statements and also allows statements to be grouped together into logical transactions.
Commit
This command is used to save the data permanently. Whenever we perform any of the DML command such as INSERT, DELETE or UPDATE, these can be rollback if the data is not stored permanently. So in order to be at the safer side COMMIT command is used.
Syntax
commit;
Rollback
This command is used to get the data or restore the data to last committed state. If due to some reasons the data inserted, deleted or updated is not correct, you can rollback to the last committed state.
Syntax
rollback;
Auto commit and Auto rollback:
In some case data are automatically committed and automatically rollback by the system defines which is technically known as auto commit and auto rollback in Transaction Control Language
Auto commit
- Whenever a user exit out of SQL*PLUS normally after performing DML operation.
- Whenever DCL or DDL command issued after any DML operation.
Auto Rollback
- Whenever a user exit out of SQL*PLUS abnormally after performing DML operation.
- Dirty system crash.
Reference Link
Data Control Language in Wiki
