Oracle Deadlock

Question

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>

Answer 1

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 an Index for your tables just because of avoiding 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, then CREATE INDEX for your FOREIGN KEY COLUMN, cause if you will not do that you will have WAITING for the Parent table when some DML process ongoing in Child table, try to do this example and you can understand:

        CREATE TABLE dept (
        dept_id NUMBER PRIMARY KEY,
        dept_name VARCHAR2(25)
        );

        CREATE TABLE emp (
        emp_id NUMBER PRIMARY KEY,
        emp_name VARCHAR2 (25),
        dept_id NUMBER REFERENCES dept (dept_id)
        );

        INSERT INTO dept VALUES (10, 'IT');
        INSERT INTO dept VALUES (20, 'Market');
        COMMIT;

    SESSION1: INSERT INTO emp VALUES (1, 'Alex', 10);
    SESSION2: DELETE FROM dept WHERE dept_id = 20; --- different dept_id, not the same with SESSION1 data

RESULT: Here you get `THE LOCK`, so `SESSION2 waiting` FOR `SESSION1 COMMIT`;

Now, we will CREATE an INDEX FOR THE FK ON emp TABLE:

CREATE INDEX emp_dept_id_idx ON emp (dept_id);

try again:

SESSION1: INSERT INTO emp VALUES (1, 'Alex', 10);
SESSION2: DELETE FROM dept WHERE dept_id = 20;

RESULT: Now NO WAITING, because the `Parent table` do not need to do `full table` scan and `LOCK whole table`;

Book: OCA-OCP 11g All-in-One (Deadlocks); Details: http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html