My job is failing with deadlock. while inserting data in to oracle table in api mode and in parallel. before inset there are updates happening on the table. Since insert operation don't lock any other rows what could be the possible causes for a dead lock?
Will increasing the commit number for the insert help?
There are several ways to get into a deadlock situation in a scenario like this.
It isn't correct that an insert doesn't take out locks. It locks the table in a mode that prevents any exclusive table locks, and if there's FK on any columns it will lock the parent rows in the parent tables to prevent them from being deleted.
Inserts like any other DML have to hold an ITL (interested transaction list) slot in the block, and if it is inserting into a preexisting block (non-append mode), it is possible that a shortage of ITL slots and free space to create more could lead to it waiting on other DMLs (like those updates) which can in turn have found themselves in a similar situation in another block, due to the inserts. So you can end up with a circular locking situation and Oracle will detect and fail one.
If you are in Exadata using HCC (hybrid columnar) compression without the ACO license there is only one lock bit per compression unit (CU) instead of one per row. Concurrent updates (whether by separate threads or with PDML) will almost certainly deadlock among themselves as a result.
Your insert may be using direct path mode, which requires an exclusive table lock, making it incompatible with other DMLs running against the table at the same time. If the other sessions holding any kind of lock whatsoever on the table are likewise blocked by your inserts, you have circularity.
If your updates are inserting entries into indexes, they likewise requires ITL entries in the index leaf blocks. You can get into the same situation with another process as explained above for table block ITLs.
If the updates are updating columns that are part of a unique key, Oracle won't allow an insert that could create a duplicate until the transaction is done.
You could have a trigger on the table logging those updates in some other table somewhere, and also trying to log the inserts. If that logging is at a different granularity than the main table both could end up updating the same logging rows, and again you have blocking.
And if I kept brainstorming I could probably come up with a few more scenarios. The point is, there are a lot of ways that an insert can be blocked by an update, and any blocking can result in a deadlock if a circular chain develops. You must determine the actual cause of the deadlock to know how to fix it. Check the trace file that is generated by the deadlock condition and use it to identify the chain. The fix will depend on what exactly is happening, which will require you to compare that trace file with your knowledge of the actual code those processes are running.
