openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Craig Russell (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OPENJPA-235) SQL reordering to avoid non-nullable foreign key constraint violations
Date Thu, 07 Jun 2007 22:49:26 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12502561
] 

Craig Russell commented on OPENJPA-235:
---------------------------------------

> Would it be sufficient to just always run the deletes first, then the updates, and finally
the inserts, when there are unique constraints involved? Assuming that we optimize away any
SQL operations for insert-then-delete-before-flush, it would seem that that algorithm would
be guaranteed to work when no constraint violations were generated in the business code, which
I think is all that we can really strive for in the case of unique constraints.

This algorithm isn't quite enough. Consider deleting an instance that has a reference from
an instance that is being updated to nullify that reference. Then you want to do the update
to nullify the reference before you do the delete.

I think the general issue is that due to uniqueness and referential integrity constraints
you need to create a dependency graph and then order the operations to preserve the dependencies.
But you also want to preserve the order the user made the changes to avoid deadlocks due to
locking dependencies. And you also want to reorder operations to take advantage of statement
batching.


> SQL reordering to avoid non-nullable foreign key constraint violations
> ----------------------------------------------------------------------
>
>                 Key: OPENJPA-235
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-235
>             Project: OpenJPA
>          Issue Type: Improvement
>          Components: kernel
>            Reporter: Reece Garrett
>            Assignee: Patrick Linskey
>             Fix For: 0.9.8
>
>         Attachments: merge-detached.patch, merge-multigen-collection-testcase.zip, openjpa-235-test.jar,
openjpa-235-test1.jar, openjpa-235-test2.zip, sqlreorder.patch, sqlReorder2.patch, sqlReorderTests.patch
>
>
> OpenJPA does not do any SQL statement re-ordering in order to resolve foreign key constraints.
Instead, objects are always inserted in the order in which the user persists the instances.
 When you persist in an order that would violate foreign key constraints, OpenJPA attempts
to insert null and then update the foreign key value in a separate statement. If you use non-nullable
constraints, though, you must persist your objects in the correct order.
> This improvement re-orders SQL statements as follows:
> 1. First, all insert statements execute. Inserts which have foreign keys with non-nullable
constraints execute AFTER the foreign keys which they depend on have been inserted since no
deferred update is possible.
> 2. Next, all update statements execute. No reordering is necessary.
> 3.  Finally, all delete statements execute. Like inserts, deletes execute in an order
which does not violate non-nullable foreign key constraints.
> If a circular foreign key reference is found during the re-ordering process then re-ordering
halts and the remaining unordered statements are left as is. There is nothing that can be
done about the circular reference (other than fixing the schema) and the resulting SQL statements
will not succeed.
> The net effect is that users do not need to worry about the persistence order of their
objects regardless of non-nullable foreign key constraints. The only class modified was org.apache.openjpa.jdbc.kernel.OperationOrderUpdateManager.
I have included a patch which includes my modifications to OperationOrderUpdateManager and
test cases. The test cases I have provided fail on the current trunk but pass with my modifications.
I have also verified that I did not break anything by using maven to run all test cases with
my modifications in place.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message