We have been facing deadlock as part of bulk insert. We have 5 session run in parallel and insert 10000 records (Bulk Insert).
All the session have there own unique key.
Table is composite partition table (Range then List). Table does not have any foreign key. Table have one unique key index.
All 5 session inserting data into same sub-partition.
Session 5 - waiting on library cache lock
session 599 - waiting on enq: TM - contention
Apart from this, we have one more session which truncate another sub-partition on same table. But it completed 3 minute before the deadlock at 2017-08-29 04:33:24.017.
Could you please help what could be reason of this deadlock. Does this related to concurrent insert.
Second - What is the event "enq: IV - contention".
From deadlock trace file :
*** 2017-08-29 04:33:24.017
-------------------------------------------------------------------------------
DEADLOCK DETECTED (id=0x9e3f5a0f)
Chain Signature: 'enq: TM - contention'<='library cache lock' (cycle)
Chain Signature Hash: 0x8c262a57
-------------------------------------------------------------------------------
Oracle session identified by:
{
instance: 3 (goldp_exa.goldp3)
os id: 85353
process id: 288, oracle@
session id: 5
session serial #: 23239
}
is waiting for 'library cache lock' with wait info:
{
p1: 'handle address'=0x1dbf3ecda8
p2: 'lock address'=0x1eef6662f0
p3: '100*mode+namespace'=0xde983b00010003
time in wait: 1.587241 sec
timeout after: 14 min 58 sec
wait id: 57768
blocking: 1 session
current sql: INSERT INTO RR_TRADE TRADE_ELIG ( TRADE_ELIG_ID , AS_OF_DATE , PORTFOLIO_ID , PRODUCT_TYPE , SOURCE_ENV , BDR_CLIENT_ID , TRADE_ID , RED_ID , RED_VERSION_ID , CSA_IM_ID , CSA_VM_ID , LEGACY_CSA_ID , DFA_IM_FLAG , DFA_VM_FLAG , DFA_PR_IM_FLAG , DFA_PR_VM_FLAG , DFA_CFTC_IM_FLAG , DFA_CFTC_VM_FLAG , EMIR_IM_
wait history:
* time between current wait and wait #1: 0.000207 sec
1. event: 'enq: TM - contention'
time waited: 0.000126 sec
wait id: 57767 p1: 'name|mode'=0x544d0006
p2: 'object #'=0xde983b
p3: 'table/partition'=0x0
* time between wait #1 and #2: 0.000061 sec
2. event: 'enq: IV - contention'
time waited: 0.000246 sec
wait id: 57766 p1: 'type|mode'=0x49560005
p2: 'id1'=0x53594e43
p3: 'id2'=0x9
* time between wait #2 and #3: 0.000057 sec
3. event: 'enq: IV - contention'
time waited: 0.000526 sec
wait id: 57765 p1: 'type|mode'=0x49560005
p2: 'id1'=0x4c4f434b
p3: 'id2'=0x9
}
and is blocked by
=> Oracle session identified by:
{
instance: 3 (goldp_exa.goldp3)
os id: 78123
process id: 203, oracle@
session id: 599
session serial #: 7615
}
which is waiting for 'enq: TM - contention' with wait info:
{
p1: 'name|mode'=0x544d0006
p2: 'object #'=0xde983b
p3: 'table/partition'=0x0
time in wait: 1.586055 sec
timeout after: 9 min 58 sec
wait id: 40310
blocking: 1 session
current sql: INSERT INTO RR_TRADE TRADE_ELIG ( TRADE_ELIG_ID , AS_OF_DATE , PORTFOLIO_ID , PRODUCT_TYPE , SOURCE_ENV , BDR_CLIENT_ID , TRADE_ID , RED_ID , RED_VERSION_ID , CSA_IM_ID , CSA_VM_ID , LEGACY_CSA_ID , DFA_IM_FLAG , DFA_VM_FLAG , DFA_PR_IM_FLAG , DFA_PR_VM_FLAG , DFA_CFTC_IM_FLAG , DFA_CFTC_VM_FLAG , EMIR_IM_
wait history:
* time between current wait and wait #1: 0.000029 sec
1. event: 'enq: IV - contention'
time waited: 0.000315 sec
wait id: 40309 p1: 'type|mode'=0x49560005
p2: 'id1'=0x53594e43
p3: 'id2'=0x9
* time between wait #1 and #2: 0.000033 sec
2. event: 'enq: IV - contention'
time waited: 0.000734 sec
wait id: 40308 p1: 'type|mode'=0x49560005
p2: 'id1'=0x4c4f434b
p3: 'id2'=0x9
* time between wait #2 and #3: 0.000077 sec
3. event: 'library cache lock'
time waited: 0.011752 sec
wait id: 40307 p1: 'handle address'=0x1dbf3ecda8
p2: 'lock address'=0x1e3c471ec8
p3: '100*mode+namespace'=0x10003
}
and is blocked by the session at the start of the chain.