Oracle 11g hunting down the deadlock, maybe foreign key?

I am trying to understand why a deadlock occurs in my database. The trace tells me that two updates on the same table are locking. In table is the first table to be written to in an transaction, only some read occur before. The updates/inserts are done in a batch manner. The key, which is also used to identify the row which is to be updated, is indexed with a b+-tree. This key also acts in other tables as a foreign key. In these other tables the foreign key is allowed to be null.

The application disallows parallel transactions containing possible conflicts.

Here is the head of the trace file:

Deadlock graph:
    ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-134b0012-3201376f        65     185     X             33     392           S
TX-13495007-50f092d1        33     392     X             65     185           S

session 185: DID 0001-0012-04533045 session 392: DID 0001-0023-03401G5A 
session 392: DID 0001-0023-03401G5A session 185: DID 0001-0012-04533045 

Rows waited on:
Session 588: no row
Session 497: no row

Can the insert of a null value in a table referencing the key as a foreign key cause some kind of table scan (don't think so)? Can a batch insert/update maybe cause bigger branches of an index to be locked? Maybe T1 gets some left branch of the tree and T2 a right branch, and now T1 wants to to insert/update a value in the right branch, but the right branch is still locked by T2 and T" then requests something from the left side? I have got no idea if and how multi granularity locking is implemented in Oracle, but it would be nice if someone could rule this out.

Is there any possibility that Oracle allows deadlocks even if the transactions are 100% logically disjunct?

What could I be looking for? Is it some problem accessing the index?

P.S.: read committed is set.

Deadlocks in Oracle with logically disjoint transactions usually involve unindexed foreign keys:


There are two issues associated with unindexed foreign keys. The first is the fact that a table lock will result if you update the parent records primary key (very very unusual) or if you delete the parent record and the child's foreign key is not indexed.

Locks in Oracle are managed at the row level. Concurrent disjoint transactions should not interfere with one another. Unindexed foreign keys are an exception, since it can result in a complete TABLE LOCK.

You should get the SQL in the trace file of the deadlock and that should help you
narrow down which table / foreign key is responsible for the lock. Once you know which table is affected by the deadlock, make sure that all foreign key references to this table are properly indexed. E.G in your example T2.t1_id should be indexed if it points to T1.t1_id.

Alternatively you could use Tom Kyte's script from the above link to determine if you have any unindexed foreign key.

If you're locked on inserts, it usually means that you're trying to insert rows that have the same values for a set of columns that are UNIQUE, for example:

CREATE TABLE T1 (col1 NUMBER NOT NULL, col2 NUMBER);
ALTER TABLE T1 ADD CONSTRAINT T1_UNIQUE UNIQUE (col1, col2);

session1> INSERT INTO T1 VALUES (1, NULL);
session2> INSERT INTO T1 VALUES (2, NULL);
session2> INSERT INTO T1 VALUES (1, NULL); --  INSERT INTO T1 VALUES (2, NULL); -- <= Deadlock !

StackExchange Database Administrators Q#11010, answer score: 3