Does a composite primary key(Foreign key + Primary key) need indexing to avoid deadlock ?
Example scenario:
One to many relationship(One employee can work in more than one department)
Parent table :
Employee
1.Employee ID(Primary key)
2.Name
Child table :
Department (Composite Primary key –> (Idx + Employee ID)
1.Idx(Primary key)
2.Department name
3.Employee ID(Primary key)
class Employee {
int Employee ID;
String name;
List departments;
}
class Department {
String departmentName;
}
<list cascade="all,delete-orphan" name="ListOfDepartment" table="Department">
<key column="Employee ID" />
<index column="idx"/>
<composite-element class="Department">
<property name="departmentName" column="departmentName" not-null="true" />
</composite-element>
</list>
Best Answer
1) A small description of
Deadlock
: It's a situation where two sessions block each other, such that neither can do anything. Deadlocks are detected and resolved automatically by the DIA0 background process.2)
Deadlocks are not the DBA’s problem
; they are caused by bad program design and resolved automatically by the database itself.3) They occur because the code attempts to do something that is logically impossible. The well-written code will always request locks in a sequence that cannot cause deadlocks to occur, or will test whether incompatible locks already exist before requesting them.
So,
you do not need
to think about creating anIndex
for your tables just because ofavoiding deadlocks
. It's better to ask your program designers to write a good code.BUT, if you think that there might be some wrong codes (it's better to fix them) OR you don't want WAITING on
Parent table
, thenCREATE INDEX
for yourFOREIGN KEY COLUMN
, cause if you will not do that you will haveWAITING
for theParent table
when some DML process ongoing inChild table
, try to do this example and you can understand:Now, we will
CREATE an INDEX
FOR THEFK ON emp
TABLE:try again:
Book: OCA-OCP 11g All-in-One (Deadlocks); Details: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html