Re: Problems using serializable transaction

Jack,

You could try 'set transaction read only'. Watch out for 'snapshot too old errors', but you shouldn't get these unless there are a lot of updates going on on the table.

HTH Doug Burns
Oracle DBA and Instructor

In article <36CB34E3.956B443B_at_email.com>,   Jack <jhou_at_email.com> wrote:
> Hello,
>
> I am working on an OLTP/OLAP hybrid application. I need to read from a
> large table more than once consistently within a stored procedure. In
> the stored procedure, I also stored the intermediate data in a temporary
> table for further processing (select, update, and delete) by other SQL
> statements. In the Oracle manual, it says that 'set transcation
> isolation level serializable' statement will allow repeatable reads and
> avoid phantom rows.
>
> However, when I tried this, I have been getting the following error:
>
> "ORA-08177: Cannot serialize access for this transaction"
>
> At the time that this error occurred, there was only one user on the
> database. I have set the INITRANS to 5 for the affected tables.
>
> Is there a limit on how long a transaction can be when using
> serializable transaction? What's the proper design for using
> serializable transaction? Is there another way of getting repeatable
> reads without using serializable transaction?
>
> Thanks in advance!
>
> Jack
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 17 1999 - 21:51:28 CST