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.
1. COMMIT
2. SAVEPOINT
3. ROLLBACK
4. SET TRANSACTION
1. COMMIT COMMAND
- COMMIT command saves all the work done.
- It ends the current transaction and makes permanent changes during the transaction.
commit;
2. 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.
- It is used to temporarily save a transaction, so that you can rollback to that point whenever necessary.
Syntax:
SAVEPOINT <savepoint_name>
Example:
SAVEPOINT no_update;
3. ROLLBACK COMMAND
- ROLLBACK command restores database to original since the last COMMIT.
- It is used to restores the database to last committed state.
ROLLBACK TO SAVEPOINT <savepoint_name>;
Example:
ROLLBACK TO SAVEPOINT no_update;
4. SET TRANSACTION
- SET TRANSACTION is used for placing a name on a transaction.
- You can specify a transaction to be read only or read write.
- This command is used to initiate a database transaction.
Syntax:
SET TRANSACTION [Read Write | Read Only];
Difference between ROLLBACK and COMMIT commands.
| ROLLBACK | COMMIT |
|---|---|
| ROLLBACK command is used to undo the changes made by the DML commands. | The COMMIT command is used to save the modifications done to the database values by the DML commands. |
| It rollbacks all the changes of the current transaction. | It will make all the changes permanent that cannot be rolled back. |
| Syntax: DELETE FROM table_name ROLLBACK | Syntax: COMMIT; |
