Hi,
We are getting deadlocks on an INSERT statement. We have also been able to re-produce the deadlock with just one session running to rule out any possibility of concurrent sessions causing any deadlocks.
The database version details are -->
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Some more details around the deadlock.
1. The stored procedure which hits the deadlock has a PRAGMA AUTONOMOUS in it, which we know starts a new child transaction. So its not 2 sessions but 2 transactions causing the deadlock.
2. The SQL giving the deadlock error - ORA-00060 is an INSERT statement.
3. Inside the insert statement a sequence.NEXTVAL is done to set a value for a primary key field.
4. The procedure also has a COMMIT inside it.
5.Ours is a RAC with 2 nodes.
6. The sequence used in INSERT is cached with a value of 20.
7. The lock mode shown in deadlock trace file is 3.
8. Grant_Bits: - KJUSERNL, KJUSEREX
9.Granted_locks: - 1
10. Cvting_locks: - 1
Queries: -
1. Referring some blogs on internet (for single instance deadlock graphs) we know there are ways to determine if this is a deadlock caused due to overlapping primary keys. (typically happening in INSERT.) however the deadlock graph details in our lmd0 file are very difficult to interpret. Are there are any pointers/messages we need to look for to know the exact cause of the deadlock?
2. As in a single instance deadlock graph file we can see the row section where it shows "Rows waited on", where can such information be seen in a deadlock graph for a RAC?