Transaction control language is used to control the transactions performed on the database. TCL can save or revoke the transactions applied on the database from SYNC point.
SYNC point is nothing but where the COMMIT or ROLLBACK executed. TCL have below two statements.
COMMIT
COMMIT is used to save all transactions performed on database from the SYNC point. If the COMMIT performed then the changes performed from the SYNC point will be made as permanent. COMMIT can also closes all the open cursors at the point of COMMIT executes except the cursors WITH HOLD.
Commit will be used to make the changes made by DML statements permanent to the database. After performing DML operations, if we issue commit then those changes will be made permanent to the database. If Auto commit option for session is enabled, then this is not required as commit takes place automatically.
Syntax
EXEC SQL COMMIT END-EXEC.
Note - DDL statements will carry implicit commit. So, if we issue DDL statement against the database, after doing some DML operations, then due to implicit commit of DDL, previous DML statements also will be committed as commit of DDL applies for the session till that point.
ROLL BACK
ROLLBACK is used to revert back all transactions performed on database from the SYNC point. If the ROLLBACK performed then the changes performed from the SYNC point will be reverted back.
To Rollback the changes to a certain transaction point, we will use SAVEPOINT. The SAVEPOINT statement names and marks the current point in the processing of a transaction. With the ROLLBACK TO statement, save points undo parts of a transaction instead of the whole transaction.
Syntax
EXEC SQL ROLLBACK END-EXEC.
In a Relational Database Management System (RDBMS), the Structured Query Language (SQL) is used to perform multiple operations to store, retrieve and manipulate the data across various tables in a database.
Let us consider few scenarios where we might have updated a record mistakenly and want to restore the data or we have inserted few records and want to save them, there Transaction Control Language (TCL) comes into the picture. The Transaction Control Language is used to maintain the integrity and consistency of the data stored in the database.
The Transaction Control Language is used in conjunction with the Data Manipulation Language to control the processing and exposure of changes. Transactions are a fundamental part of how relational databases protect the integrity and reliability of the data they hold. Transactions are automatically used on all DDL and DML commands.
A transaction is used to group together a series of low-level changes into a single, logical update. A transaction can be anything from updating a single value to a complex, multistep procedure that might end up inserting several rows into a number of different tables. The classic transaction example is a database that holds account numbers and balances.
If you want to transfer a balance from one account to another, that is a simple two-step process: subtract an amount from one account balance and then add the same amount to the other account balance. That process needs to be done as a single logical unit of change, and should not be broken apart. Both steps should either succeed completely, resulting in the balance being correctly transferred, or both steps should fail completely, resulting in both accounts being left unchanged. Any other outcome, where one step succeeds and the other fails, is not acceptable.
Typically a transaction is opened, or started. As individual data manipulation commands are issued, they become part of the transaction. When the logical procedure has finished, the transaction can be committed, which applies all of the changes to the permanent database record. If, for any reason, the commit fails, the transaction is rolled back, removing all traces of the changes. A transaction can also be manually rolled back.
TCL means Transaction Control Language. These statements are used to control the transactions made by the DML statements to the database. Any change which is made to the database by a DML statement will be called as a transaction. We need to control these transactions made by DML statements.
Whatever change made to the database by a DML statement will not reside permanently in the database, unless autocommit option enabled in the session. Those changes will reside in temporary memory. So, we need to handle these changes.
Transaction Control Language(TCL) commands are used to manage transactions in database.These are used to manage the changes made by DML statements. It also allows statements to be grouped together into logical transactions. These commands are to keep a check on other commands and their affect on the database. These commands can annul changes made by other commands by rolling back to original state. It can also make changes permanent.