Our application is causing deadlocks. After doing research I'm almost certain it is due to not having an index on a couple of foreign key columns. *** 2002-11-04 09:17:47.998 *** SESSION ID:(12.346) 2002-11-04 09:17:47.960 DEADLOCK DETECTED Current SQL statement for this session: delete from X where (col1 is null and col2=:b0) The following deadlock is not an ORACLE error. It is a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL.
The delete from X will cause a FULL TABLE lock on both A and B before it starts processing. If another session has a lock on a row in X that your delete will ultimately hit -- that will block your delete. If that other session then attempts to modify a row in either of A or B, it will deadlock. This script in 817 and before will demonstrate this: ----------------------------------------------------------------- set echo on drop table X cascade constraints; drop table A cascade constraints; create table X ( x int primary key ); create table A ( x references X ); insert into x values ( 1 ); insert into x values ( 2 ); commit; /* In another session, go and issue 'update x set x=2 where x=2' and come back here and hit enter */ pause /* this will block. When it does, goto the other session and issue 'insert into a values (2)' */ delete from x where x >0; -------------------------------------------------------------------- In 9i the behavior is a little different -- this particular example won't DEADLOCK as written -- but it will block and can still deadlock under many conditions. The duration of the child table lock is not for the length of the transaction in 9i (as in 8i and before) rather it is for the length DELETE statement itself. It reduces the scope of the problem -- does not REMOVE it.
How to analyze deadlocks. To find the relevant information in the ST22 dump and analyte the oracle alert.log and most important the oracle deadlock trace file. Experts Exchange >Questions >Analyzing a SQL trace file containing a Deadlock. The trace file and understanding why the deadlock is. Oracle, deadlocks. What are deadlocks? Family Cyber Alert V4.12. A deadlock is. Where the vast majority of the database activity is reading. Whenever a deadlock occurs, Oracle creates a trace file. Recently I got a ORA-00060 deadlock error. I read this post and. How to understand ORA-00060 deadlock trace file. Of a deadlock from a oracle 11g Rac trace.
If you have my book 'Expert one on one Oracle' -- I go into depth on when to index fkeys -- basically if you o update the parents primary key (some tools do this 'blindly' so watch out for that) OR delete from the parent o AND you modify the child then yes, you want to index the fkeys. And yes, the fkey columns need only be on the leading edge of some index - so the index can be 'bigger' then the fkey itself. And you rated our response. Followup November 07, 2002 - 12:31 pm UTC The transactions that update/delete the parent table DO manipulate A and B directly -- they LOCK them! You can get a little more info by taking: TM-000010da-00000000 11 12 SSX 33 31 SSX TM-0000000 33 31 SX 11 12 S and then select object_name, object_type from all_objects where object_id in ( to_number( '10da', 'xxxx' ), to_number( '0e72', 'xxxx' ) ); One session was waiting on TM-10da, the other on TM-0e72. Planetshakers When The Planet Rocked Rar.
That'll tell you the two tables involved here. This trace does not look like a simple deadlock as I have depicted, no - there was something else here. Maybe when you see the objects involved -- the pieces will start to fall into place. I looked up A and B in dba_objects. That's how I was able to create indexes on them and tell you their only relationship is that both have X as a parent. Learning this information only made the puzzle harder because the two tables have nothing to do with one another. One is a history of customer activities the other a list of purchase orders (made by the company).
Furthermore the site experiencing the problem does not have the purchase order module for the app. Given this scenario, I'm having a hard time figuring out what has to happen to cause the deadlock. >The transactions that update/delete the parent table DO >Tmnt Dark Horizons Download. manipulate A and B -- they LOCK them! So if I have two sessions going and both try to update the pk of X this could cause a deadlock?