Re: DEADLOCK AND TRACE FILE???

<Hi!,
<We have a problem with a sql*forms 3 application. Their is 7 users working on
<PC's and the server is a PS2 with SCO unix and Oracle 7....
<Sometime the system get very slow and it produce a BIG trace file
<(ora_xxx.trc) on the server telling that there is a deadlock.
<I paste here the first page of the trace:
<
<+-----------------------------------------------------------------------------+
<Dump file /spSta1/oracle/produit/7.0.12/rdbms/log/ora_380.trcORACLE7 Server
<Release 7.0.15.4.0 - ProductionWith the procedural and distributed
<optionsPL/SQL Release 2.0.17.1.0 - ProductionORACLE_HOME =
</spSta1/oracle/produit/7.0.12ORACLE_SID = spSta
<Oracle process number: 13 Unix process id: 380
<System name: ssp
<Node name: ssp
<Release: 3.2
<Version: 2
<Machine: i386
<
<Wed Aug 31 09:48:44 1994
<*** SESSION ID:(14.16)
<DEADLOCK DETECTED
<Current SQL statement for this session:
<INSERT INTO PERMISSION_STATIONNER( NUMERO_PERMIS,ANNEE_REFERENCE,ID_VEHICULE,NUMERO_INDIVIDU,DATE_ACTIVATION,DATE_EXPIRATION,NUMERO_DOCUMENT ) VALUES ( :b1,:b2,:b3,:b4,:b5,:b6,:b7 )
<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 following
<information may aid in determining the deadlock:
<Deadlock graph:
< ---------Blocker(s)-------- ---------Waiter(s)---------
<Resource Name process session holds waits process session holds waits
<TM-0000067a-00000000 14 13 SX SSX 20 18 SX SSX
<TM-0000067a-00000000 20 18 SX SSX 14 13 SX SSX
<===================================================
<PROCESS STATE
<-------------
<Process global information:
< process: 80415248, call: 80429518, xact: 80480f4c, curr sess: 8041c334, user sess: 8041c334
<+-----------------------------------------------------------------+
<AND SO ...
<
<My problem is that:
<
<1- I dont see how i can get a deadlock since users never
<access the same records;
<
<2- the trace file say that the deadlock is on INSERT INTO PERMISSION...: A
<deadlock on a INSERT???? Please explain me, I didn't know that a insert needed
<a lock...;
<
<3- Anybody know how to interpret the trace file?
<
<Please help!!!!
<Thank's
<
<Jean-Marc Boivin
<Universite Laval
<Quebec, PQ
<(Canada)
<jmboivin_at_cti.ulaval.ca

I have just struggled with this same problem and discovered the deadlock was the result of not having indexes on any foreign key fields in the child tables. In the testing that I did using two tables, a parent (master) and a child (detail), I could cause deadlocks to happen during inserts, updates and deletes. After adding the index to the foreign key field the problem did not happen again. Not sure if this fits your situation, but I thought I would pass on my experience with deadlocks.

                                          /==========\             8||8 
George Chernisky                      /==================\         8||8
Computer Specialist                 / <> <>    ss   <> <>  \       8||8
DLA Systems Automation Center       |======================|        ||
Database Support (DSAC-FMA)          |       |\__/|       |         ||
DSN 850-9476                         |_______|    |_______|         || 
COM (614)692-9476                       GONE  RACING !!!         [======]