Home » SQL & PL/SQL » SQL & PL/SQL » Why Deadlock occured (Oracle 9.2.0.3)
Why Deadlock occured [message #361183] Tue, 25 November 2008 05:24 Go to next message
avik2009
Messages: 61
Registered: November 2008
Member

What is wrong with the procedure that causes deadlock.
What need to be checked? Is there anything wrong that I have written in this procedure?


PROCEDURE sp_get_info  ( w_order_id IN workorder.w_id%TYPE,
    RC1 OUT sys_refcursor )
AS
  v_id obj_details.object_id%TYPE;
  v_sub_id obj_details.sub_obj_id%TYPE;
  
  
  
BEGIN
  OPEN RC1 FOR


WITH subq AS
  (SELECT DISTINCT ci.sub_obj_id,
    ci.path_id
     FROM sub_item ci
    WHERE ci.w_id      = w_order_id
  AND ci.sub_item_code = 'A'
  AND ci.updt_tms      =
    (SELECT MAX (updt_tms)
       FROM sub_item si
      WHERE si.sub_obj_id = ci.sub_obj_id
    AND si.path_id        = ci.path_id
    )
  )
 SELECT subq.sub_obj_id,
  subq.path_id         ,
  status_code          
   FROM obj_details    ,
  subq
  WHERE obj_details.sub_obj_id = subq.sub_obj_id
AND obj_details.path_id        = subq.path_id
AND row_ins_tms                =
  (SELECT MAX (row_ins_tms)
     FROM obj_details x,
    subq
    WHERE x.sub_obj_id = subq.sub_obj_id
  AND x.path_id        = subq.path_id
  );
END sp_get_info;

Re: Why Deadlock occured [message #361186 is a reply to message #361183] Tue, 25 November 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A select can't cause a deadlock as it takes no lock.

Regards
Michel
Re: Why Deadlock occured [message #361190 is a reply to message #361183] Tue, 25 November 2008 05:33 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure this procedure is giving you a deadlock, because I rather doubt that it is?
Deadlocking can only occur when you lock rows in a table and this procedure doesn't do that.
Re: Why Deadlock occured [message #361193 is a reply to message #361190] Tue, 25 November 2008 05:47 Go to previous messageGo to next message
avik2009
Messages: 61
Registered: November 2008
Member
A message has been came in my way that this procedure is causing deadlock.

Do I need to ask to check v$lock, v$session by running below query



select username,
          v$lock.sid,
              trunc(id1/power(2,16)) rbs,
               bitand(id1,to_number('ffff','xxxx'))+0 slot,
               id2 seq,
           lmode,
               request
    from v$lock, v$session
   where v$lock.type = 'TX'
     and v$lock.sid = v$session.sid
    and v$session.username = USER
/



Re: Why Deadlock occured [message #361197 is a reply to message #361193] Tue, 25 November 2008 06:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I too think something is going on that we're not being told about.

There is nothing in that procedure that will establish any sort of lock other than transient latches to ensure multiversion concurrency.

SELECT statements establish no lock that show up in v$lock, or that cause deadlocks, unless something very very odd is going on.

Can you post the error message?

Thought - is this error happenning when you execute the procedure, or when you compile it?
Re: Why Deadlock occured [message #361199 is a reply to message #361193] Tue, 25 November 2008 06:22 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can check what you want but the deadlock DOES NOT come from this procedure.
You should better check if you really have a deadlock, what make you think you have one?

Regards
Michel
Previous Topic: ORA-12015: can not create fast refresh materialized view from complex query
Next Topic: Status: "301 Moved Permanently" internally altered by PL/SQL Agent to "302 Found"
Goto Forum:
  


Current Time: Sun Dec 11 05:59:08 CST 2016

Total time taken to generate the page: 0.08641 seconds