Re: isolation level serializable

Laurenz Albe wrote:
> Chuck <skilover_nospam_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. Received on Wed Mar 21 2007 - 16:53:24 CDT