Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: read consistency question

Re: read consistency question

From: Tanel Poder <tanel_at_@peldik.com>
Date: Wed, 23 Jul 2003 22:22:05 +0300
Message-ID: <3f1ee05d_1@news.estpak.ee>


Hi!

There's several bugs related to serializable transaction isolation levels. The one you get might be because of index block splits occurring during inserting (or updating). Try with no indexes first. Then with indexes. And when you get the 8177 error again, just retry your DML, because index block is already split.

Tanel.

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns93C18C43F177Cchuckhsofthomenet_at_130.133.1.4...
> Already tried it. It produces an ora-08177 error if you try to do DML
> (i.e. the delete) on a table that another session has changed since your
> transaction began.
>
> Also tried encapsulating the insert and delete between a begin/end pair
> thinking that it might treat the anonymous pl/sql as a single statement.
> That didn't work either. It yielded the same results as if they were run
> as two separate statements. --
> Chuck
>
> "JJ Reynolds" <jjrjunk_at_adelphia.net> wrote in
> news:GwzTa.36542$8g6.611241_at_news1.news.adelphia.net:
>
> > Check into SET TRANSATION ISOLATION LEVEL SERIALIZABLE;
> >
> > I *think* it will do what you are asking.
> >
> > -JJ
> >
> >
> >
> > "Chuck" <chuckh_at_softhome.net> wrote in message
> > news:Xns93C182EF925C8chuckhsofthomenet_at_130.133.1.4...
> >> How can I set transaction-level read consistency in a read/write
> >> transaction?
> >>
> >> For example lets say session A is inserting and commiting rows every
> >> few seconds to table mytable. In session B I want to copy some rows
> >> from mytable to another table and then delete only those rows,
> >> ignoring those created by session A between after my insert began.
> >>
> >> insert into mytable2 (select * from mytable where col1 = 'x');
> >> 10 rows inserted
> >>
> >> delete from mytable where col1 = 'x';
> >> 12 rows deleted.
> >>
> >> I only want to delete the 10 rows that were processed by the insert.
> >> I don't want to delete the 2 rows that session A inserted after the
> >> insert began. I'm not looking for a workaround such as using an
> >> intermediate staging table. I'm looking for a way to make my session
> >> do transaction- level read consistency so it doesn't even see the 2
> >> new rows inserted by session A after my insert began.
> >>
> >> TIA
> >
> >
> >
>
Received on Wed Jul 23 2003 - 14:22:05 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US