Using SET TRANSACTION

You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a specified rollback segment. The Read-only transactions are helpful for running the multiple queries against one or more tables whereas other users update the same tables.

During the read-only transaction, all the queries refer to similar snapshot of the database, providing the multi-query, multi-table, read-consistent view. The Other users can carry on to query or update data as usual. The commit or rollback ends the transaction. In the illustration below, like the store manager, you use the read-only transaction to meet the sales figures for the day, the past week & the past month.

The figures are unaffected by the other users updating the database throughout the transaction.

DECLARE

daily_sales REAL;

weekly_sales REAL;

monthly_sales REAL;

BEGIN

...

COMMIT; -- ends previous transaction

SET TRANSACTION READ ONLY;

SELECT SUM(amt) INTO daily_sales FROM sales

WHERE dte = SYSDATE;

SELECT SUM(amt) INTO weekly_sales FROM sales

WHERE dte > SYSDATE - 7;

SELECT SUM(amt) INTO monthly_sales FROM sales

WHERE dte > SYSDATE - 30;

COMMIT; -- ends read-only transaction

...

END;

The SET TRANSACTION statement should be the first SQL statement in the read-only transaction and can only come out once in the transaction. When you set a transaction to the READ ONLY, the following queries see only changes committed before the transaction began. The use of READ ONLY does not affect the other users or transactions.

Posted Date: 10/4/2012 5:19:36 AM | Location : United States

Ask an Expert

Related Discussions:- Using set transaction, Assignment Help, Ask Question on Using set transaction, Get Answer, Expert's Help, Using set transaction Discussions

Write discussion on Using set transaction
Your posts are moderated

Write your message here..

Related Questions

What are decision support systems, (a) What are decision support systems, a... (a) What are decision support systems, and what role do they play in the business environment? (b) Data warehousing is defined as "a subject-oriented, integrated, non-volatile c

Create a sql database, The requirements as follows: Create a folder call... The requirements as follows: Create a folder called "SECURITY" on the server and upload all your project files to that folder. Please note, the "SECURITY" folder is NOT to be IN

Package standard, Package STANDARD The package named STANDARD defines t... Package STANDARD The package named STANDARD defines the PL/SQL atmosphere. The package specification globally declares the exceptions, types, and subprograms that are available

Collection methods in pl sql, Collection Methods:   The collection me... Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Write a stored procedure, a. Create a table odetails_new. It has all the a... a. Create a table odetails_new. It has all the attributes of odetails and an additional column called cost, whose values are the product of the quantity and price of the part bein

Avoiding collection exceptions, Avoiding Collection Exceptions   In ma... Avoiding Collection Exceptions   In many cases, if you reference a nonexistent collection element, then PL/SQL raises a predefined exception. Consider the illustration shown b

Data types and representations, Data Types and Representations This ex... Data Types and Representations This explains the concept possible representation, abbreviated possrep, and explains how these can be used in conjunction with constraints to de

Creating a table, Creating a Table Syantax: CREATE TABLE ENROL... Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Multiset types - sql, Multiset types - SQL An SQL multiset is what in ... Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Existential quantification - sql, Existential Quantification - SQL Ex... Existential Quantification - SQL Existential quantification-stating that something is true of at least one object under consideration-can be expressed by OR(r,c), meaning tha