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.