Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

oracle deadlocks on delete statement

WestDraytonSep 9 2010 — edited Sep 9 2010
I had a package procedure that deletes from a inline-view. It worked well and didn't create any database locks, looked like this:
PROCEDURE serverdisconnect(pCode1 NUMERIC, pCode2 NUMERIC) IS
  BEGIN
	DELETE FROM 
	(
	   SELECT cl.* FROM CurrentLogins cl, Accounts a 
	   WHERE cl.Code1 = pCode1 
	   AND cl.Code2 = pCode2 
	   AND cl.Code = a.code 
	   AND a.Type = 'lplayer'
	   ORDER BY a.code
	);
    COMMIT;
  END serverdisconnect;
I slightly changed the procedure to look like following, and deadlocks started to come:
 PROCEDURE ServerDisconnect(pCode1 NUMERIC, pCode2 NUMERIC, pChannelServerCode CurrentLogins.ChannelServerCode%TYPE, pDeleteList OUT cursor_type) 
  IS
    vDeleteList sys.ODCINumberList;
  BEGIN
    DELETE FROM
    (
        SELECT cl.* FROM CurrentLogins cl, Accounts a
        WHERE cl.Code1 = pCode1 
       AND cl.Code2 = pCode2 
       AND cl.Code = a.code 
       AND cl.ChannelServerCode = pChannelServerCode
       AND cl.Code = a.code
       AND a.Type = 'lplayer'
    ) RETURNING Code
      BULK COLLECT INTO vDeleteList;
      
    OPEN pDeleteList FOR
      SELECT * FROM TABLE(vDeleteList);
      
    COMMIT;
  END ServerDisconnect;
As you see the main difference in the delete statement is that i removed "ORDER BY"-clause? Can really such data ordering plays a role with dead locking? Does the data records be always ordered with ORDER-BY-clause same way always to avoid deadlock? Why i started to get deadlock after changing the procedure?

I have Oracle 10g.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 7 2010
Added on Sep 9 2010
3 comments
1,159 views