1 Reply Latest reply on Mar 3, 2006 5:51 AM by milan wölke

    Missing index on foreign keys in oracle

    milan wölke Newbie

      Hello,

      I have a deadlock problem with oracle.

      I have two entity beans, lets say "Order" and "OrderPos". There is a bidirectional OneToMany relationship between Order and OrderPos.

      @Entity
      public class Order{
      long id;
      Set<OrderPos> positions;
      @Id
      @GeneratedValue(...)
      public long getId(){...}
      public void setId(long id){...}
      
      @OneToMany(cascade=CascadeType.ALL, mappedBy="order")
      public Set<OrderPos> getPositions(){...}
      public void setPositions(Set<OrderPos> positions){...}
      }
      

      public class OrderPos{
      long id;
      Order order;
      @Id
      @GeneratedValue(...)
      public long getId(){...}
      public void setId(long id){...}
      
      @ManyToOne
      public Order getOrder(){...}
      public void setOrder(Order order){...}
      }
      


      For these two classes hibernate generated two tables: Order and OrderPos.
      For the Order table it generates a primary key constraint as well as an index on the primary key. For OrderPos it generates a primary key constraint, a foreign key constraint for the orderid, and an index on the primary key.

      If I try to delete two Orders with all their Positions concurently, I get a deadlock. This is the sql generated by hibernate to do this.

      session 1:
       delete from OrderPos where order_id = 4711;
      session 2:
       delete from OrderPos where order_id = 4712;
      session 1:
       delete from Order where id = 4711;
      session 2:
       delete from Order where id = 4712;
      


      With this scenario session 1 receives a deadlock.
      Investigating this problem I found, that oracle is expecting an index on all foreign keys of a table to prevent this situation.

      Is there a way to have hibernate generate these indexes? Or is there another way to accomplish this kind of concurrent deletes?

      Please help.

      Thanx in advance.

      PS: Im using default JBoss 4.0.4 RC1; Oracle 9i