Re: Atomically moving rows from one table into another

Depending on your application design, the other option would be to serialize the transaction:
set transaction isolation level serializable;

Then, if someone slipped in a row during your transaction, your process would return ORA-8177.

So, if you serialize, your session will get the error. If you lock table, as Charlie suggests, then your transaction will proceed, but the other transaction, the one trying to slip in data, will wait on an enqueue.

-Mark

"charlie cs" <cs3526_at_ureach.com> wrote in message news:<sKrn9.3520$Oa1.2225_at_nwrddc02.gnilink.net>...
> Lock table A in share mode;
> insert into table B select * from table A;
> delete from table A;
> commit;
>
> "Wolfram Roesler" <wr_at_grp.de> wrote in message
> news:Xns929DB035AAFC0wrgrpde_at_130.133.1.4...
> > Hello,
> >
> > suppose I have two tables, tab1 and tab2, created from the same DDL.
> > I want to move data from tab1 to tab2 like this: (xxx is some
> > where clause)
> >
> > INSERT INTO tab2 (SELECT * FROM tab1 WHERE xxx);
> > DELETE FROM tab1 WHERE xxx;
> > COMMIT;
> >
> > Now, what happens if, after the INSERT yet before the DELETE,
> > someone inserts rows into tab1 that match xxx? They will be
> > deleted although they have not been transferred to tab2.
> >
> > Is there a way to atomically move data from one table to
> > another that doesn't suffer from this problem?
> >
> > Thanks for any help
> > W. Roesler
Received on Mon Oct 07 2002 - 14:58:33 CDT