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

Reply via email to