Re: read consistency question

From: Chuck <chuckh_at_softhome.net>
Date: 23 Jul 2003 17:47:19 GMT
Message-ID: <Xns93C18C43F177Cchuckhsofthomenet@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 - 12:47:19 CDT