Search code examples
oraclehibernatedatabase-deadlocks

Does shared index on Oracle table leads to deadlock?

I am aware that unindexed foreign key in a child table can potentially lead to ORA-00060: deadlock. Similarly can a shared index can cause deadlock.

I have shared index in the following scenario,

class TableA {
int tableAId;
List<TableB> listOfTableB;
}

class TableB {
String name;
}

Related hibernate mapping (partial),

<list cascade="all,delete-orphan" name="listOfTableB" table="TableB">
        <key column="tableAId" />
        <index column="idx"/>
        <composite-element class="TableB">
            <property name="name" column="name" not-null="true" />
        </composite-element>
</list>

Using the above code, in DB it is creating a shared index (index of same name) for 'tableAId' and 'idx' in TableB.

In this scenario should I add a separate index for 'tableAId' alone inaddition to existing shared index to avoid deadlock ? Or the existing index itself is enough?

Solution

  • If you have an index on tableAId, idx then there is probably (almost always) no benefit in also having an index on tableAid alone. There is no danger of deadlock from this.