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
