Re: read consistency question

"Tanel Poder" <tanel@@peldik.com> wrote in message news:3f1ee05d_1_at_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
> > >
> > >
> > >
> >
>
>

Tanel

This (as I understand it) is the correct behaviour for serializable transactions.
I do not believe we are seeing a bug here. It is the type of isolation the OP requires and the 8177 error is telling him
that a rollback/restart transaction is required. It may well be that due to the
high concurrency session A will never succeed and a different approach is required.

HTH eric

--
Remove the dross to contact me directly