SQL Transaction Control Language (TCL)

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 are as follows:
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.
Syntax:
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.

    • 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.

3. 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;

4. SET TRANSACTION

  • SET TRANSACTION is used for placing a name on a transaction.

    • Syntax:
    SET TRANSACTION [Read Write | Read Only];

  • You can specify a transaction to be read only or read write.
  • This command is used to initiate a database transaction.

Difference between ROLLBACK and COMMIT commands.

ROLLBACKCOMMIT
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;
▲