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