Understanding Deadlock Trace File.

Hi,

The following is a deadlock trace file. Can somebody please help me in understanding: what are the two processes (corresponding programs also), and what are the two rows (of which tables also) involved in the deadlock and how to avoid this deadlock.


----------------------------------------------------------------
*** SESSION ID:(265.45954) 2005-08-27 11:50:02.315
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE task_list t
SET t.task_status = '01',
t.user_hold_code = '00',
t.userid = 'yantra',
t.mod_program_id = 'PCKTASKREL'
WHERE t.whse = '80'
AND t.work_type IN ('32','33','34','72','78')
AND t.task_status = '06'
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
TX-0033000c-00021663 358 265 X 592 527 X
TX-006c000f-000161b0 592 527 X 358 265 X
session 265: DID 0001-0166-00044631 session 527: DID 0001-0250-00020DCB
session 527: DID 0001-0250-00020DCB session 265: DID 0001-0166-00044631
Rows waited on:
Session 527: obj - rowid = 00008A21 - AAAIohAA2AAAstHAAM
(dictionary objn - 35361, file - 54, block - 183111, slot - 12)
Session 265: obj - rowid = 00008A21 - AAAIohAA2AAAstHAAP
(dictionary objn - 35361, file - 54, block - 183111, slot - 15)
Information on the OTHER waiting sessions:
Session 527:
pid=592 serial=64994 audsid=45813022 user: 29/DCS80
O/S info: user: mschenk, term: , ospid: 18617, machine: trout
program: wh446@trout (TNS V1-V3)
application name: WMS_YANTRA , hash value=3277151378
action name: 80, hash value=4282178430
Current SQL Statement:

UPDATE TASK_LIST
SET
WHSE =
SUBSTR(:b4, 1, 5)
,TASK_ID =
TO_NUMBER(NVL(SUBSTR(:b4,6,11), '0'))
,WAIT_TASK_ID =
TO_NUMBER(NVL(SUBSTR(:b4,17,11), '0'))
,TASK_STATUS =
SUBSTR(:b4, 28, 2)
,REFERENCE_1 =
NVL(RTRIM(SUBSTR(:b4, 30, 50)),' ')
,USER_HOLD_CODE =
SUBSTR(:b4, 80, 2)
,DEPENDENT_TASK_TO_USER =
SUBSTR(:b4, 82, 1)
,SOURCE_LOCATION =
SUBSTR(:b4, 83, 20)
,TARGET_LOCATION =
SUBSTR(:b4, 103, 20)
,PUTAWAY_SEQUENCE_NO =
TO_NUMBER(NVL(SUBSTR(:b4,123,10), '0'))
,PICK_SEQUENCE_NO =
TO_NUMBER(NVL(SUBSTR(:b4,133,10), '0'))
,COUNT_ZONE =
SUBSTR(:b4, 143, 2)
,EQUIPMENT_CAPABILITY_CODE =
SUBSTR(:b4, 145, 4)
,TARGET_WIDTH_CODE =
SUBSTR(:b4, 149, 4)
,EQUIPMENT_ASSIGNED =
SUBSTR(:b4, 153, 2)
,TASK_PRIORITY =
SUBSTR(:b4, 155, 6)
,REFERENCE_TYPE =
SUBSTR(:b4, 161, 2)
,REFERENCE_KEY =
NVL(RTRIM(SUBSTR(:b4, 163, 50)),' ')
,USER_ASSIGNED =
SUBSTR(:b4, 213, 8)
,REFERENCE_3 =
NVL(RTRIM(SUBSTR(:b4, 221, 50)),' ')
,FUNCTION_ID =
SUBSTR(:b4, 271, 8)
,WORK_TYPE =
SUBSTR(:b4, 279, 2)
,ASSIGNED_PID =
TO_NUMBER(NVL(SUBSTR(:b4,281,10), '0'))
,REFERENCE_2 =
NVL(RTRIM(SUBSTR(:b4, 291, 50)),' ')
,SOURCE_AREA =
SUBSTR(:b4, 341, 2)
,TARGET_AREA =
SUBSTR(:b4, 343, 2)
,TASK_SKIPPED_COUNT =
TO_NUMBER(NVL(SUBSTR(:b4,345,5), '0'))
,USER_ASSIGN_MANUAL =
SUBSTR(:b4, 350, 1)
,TARGET_HEIGHT_CODE =
SUBSTR(:b4, 351, 4)
,TASK_ASSIGN_REASON =
TO_NUMBER(NVL(SUBSTR(:b4,355,3), '0'))
,ITEM_ID =
NVL(RTRIM(SUBSTR(:b4, 358, 40)),' ')
,OEM =
SUBSTR(:b4, 398, 3)
,SOURCE_HEIGHT_CODE =
SUBSTR(:b4, 401, 4)
,SOURCE_WIDTH_CODE =
SUBSTR(:b4, 405, 4)
,WORK_TYPE_PRIORITY =
SUBSTR(:b4, 409, 6)
,ITEM_SORT_FIELD =
NVL(RTRIM(SUBSTR(:b4, 415, 40)),' ')
,LOADING_SEQ_NO =
TO_NUMBER(NVL(SUBSTR(:b4,455,10), '0'))
,REFERENCE_PRIORITY =
SUBSTR(:b4, 465, 6)
,PHY_INVENTORY_SEQ_NO =
TO_NUMBER(NVL(SUBSTR(:b4,471,10), '0'))
,PRODUCT_CLASS =
SUBSTR(:b4, 481, 6)
,PACK_TYPE =
SUBSTR(:b4, 487, 4)
,QUALITY_STATUS =
SUBSTR(:b4, 491, 2)
,SOURCE_AREA_GROUP =
SUBSTR(:b4, 493, 2)
,TARGET_AREA_GROUP =
SUBSTR(:b4, 495, 2)
,ORACLE_SID =
TO_NUMBER(NVL(SUBSTR(:b4,497,16), '0'))
,ORACLE_SERIAL_NO =
TO_NUMBER(NVL(SUBSTR(:b4,513,16), '0'))
,BATCH_NO =
SUBSTR(:b4, 529, 10)
,WAVE_NO =
SUBSTR(:b4, 539, 4)
,WAVE_DATE =
TO_NUMBER(NVL(SUBSTR(:b4,543,9), '0'))
,CREATE_TIME_STAMP =
TO_DATE(NVL(SUBSTR(:b4, 552, 14),'0'),'YYYYMMDDHH24MISS')
,MODIFY_TIME_STAMP =
TO_DATE(NVL(SUBSTR(:b4, 566, 14),'0'),'YYYYMMDDHH24MISS')
,USERID =
SUBSTR(:b4, 580, 8)
,MOD_PROGRAM_ID =
SUBSTR(:b4, 588, 10)
WHERE TASK_LIST.WHSE =
:b3
AND TASK_LIST.TASK_ID =
:b2
AND (:b1 = ' '
OR TASK_LIST.MODIFY_TIME_STAMP =
TO_DATE(:b1,'YYYYMMDDHH24MISS'))
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 50441d1c0, call: 52797d518, xact: 507205550, curses: 5053c6fb8, usrses: 5053c6fb8
----------------------------------------
SO: 50441d1c0, type: 2, owner: 0, flag: INIT/-/-/0x00

- - - - - - - - -
- - - - - - - - -

------------------------------------------------------------------

Can you please also tell me what is 'Session DID', how to interpret the actual resource from TX-0033000c-00021663.

Thanks,
Venkat.