Interview Questions Answers.ORG
Interviewer And Interviewee Guide
Interviews
Quizzes
Home
Quizzes
Interviews DB Oracle Interviews:Concepts and ArchitectureData AccessDatabase ArchitectureDatabase ManagementDatabase Security OracleDistributed ProcessingFlexfieldForms ReportsGeneral OracleMemory ManagementMTO-SAP Financial AccountingOCIOracle AOLOracle AROracle Backup RecoveryOracle D2KOracle DatabaseOracle Database DeveloperOracle DB OptimizationOracle DBAOracle DeveloperOracle ETLOracle Forms 3.0Oracle Forms 4.0Oracle GLOracle PL-SQLOracle RMANOracle ScenariosOracle SecurityOracle SQLOracle System ArchitectureOracle Technology Network (OTN)Programmatic ConstructsRAC (Real Application Clusters)SQL Plus
Copyright © 2018. All Rights Reserved
Oracle Database Interview Question:
How To Manage Transaction Isolation Level?
Submitted by: AdministratorTransaction 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:
<pre>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 /</pre>
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.
Submitted by: Administrator
<pre>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 /</pre>
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.
Submitted by: Administrator
Copyright 2007-2024 by Interview Questions Answers .ORG All Rights Reserved.
https://InterviewQuestionsAnswers.ORG.
https://InterviewQuestionsAnswers.ORG.