Transaction Control Language (TCL)

Transaction Control Language (TCL)

A Transaction Control Language (TCL) is a  computer language and a subset of  SQL, used to control transactional processing in a database. A transaction is logical unit of work that comprises one or more SQL statements, usually a group of  Data Manipulation Language (DML) statements.

A series of one or more SQL statements that are logically related, or a series of operation performed on Oracle table data is termed as a Transaction. Oracle treats changes to table data as a two step process. First the changes requested are done. To make these changes permanent a COMMIT statement has to be given at the SQL prompt. A ROLLBACK statement given at the SQL prompt can be used to undo a part of or the entire Transaction.

A Transaction begins with the first executable SQL statement after a Commit, Rollback or Connection made to the Oracle engine. All changes made to an Oracle table data via a transaction are made or undo at one instance.

Specially, a Transaction is a group of events that occurs between any of the following events:

Connecting to Oracle

Disconnecting from Oracle

Committing changes to the table

Rollback

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples of TCL commands include:

COMMIT to apply the transaction by saving the database changes.

 ROLLBACK to undo all changes of a transaction.

 SAVEPOINT to divide the transaction into smaller sections. It defines breakpoints for a transaction to allow partial rollbacks.

COMMIT

PURPOSE: To end your current transaction and make permanent all changes performed in the transaction. This command also erases all savepoints in the transaction and releases the transaction's locks. You can also use this command to manually commit an in-doubt distributed transaction.

SYNTAX:

COMMIT [WORK]  [ COMMENT 'text'  | FORCE 'text' [, integer] ]

Where:

WORK : is supported only for compliance with standard SQL. The statements COMMIT and COMMIT WORK are equivalent.

COMMENT : specifies a comment to be associated with the current transaction. The 'text' is a quoted literal of up to 50 characters that Oracle stores in the data dictionary view DBA_2PC_PENDING along with the transaction ID if the transaction becomes in-doubt.

FORCE : manually commits an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. You can also use the integer to specifically assign the transaction a system change number (SCN). If you omit the integer, the transaction is committed using the current SCN. COMMIT statements using the FORCE clause are not supported in PL/SQL.

PREREQUISITES:

You need no privileges to commit your current transaction. To manually commit a distributed in-doubt transaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually commit a distributed in-doubt transaction that was originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Example:

To commit your current transaction, enter

SQL> COMMIT WORK;

Commit complete.

ROLLBACK

PURPOSE:

To undo work done in the current transaction. You can also use this command to manually und the work done by an in-doubt distributed transaction.

SYNTAX:

ROLLBACK [WORK][ TO [SAVEPOINT] savepoint | FORCE 'text' ] Where:

WORK : is optional and is provided for ANSI compatibility.

TO : rolls back the current transaction to the specified savepoint. If you omit this clause, the ROLLBACK statement rolls back the entire transaction.

FORCE : manually rolls back an in-doubt distributed transaction. The transaction is identified by the 'text' containing its local or global transaction ID. To find the IDs of such transactions, query the data dictionary view DBA_2PC_PENDING. ROLLBACK statements with the FORCE clause are not supported in PL/SQL.

PREREQUISITES:

To roll back your current transaction, no privileges are necessary. To manually roll back an in-doubt distributedtransaction that you originally committed, you must have FORCE TRANSACTION system privilege. To manually roll back an in-doubt distributed transaction originally committed by another user, you must have FORCE ANY TRANSACTION system privilege.

Example:

To rollback your current transaction, enter

SQL> ROLLBACK;

Rollback complete.

CLOSING TRANSACTION:

A Transaction can be closed by using either a Commit or a Rollback statement. By using these statement, table data can be changed or all the changes made to the table data undo.

Using COMMIT:

A COMMIT ends the current transaction and makes permanent any changes made during the transaction. All transaction locks acquired on tables are released.

Syntax:

COMMIT;

Using ROLLBACK:

Syntax:

ROLLBACK [WORK] [TO SAVEPOINT]  < Save-point_Name>

WORK: It is optional and is provided for ANSI compatibility

SAVEPOINT : It is optional and it is used to rollback a partial transaction, as far as the

specified savepoint.

SAVEPOINTNAME:  It is a savepoint created during the current transaction

Crating a SAVEPOINT:

SAVEPOINT marks and saves the current point in the processing of a transaction. When a SAVEPOINT is used with a ROLLBACK statement, parts of a transaction can be undone. An active savepoint is one that is specified since the last COMMIT or ROLLBACK.

Syntax:

SAVEPOINT  <SavePointName>

Example:

DECLARE

Total_Sal number(9);

BEGIN

INSERT INTO Emp VALUES('E101', 'Aamir', 10, 7000');

INSERT INTO Emp VALUES('E102', 'Aatif', 11, 6500');

SAVEPOINT no_update;

UPDATE Emp SET salary =salary+2000 WHERE Emp_Name = 'Aamir';

UPDATE Emp SET salary =salary+2000 WHERE Emp_Name = 'Aatif';

SELECT sum(Salary) INTO Total_sal FROM Emp;

IF Total_Sal > 15000 THEN

ROLLBACK To SAVEPOINT no_update;

END IF;

COMMIT;

END;

The bove PL/SQL block, it will insert two records in the table Emp, then after no_update SavePoint has been declared.

on both of record the updated with 2000 respectively, now calculated all the salary in Emp Table, If Salary has been reached more than 15000 in its table, then it is automatically rolled back, it means undo that was updated earlier by 2000. If this type situation is coming then you can use these commands.