Home >> FAQs/Tutorials >> Oracle Tutorials

Oracle Tutorials - Manage Transaction Isolation Levels

By: FYIcenter.com

(Continued from previous topic...)

How To Manage Transaction Isolation Level?

Transaction isolation level can be managed in a procedure by using the SET TRANSACTION and COMMIT statements. Here is a sample script on how to manage transaction isolation level:

SQL> CREATE OR REPLACE PROCEDURE HR.TOTAL_SALARY AS
  2    total_salary NUMBER(12,2);
  3  BEGIN
  4    SET TRANSACTION READ ONLY;
  5    SELECT SUM (salary) INTO total_salary FROM employees;
  6    DBMS_OUTPUT.PUT_LINE('Total salary 1: ' 
  7       || total_salary); 
  8    -- Other sessions may change salaries of some records
  9    SELECT SUM (salary) INTO total_salary FROM employees;
 10    DBMS_OUTPUT.PUT_LINE('Total salary 2: ' 
 11       || total_salary);
 12    COMMIT;
 13  END;
 14  /

SQL> EXECUTE TOTAL_SALARY;
Total salary 1: 691400
Total salary 2: 691400

"READ ONLY" transaction level takes a read only snapshot of the database. This allows other sessions to update the database without any locks. All queries in the session will produces identical results. So both SELECT statements in this script will return the same value guaranteed.

(Continued on next topic...)

1. What Is PL/SQL?
2. What Are the Types PL/SQL Code Blocks?
3. How To Define an Anonymous Block?
4. How Many Anonymous Blocks Can Be Defined?
5. How To Run the Anonymous Block Again?
6. What Is a Stored Program Unit?
7. How To Create a Stored Program Unit?
8. How To Execute a Stored Program Unit?
9. How Many Data Types Are Supported?
10. What Are the Execution Flow Control Statements?
11. How To Use SQL Statements in PL/SQL?
12. How To Process Query Result in PL/SQL?
13. How To Create an Array in PL/SQL?
14. How To Manage Transaction Isolation Level?
15. How To Pass Parameters to Procedures?
16. How To Define a Procedure inside Another Procedure?
17. What Do You Think about PL/SQL?