Re: isolation level serializable

On Mar 22, 12:53 am, Chuck <skilover_nos..._at_bluebottle.com> wrote:
> Laurenz Albe wrote:
> > Chuck <skilover_nos..._at_bluebottle.com> wrote:
> >> I have a stored procedure that copies a bunch of tables from one schema
> >> to another using "execute immediate 'insert...' " and code that looks
> >> something like this...
>
> >> for i in (select table_name from ....)
> >> loop
> >> stmt := 'insert into s1.'||table_name;
> >> stmt := stmt||' (select * from s2.'||table_name')';
> >> execute immediate stmt;
> >> end loop;
>
> >> Some of the tables have FK relationships between them on both the source
> >> and destination schemas. The procedure uses a similar loop early on to
> >> disable all constraints on the destination tables, and another later to
> >> enable them again after the inserts are done. The constraints stay
> >> enabled the entire time on the source tables.
>
> >> The problem is that I am getting FK constraint violations when I try to
> >> enable the constraints on the destination tables. I thought I could
> >> eliminate this by using a serializable isolation level for the
> >> transaction. According to the manual, this will cause all queries to
> >> look at a snapshot of the source tables from the same point in time, so
> >> I execute this before the first insert.
>
> >> commit;
> >> set transaction isolation level serializable;
>
> >> Then I do another commit at the end of the last insert. There are no
> >> commits, rollbacks, DDL, or any other SQL between the inserts, however I
> >> still get FK violations at the end when I try enable the constraints.
> >> Why? Am I misunderstanding the serializable isolation level? Is there
> >> something special about "execute immediate" where it ignores the
> >> serializable transaction setting?
>
> > You seem to have misunderstood the concept of serializable isolation.
>
> I think you misunderstood my question or maybe I didn't make it clear. I
> am copying data from source to destination tables. I am the only one
> accessing the destination tables. The *source* tables are constantly
> being modified and always have all constraints enabled. Therefore
> shouldn't using a serializable transaction present a my session with a
> consistent (repeatable read) view of the *source* tables, even though
> they are being modified?
>
> > This is something that only affects what you see when you look at the
> > table,
>
> Exactly. That's what I want - repeatable read on the source tables using
> a snapshot as of the beginning of the transaction.
>
> >but has no influence on what happens when you or somebody else
> > modify the table.
>
> Nobody else is modifying the destination tables. I have exclusive access
> to them.

I think that some unforeseen/unexpected implicit commit happens while the copying is en route and Oracle automatically reverts isolation level back to default READ COMMITTED. Without seeing actual code it's impossible to tell where this commit might be issued. For example, TRUNCATE is actually DDL and it implicitly commits, so if you do any truncates inside the copy loop this might be the cause.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Mar 22 2007 - 02:50:02 CDT