Re: Problems using serializable transaction

Doug,

No, I can't not 'set transaction read only' because I need to write the query result into a temporary table for further processing. Is there another way?

Jack

dougburns_at_hotmail.com wrote:
>
> 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 Fri Feb 19 1999 - 15:37:47 CST