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
Chuck

I think I'd be tempted to add a column to mytable which would initially be updated as an 'identified' row. The 'identified' rows could then be inserted into mytable2 and deleted from mytable.
Best I can think of at present.

HTH eric

--
Remove the dross to contact me directly