12c
I asked in another post how to find out when a foreign key was created. We have 2 separate processes that are experiencing a number of deadlocks daily that have increased in the last while. I was asking to see whn a FK was created to we could try and pin it down to a particular FK,
T1 has 4 foreign keys, all indexed
T2 has 5 foreign keys, all indexed.
1 of the foreign keys on T1 and T2 looks at the same primary key on a third table T3.
T3 has 10 foreign keys of which 4 are unindexed. T1 or T2 do not reference these in constraints. They are unindexed due to legacy statements. easy job to index them but thats not the question here.
the deadlock graphs shows the following 2 statements causing the deadlock
select * from t1 where t1_id = :1 for update of t1_id -- where t1_id is the column with a fk to t3_id
select * from t2 where t2_id = :1 for update of t2_id -- where t2_id is NOT the fk to t3_id
I cant seem to see what the object is in the graph that causing the deadlock
I could understand if any of the foreign keys on t1 or t2 were unindexed but they all are so Im guessing its the unindexed FK's in T3. Now I could just index them and see if deadlocks go away but Id like to be able to see which one.
The reason I was looking for the date the FKs were created was I know the date the deadlocks started to increase, if I could match that up with a creation date of a FK I have my culprit.
Global Wait-For-Graph(WFG) for GES Deadlock ID=[14_0_462]
------------------------------------------------------------------------
Victim : (instance=1, lock=0x184483de80)
Start (master) Instance : 1
Number of Locks involved : 4
Number of Sessions involved : 2
User session identified by:
{
User Name : ddbuser1
User Machine : machine1
OS Terminal Name : xx
OS Process ID : 3564:4668
OS Program Name : app1.exe
Application Name : app1
Action Name : actionname
Current SQL : select * from t1 where t1_id = :1 for update of t1_id
Session Number : 3969
Session Serial Number : 17734
Server Process ORAPID : 1246
Server Process OSPID : 14602
Instance : 1
}
waiting for Lock 0x184483de80 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x7001e.0xb05321(ext 0x0,0x2)
GES Transaction ID : 4DE000-0001-00004F37
}
which is blocked by Lock 0x184483dc90 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x7001e.0xb05321(ext 0x0,0x2)
GES Transaction ID : 32C000-0001-00003BFC
}
owned by the
User session identified by:
{
User Name : ddbuser1
User Machine : machine1
OS Terminal Name : xx
OS Process ID : 3564:4860
OS Program Name : app2.exe
Application Name : app name
Action Name : Action2
Current SQL : select * from t2 where t2_id = :1 for update of t2_id
Session Number : 3508
Session Serial Number : 61881
Server Process ORAPID : 812
Server Process OSPID : 150940
Instance : 1
}
waiting for Lock 0x184483e468 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x370000.0x56a0c2(ext 0x0,0x2)
GES Transaction ID : 32C000-0001-00003BFC
}
which is blocked by Lock 0x184483e070 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x370000.0x56a0c2(ext 0x0,0x2)
GES Transaction ID : 4DE000-0001-00004F37
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[14_0_462]
====================================================================