This could work, if i were using hbm files. but as i told im using hibernate as ejb 3 persistence provider. Im not aware of any way to specify this with ejb3 annotations. Could you please reconsider your answer.
Regards, Milan Wölke -----Ursprüngliche Nachricht----- Von: Steve Ebersole [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 7. März 2006 19:01 An: Woelke, Milan; hibernate-devel@lists.sourceforge.net Betreff: RE: [Hibernate] bug or feature: oracle deadlock due to missing foreign key index Or, you could just specify an index on the <many-to-one/>: <class name="Position" ...> ... <many-to-one class="Order" column="order_id" index="IDX_FK_POSITION_ORDER" .../> </class> -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Woelke, Milan Sent: Tuesday, March 07, 2006 11:48 AM To: hibernate-devel@lists.sourceforge.net Subject: [Hibernate] bug or feature: oracle deadlock due to missing foreign key index Hi, I have a problem with hibernate bundled with jboss 4.0.4 as ejb 3.0 persistence provider. Before you skip my post, as just a misguided user question, please continue to read. First a scenario: We have two classes Order and Position, which are part of a bidirectional OneToMany relationship. Deletes are cascaded from Order to Position. Hibernate generates for these two classes two tables, Order and Position, where Position has a foreign key constraint to the primary key of Order. Both tables have an index on their primary keys. If I try to delete two Orders concurrently a deadlock at the db-level occurs. Its easy to reproduce this behavior: session 1: delete from Position where order_id=4711; session 2: delete from Position where order_id=4712; session 1: delete from Order where id=4711; session 2: delete from Order where id=4712; session 1: DEADLOCK What happens on the db level is something like that: session 1: rowlock on the position row to be deleted sharelock on the order table session 2: rowlock on the position row to be deleted sharelock on the order table session 1: tries to aquire rowlock for the order row to be deleted, waits since there is a sharelock on the table by another session session 2: tries to aquire rowlock for the order row to be deleted, waits since there is a sharelock on the table by another session session 1: since the other session waits for this session to release its sharelock on order and this session waits for the other session to release its sharelock, oracle detects a deadlock, and rolls back session 1. To avoid this situation its necessary to create an index on all columns with a foreign key constraint on it. Oracle then doesnt lock the whole order table but just the row connected to the deleted positions. For more information see: http://www.akadia.com/services/ora_locks_survival_guide.html (chapter Referential Integrity Locks) So, Im asking: Is this a bug? Or am I missing something? If it is a bug, I have a solution: It would be necessary to overwrite getAddForeignKeyConstraintString of org.hibernate.dialect.Oracle9Dialect with something like that: public String getAddForeignKeyConstraintString( String constraintName, String[] foreignKey, String referencedTable, String[] primaryKey, boolean referencesPrimaryKey ){ String pureFK = super.getAddForeignKeyConstraintString(constraintName, foreignKey, referencedTable, primaryKey, referencesPrimaryKey); StringBuffer buffer = new StringBuffer(pureFK); buffer.append(", add index (").append(StringHelper.join(", ", foreignKey)).append(")"); return buffer.toString(); } I guess I could do this myself, but I need some feedback to know that my analysis is correct. Also I dont know if such a solution would be acceptable for you. Ive posted this several times to different forums (one after one, im not doing any spam), but never received any replies to that. So please, please respond. Regards, Milan Wölke. ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=k&kid0944&bid$1720&dat1642 _______________________________________________ hibernate-devel mailing list hibernate-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/hibernate-devel ------------------------------------------------------- This SF.Net email is sponsored by xPML, a groundbreaking scripting language that extends applications into web and mobile media. Attend the live webcast and join the prime developer group breaking into this new coding territory! http://sel.as-us.falkag.net/sel?cmd=lnk&kid0944&bid$1720&dat1642 _______________________________________________ hibernate-devel mailing list hibernate-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/hibernate-devel
