Re: read consistency question

"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
so basically your saying that you have one table myTable. Session A and Session B are performing DML on it. Session 'A' does a commit, but you dont want session 'B' to actually see the commit?

is that correct? I dont think Oracle works that way. Once you issue a commit it becomes global.

Easiest way to handle this is to add a 'Session' Column to your table. Then track it that way. I know forms has logic in it where if you query data and another users changes, you are forced to re-query BEFORE you can change the data, once its committed you can still perform DML on it.

A second way to do this is to use a Global Temp table. Each session commits into the temp table. Then when you log off or at whatever time you choose, those records are committed to the actual database table. Received on Wed Jul 23 2003 - 13:23:58 CDT