TCL in Oracle 12c: Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK
Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK
TCL (Transaction Control Statement) is used to manage transaction in databases. So first of all,
What is Transaction?
A transaction is a sequence of one or more SQL statements that databases treats as a unit: either all of the statements are performed or none of them.
In Real time scenarios, we need Transaction to model the business process so that several operations can be performed as a unit. For e.g. when an Manager leaves the organization then a row must be inserted into JOB_HISTORY table to record when Manager left and for every Employees who reports to that manager value of MANAGER_ID must be updated in EMPLOYEES table. To model this we group the INSERT and UPDATE statements into a single transaction.
There are three types of TCL in SQL:
- SAVEPOINT: Used to mark a save point in transaction, which can be rollback later.
- COMMIT: It ends the current transaction and make the permanent changes.
- ROLLBACK: Used to undo (or rollback) an unsaved transaction either entirely or to the specified SAVEPOINT.
In SQL Developer:
Note: If you do not commit a transaction explicitly and the programs terminates explicitly then the database automatically rolls back to the last uncommitted transaction.
Committing a Transaction
Committing a transaction makes permanent changes, delete all Save-point and release the database locks.
BEFORE Transaction
SELECT * FROM REGIONS ORDER BY REGION_ID;
Transaction: add row to tables
INSERT INTO regions (region_id, region_name) VALUES (5, 'Africa');
Check for the added row
Commit the transaction:
Using Rollback: Rolling Back the Transaction
It undoes the changes. One can roll back entire current transaction or can roll back to a particular save-point.
1.Rollback the current transaction to specified savepoint.
Use the ROLLBACK statement with the TO SAVEPOINT clause.
- Does not end the transaction
- Reverses only the changes made after the specified savepoint
- Erases only the savepoints set after the specified savepoint (excluding the specified savepoint itself)
Releases all table and row locks acquired after the specified savepoint.
2.Rollback the entire current transaction:
Use either the ROLLBACK statement or in sql developer environment click on rollback icon.
- Ends the transaction
- Reverses all of its changes
- Erases all of its savepoints.
- Releases any transaction locks
Before transaction:
SELECT * FROM REGIONS ORDER BY REGION_ID;
Running a Transaction (updating the table):
UPDATE REGIONS
SET REGION_NAME = 'Middle East' WHERE REGION_NAME = 'Middle East and Africa';
Check the Changes: Region name changes from ‘Middle East and Africa’ to ‘Middle East’.
Rollback transaction:
ROLLBACK;
Check the Result after rollback: Region Name changes from ‘Middle East’ to ‘Middle East and Africa’.
Working with savepoints in Transaction:
Region Table before transaction
Check Countries in Region 4
SELECT COUNTRY_NAME, COUNTRY_ID, REGION_ID FROM COUNTRIES WHERE REGION_ID = 4 ORDER BY COUNTRY_NAME;
Check Countries in Region 5
No countries are there in Region 5.
Running a transaction with several savepoint.
UPDATE REGIONS SET REGION_NAME = 'Middle East' WHERE REGION_NAME = 'Middle East and Africa'; UPDATE COUNTRIES SET REGION_ID = 5 WHERE COUNTRY_ID = 'ZM'; SAVEPOINT zambia; UPDATE COUNTRIES SET REGION_ID = 5 WHERE COUNTRY_ID = 'NG'; SAVEPOINT nigeria; UPDATE COUNTRIES SET REGION_ID = 5 WHERE COUNTRY_ID = 'ZW'; SAVEPOINT zimbabwe; UPDATE COUNTRIES SET REGION_ID = 5 WHERE COUNTRY_ID = 'EG'; SAVEPOINT egypt;
Now Check REGIONS table after transaction:
Check the list of Countries in Region 4
Check the list of countries in Region 5
Now Rollback To Savepoint Nigeria
ROLLBACK TO SAVEPOINT nigeria;
Check REGIONS table after rollback:
Check list of countries in region 4 after rollback:
Check list of countries in region 5 after rollback:
Hence, we are done with the Transaction control Statement- SAVEPOINT, COMMIT and ROLLBACK and in Oracle 12c.
Hope You enjoyed the tutorial.
