Re: isolation level serializable

xhoster@xxxxxxxxx wrote:
Laurenz Albe <invite@xxxxxxxxxxxxxxx> wrote:
Chuck <skilover_nospam@xxxxxxxxxxxxxx> 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.

That's precisely what I am doing. The commit is only there because prior
to bulk of the inserts is another insert into a log table. It just says
"I'm at such and such point in the procedure". Prior to that was a bunch
of truncates which do implicit comiits. If I don't commit at this point,
the "set transaction" will fail because it must be the first command of
the 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?

As stated in the OP, it is a foreign key constraint violation on one the
destination tables. A child row has no parent.

Are you absolutely sure that the corresponding
constraint is in place and enabled and set to validate on the source table?

Yes. Absolutely.


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?

My session is the only one that has access to the s1 schema.
.

Relevant Pages

  • Re: How to update if exists else Insert in one SQL statement
    ... The update loop is harder - the only way to prevent ... UPDATE Dest ... ROLLBACK TRANSACTION ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: isolation level serializable
    ... end loop; ... on to disable all constraints on the destination tables, ... set transaction isolation level serializable; ...
    (comp.databases.oracle.server)
  • Re: isolation level serializable
    ... to another using "execute immediate 'insert...' ... on to disable all constraints on the destination tables, ... set transaction isolation level serializable; ... Each transaction should commit or rollback itself when ...
    (comp.databases.oracle.server)
  • Re: Using ADODB.Connection Execute and commit
    ... If no transaction is opened than each statement is ... committed automatically, but if transaction is opened before the loop, then ... >> Val Mazur ... > would commit each transaction as it is executed. ...
    (microsoft.public.vb.database.ado)
  • Re: Using ADODB.Connection Execute and commit
    ... I agree with Al Reid... ... there is no automatic commit. ... > would commit each transaction as it is executed. ...
    (microsoft.public.vb.database.ado)