Re: read consistency question

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Thu, 24 Jul 2003 01:20:56 GMT
Message-ID: <Xns93C1D8D2B2BF2pobox002bebubcom@216.148.227.77>

Chuck <chuckh_at_softhome.net> wrote in
news:Xns93C18C43F177Cchuckhsofthomenet_at_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

>>
>

The best solution would be to not commit in session A if possible.

If you have no control on what session A is doing, then you will have to do some coding (session A really shouldn't be committing every few seconds anyway but that's another subject).

The best way would be to bulk collect the rowids you will process into a varray and use that in the copy as well as the delete later. Here's a sequential example, the insert and commit happens after the copy but before the delete, but it wouldn't matter if they were different sessions.

SQL> -- table starts with 10 rows
SQL> create table t as
  2 select * from (

  3          select rownum rn, a.* from all_objects a
  4      order by object_id)

  5 where rn < 11;

Table created.

SQL> create table t1 as select * from t where 0 = 1;

Table created.

SQL> commit;

Commit complete.

SQL> create or replace type arr_rowid as table of varchar2(18);   2 /

Type created.

SQL> declare x arr_rowid;
  2 begin
  3 -- copy rowids into varray
  4

  5      select rowidtochar(rowid) bulk collect
  6      into x from t;
  7
  8      -- copy rows in table using varray
  9
 10      insert into t1
 11      select * from t where rowid in (
 12          select chartorowid(column_value)
 13          from table(cast(x as arr_rowid))
 14          );
 15
 16      -- 2 more rows inserted
 17
 18      insert into t
 19      select * from (
 20          select rownum rn, a.* from all_objects a
 21          order by object_id)
 22      where rn > 10 and rn < 13;
 23
 24      -- and commited :(
 25
 26      commit;
 27
 28      -- now delete from table using varray
 29
 30      delete from t where rowid in (
 31          select chartorowid(column_value)
 32          from table(cast(x as arr_rowid))
 33          );

 34 end;
 35 /

PL/SQL procedure successfully completed.

SQL> select count(*) from t;

  COUNT(*)

---

         2

SQL> select count(*) from t1;

  COUNT(*)

---

        10
        

-- 
Martin Burbridge

Received on Wed Jul 23 2003 - 20:20:56 CDT