Resolve Deadlock

[rshivagami]

I have the following deadlock error :

Current SQL Statement:
  UPDATE cum SET sc = :1 , scd = :2  WHERE c_id = :3 AND u_id = :4
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE cu SET o_id = :1  WHERE u_id = :2

Here is the index/constraints structures :
Table cum :
PK constraint on c_id & u_id combination with index
FK constraint on c_id without index
FK constraint on u_id without index
Nonunique Index on c_id

Table CU :
PK constraint on u_id with index
FK contraint on o_id without index

What could be the cause ? Would adding an index to the FK constraint cum.u_id help ?

And this is not reproducible either. How do I verify it ?

Thanks.

[jhmartin]

You only need an index on the parent side of a FK constraint, and it is usually a good idea to prevent locking issues. However, no two statements can cause a deadlock, as one would just wait. You need to look at what those sessions are doing around the time of those two statements.

[PAVB]

You have either to create indexes on your FK constraints or disable table locks on tables that have indexless FK constraint.

[jhmartin]

I didn't think it was possible to disable table locks in that situation.

[rshivagami]

I cannot disable table locks as that table is being heavily used. So do I need an index on the child side of a FK constraint also ?

[jhmartin]

Operations on the parent benefit from a index on the child. Operations on the child benefit from an index on the parent.

[PAVB]

Metalink Note 15476.1 reads: "...it is recommended to have indexes on the foreign key columns of the child tables in order to avoid this additional locking activity..."

By the way, "ALTER TABLE TABLE_NAME DISABLE TABLE LOCK" does not disables row level transactional locking.

[rshivagami]

ok. makes sense.
Thanks everyone.