Hibernate version: Hibernate 3 bundled with JBoss 4.0.4 as EJB 3.0 persistence provider
Mapping documents: none
Code between sessionFactory.openSession() and session.close(): n. a.
Full stack trace of any exception that occurs: n. a.
Name and version of the database you are using: Oracle 9i
The generated SQL (show_sql=true):n. a.
Debug level Hibernate log excerpt:n. a.
Hello,
Ive a deadlock problem with oracle and hibernate.
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:
Quote:
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
This happens since there is no index on the foreign key of position. What happens is the following:
Quote:
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_lock ... guide.html (chapter Referential Integrity Locks)
So, Im asking: Is this a bug? Or am I missing something?
If it is, I have an solution:
It would be necessary to overwrite getAddForeignKeyConstraintString of org.hibernate.dialect.Oracle9Dialect with something like that:
Code:
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. So please respond, please.
Regards, Milan Wölke.
Login
FAQ
Search