Transaction Control Language (TCL) SQL commands with examples

0
199

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.

What is 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.

What is basic underlying idea of TCL?

Rollback database state to previously committed state or any savepoints in case of failure of single sub-step.

Where is TCL used?

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.

Types of TCL commands

1. COMMIT
2. SAVEPOINT
3. ROLLBACK
4. SET TRANSACTION

COMMIT

: Make changes done in transaction permanent

Syntax: commit;

ROLLBACK

: Rollbacks the state of database to the last commit point

Syntax: ROLLBACK TO SAVEPOINT <save_point_name>;

SAVEPOINT

: Use to specify a point in transaction to which later you can rollback by its name.

Syntax: SAVEPOINT <save_point_name>;

SET TRANSACTION

: Used to initiate a database transaction & give it a name.

Syntax: SET TRANSACTION <READ ONLY/READ WRITE> NAME ‘Transaction_name_xyz’;

Code example

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; 

The first COMMIT statement ensures that SET 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.