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.