Re: isolation level serializable

Laurenz Albe <invite_at_spam.to.invalid> 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;

I don't like that. Each transaction should commit or rollback itself when it is done. I don't think it should be committed implicitly by the start of some other transaction.

> >
> > 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?

To know why, it would help to know what. What is the problem that is causing the violation? Are you absolutely sure that the corresponding constraint is in place and enabled and set to validate on the source table?

Is your process the only one that writes into the s1 schema or can other people be messing around with it at the same time as you are?

> > 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.
>
> This is something that only affects what you see when you look at the
> table, but has no influence on what happens when you or somebody else
> modify the table.

If you modify a table based on what you see in another table, then obviously it does have an effect on how you modify the table.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB