Transaction Control Language(TCL) commands are used to manage transactions in the database made by DML statements. It also allows statements to be grouped together into logical transactions to keep a systematic flow in order to complete a transaction.
▪ A transaction is a set of SQL statements which a DBMS treats as a Single Unit. i.e. all the statements should execute successfully or none of the statements should execute.
▪ To control transactions Oracle does not made permanent any DML statements unless you commit it. If you don’t commit the transaction and power goes off or system crashes then the transaction is roll backed.
Rollback database state to previously committed state or any savepoints in case of failure of single sub-step.
A basic application could be a bank withdrawal activity. For this to happen certain steps need to be followed:
First Check if there is sufficient balance to withdraw, if yes proceed, else fail the transaction, i.e. no amounts should be cut from withdrawee’s account.
Second, if there’s sufficient balance withdraw and reduce/ cut that much amount from withdrawee’s bank. Only if both steps are done successfully, transaction is said to be completed.
4. SET TRANSACTION
: Make changes done in transaction permanent
: Rollbacks the state of database to the last commit point
Syntax: ROLLBACK TO SAVEPOINT <save_point_name>;
: Use to specify a point in transaction to which later you can rollback by its name.
Syntax: SAVEPOINT <save_point_name>;
: Used to initiate a database transaction & give it a name.
Syntax: SET TRANSACTION <READ ONLY/READ WRITE> NAME ‘Transaction_name_xyz’;
The below example includes usage of all TCL commands:
BEGIN TRAN INSERT INTO class VALUES(1,'XYZ'); COMMIT; UPDATE class SET name = 'ABC' WHERE id = '1'; SAVEPOINT A; INSERT INTO class VALUES(2,'MLN'); SAVEPOINT B; END TRAN
Example to depict SET TRANSACTION:
COMMIT; SET TRANSACTION READ ONLY NAME 'Transaction_Name_1'; SELECT ID, Name FROM Student_table WHERE ID = 1 COMMIT;
COMMIT statement ensures that
TRANSACTION is the first statement in the transaction. The last
COMMIT statement does not actually make permanent any changes to the database. It simply ends the read-only transaction.