[JBoss-dev] need Oracle deadlock help

[Bill Burke]

I'm getting the following Oracle deadlock detection.  I can't seem to 
figure out why it is happening or how I can do the SQL call to order the 
row locks of the Inventory table.  Anybody got any hints?  I'm making 
the correct assumption that the two sessions that are deadlocking are 
making the same SQL call, correct?  Thanks in Advance,

Bill

DEADLOCK DETECTED
Current SQL statement for this session:
SELECT t0_a.b_assembly_id, t0_a.b_comp_id, t0_a.b_line_no, 
t0_a.b_assembly_id, t0_a.b_comp_id, t0_a.b_line_no, t0_a.b_qty, 
t0_a.b_eng_change, t0_a.b_ops, t0_a.b_ops_desc, component.p_id, c\
omponent.p_name, component.p_desc, component.p_rev, component.p_planner, 
component.p_type, component.p_ind, component.p_lomark, 
component.p_himark, inventory.in_p_id, inventory.in_qty, inv\
entory.in_location, inventory.in_acc_code, inventory.in_act_date, 
inventory.in_ordered FROM M_BOM t0_a LEFT OUTER JOIN M_PARTS component 
ON t0_a.b_comp_id=component.p_id LEFT OUTER JOIN M_\
INVENTORY inventory ON component.p_id=inventory.in_p_id WHERE 
(t0_a.b_assembly_id = :1) ORDER BY inventory.in_p_id FOR UPDATE OF 
inventory.in_p_id, inventory.in_qty, inventory.in_location,\
 inventory.in_acc_code, inventory.in_act_date, inventory.in_ordered
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-00140001-000002d2        15     151     X             13     329 
      X
TX-00e20005-000001f7        13     329     X             15     151 
      X
session 151: DID 0001-0076-00000001     session 329: DID 0001-0070-00000001
session 329: DID 0001-0070-00000001     session 151: DID 0001-0076-00000001
Rows waited on:
Session 329: obj - rowid = 00001C5B - AAAFe5AAKAAAAB/AAK
  (dictionary objn - 7259, file - 10, block - 127, slot - 10)
Session 151: obj - rowid = 00001C5B - AAAFe5AAKAAAACAAAK
  (dictionary objn - 7259, file - 10, block - 128, slot - 10)
Information on the OTHER waiting sessions:
Session 329:
  pid=13 serial=61 audsid=99742 user: 23/SPEC
  O/S info: user: wburke, term: unknown, ospid: , machine: xeon
            program: JDBC Thin Client
  Current SQL Statement:

SELECT t0_a.b_assembly_id, t0_a.b_comp_id, t0_a.b_line_no, 
t0_a.b_assembly_id, t0_a.b_comp_id, t0_a.b_line_no, t0_a.b_qty, 
t0_a.b_eng_change, t0_a.b_ops, t0_a.b_ops_desc, component.p_id, c\
omponent.p_name, component.p_desc, component.p_rev, component.p_planner, 
component.p_type, component.p_ind, component.p_lomark, 
component.p_himark, inventory.in_p_id, inventory.in_qty, inv\
entory.in_location, inventory.in_acc_code, inventory.in_act_date, 
inventory.in_ordered FROM M_BOM t0_a LEFT OUTER JOIN M_PARTS component 
ON t0_a.b_comp_id=component.p_id LEFT OUTER JOIN M_\
INVENTORY inventory ON component.p_id=inventory.in_p_id WHERE 
(t0_a.b_assembly_id = :1) ORDER BY inventory.in_p_id FOR UPDATE OF 
inventory.in_p_id, inventory.in_qty, inventory.in_location,\
 inventory.in_acc_code, inventory.in_act_date, inventory.in_ordered
End of information on OTHER waiting sessions.
===================================================
P

--
================
Bill Burke
Chief Architect
JBoss Group LLC.
================


-------------------------------------------------------
This SF.net email is sponsored by: The SF.net Donation Program.
Do you like what SourceForge.net is doing for the Open
Source Community?  Make a contribution, and help us add new
features and functionality. Click here: http://sourceforge.net/donate/
_______________________________________________
JBoss-Development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development