| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Atomically moving rows from one table into another
In addition to suggestions to use PL/SQL loop over cursor for this, you can
achieve what you want in a serializable transaction:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO tab2 (SELECT * FROM tab1 WHERE xxx);
DELETE FROM tab1 WHERE xxx;
COMMIT;
I conducted a simple demo for this:
create table tab1 as select object_id, object_name, object_type from all_objects; create table tab2 as select * from tab1 where rownum < 1;
SQL> set transaction isolation level serializable;
Transaction set.
SQL> insert into tab2 select * from tab1 where object_type='TABLE';
102 rows created.
SQL> exec wait_for_signal('stage1')
PL/SQL procedure successfully completed.
SQL> delete tab1 where object_type='TABLE';
102 rows deleted.
SQL> commit;
Commit complete.
SQL> exec send_signal('stage2')
PL/SQL procedure successfully completed.
SQL> insert into tab1 (object_id, object_name, object_type) 2 values( 1000000, 'TEST_TAB_1','TABLE');
1 row created.
SQL> insert into tab1 (object_id, object_name, object_type) 2 values( 1000001, 'TEST_TAB_1','TABLE');
1 row created.
SQL> insert into tab1 (object_id, object_name, object_type) 2 values( 1000002, 'TEST_TAB_1','TABLE');
1 row created.
SQL> commit;
Commit complete.
SQL> exec send_signal('stage1')
PL/SQL procedure successfully completed.
SQL> exec wait_for_signal('stage2')
PL/SQL procedure successfully completed.
SQL> select count(*) from tab1 where object_type = 'TABLE';
COUNT(*)
----------
3
SQL> select count(*) from tab2 where object_id in (1000000,1000001, 1000002);
COUNT(*)
----------
0
-----------------------------------------------------------------------------
Comments:
wait_for_signal() and send_signal() are using a pipe to send signals from one session to another - I used them to coordinate actions in these two sample sessions to run in needed order. So, session 1 inserts some rows based on some condition into tab2, then session 2 inserts a few new rows into tab1 which qualify for the same condition and commits. Session 1 then deletes all rows matching the original condition (and can delete those newly inserted rows since they match, too, which we do not want to happen because they arrived after we copied rows into tab2). Session 1 commits. Session 2 now reads tab1 and verifies that the rows it just inserted are still there. They are, and note that session 1 deletes exactly the same number of rows it copied into tab2, even though session 2 just inserted and committed three new rows into tab1. Session 1 performed as if no other sessions were active, which is what SERIALIZABLE isolation level guarantees. Remove SET TRANSACTION and repeat the test (transaction now runs at default READ COMMITTED level) - now 105 rows are deleted from tab1 while only 102 were inserted into tab2 and new rows do not survive.
hth.
-- Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications. All opinions are mine and do not necessarily go in line with those of my employer. "Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns929DB035AAFC0wrgrpde_at_130.133.1.4...Received on Mon Oct 07 2002 - 05:21:47 CDT
> 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
 |
 |