ORA-000060: Deadlock detected.

I'm having this kind of error on my sql statements.

ORA-000060: Deadlock detected.

Here is the exact error that i got:

DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO P_WORK_ITEM_VERSION(WORK_VERSION_ID,ACTIVITY_ID,PARENT_ACTIVITY_ID,WORK_ITEM_ID,PROCESS_TYPE_CODE,CU RRENT_IND,BOX_TYPE_CODE,VERSION,DELETED_IND,PRIORITY_CODE,STATUS_CODE,EMAIL_SENT_IND,VIEWED_IND,RECI PIENTS,SENDERS,SENDER_USER_ID,SENDER_APP_ID,SENT_DATETIME,BOX_ACTOR_TYPE_CODE,BOX_ACTOR_ID,BOX_APP_I D,OWNER_USER_ID,OWNER_APP_ID,HAS_ATTACHMENTS_IND,HAS_COMMENTS_IND,CREATION_DATETIME,CREATED_BY,LAST_ UPDATE_DATETIME,LAST_UPDATE_BY,ERROR_DISPLAY_MESSAGE,ERROR_EXCEPTION_MESSAGE,ERROR_DATETIME,EXPIRED_ IND,PREVIOUS_EXPIRED_IND,PROCESSED_IND,CASCADE_ITERATION,CASCADE_ASSIGN_DATETIME,REMOTE_IND,PERSONAL _SUBFLOW_CORRELATION,PARENT_VERSION_ID,PREVIOUS_ERROR_IND,EXPIRED_EMAIL_SENT_IND,ACTIVITY_PATH,RECLO CK) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27, :28,:29,:30,:31,:32,:33,:34,:35,:36,:37,:38,:39,:40,:41,:42,:43,:44)
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-000500bc-000003e3 13 20 X 18 19 S
TX-0003009a-000003e8 18 19 X 13 20 S
session 20: DID 0001-000D-00000002 session 19: DID 0001-0012-00000002
session 19: DID 0001-0012-00000002 session 20: DID 0001-000D-00000002
Rows waited on:
Session 19: no row
Session 20: no row

What should I do to resolve this?.. Thanks!

Ur Application is the culprit..

Usualy dead lock are because of unindexes foreign keys.

I have a script to find them and one to create them.

Here's a script to find them:
--------------------------------------------------------
column columns format a30 word_wrapped
column tablename format a15 word_wrapped
column constraint_name format a15 word_wrapped

select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
/

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

Here's a script to create them:

--------------------------------------------------------
create global temporary table temp_index
(commande varchar2(500))
on commit delete rows
/

define Tablespace_name = &&Tablespace_name

set feedback off
set linesize 255
set serveroutput on
set verify off
set heading off;

declare
L_nom_colo varchar2(2000);
--Cette requête retourne les contraintes référencées.
Cursor sel_cons is
Select constraint_name, table_name
from user_constraints
where constraint_type = 'R';
--Cette requête retourne le ou les colonne(s) de la contrainte.
Cursor sel_colo(P_nom_cons user_cons_columns.constraint_name%type,
P_nom_tabl user_cons_columns.table_name%type) is
Select column_name
from user_cons_columns
where constraint_name = P_nom_cons
and table_name = P_nom_tabl
order by position;
--
begin
--
for liste_cons in sel_cons loop
L_nom_colo := null;
for liste_colo in sel_colo(liste_cons.constraint_name, liste_cons.table_name) loop
if L_nom_colo is not null and liste_colo.column_name is not null then
L_nom_colo := L_nom_colo || ',';
end if;
L_nom_colo := L_nom_colo || liste_colo.column_name;
End loop;
--
insert into temp_index values('Create index ' || liste_cons.constraint_name || ' on '
|| liste_cons.table_name || '(' || L_nom_colo || ') tablespace &Tablespace_name;');
end loop;
end;
/

spool &SCRIPT_NAME

select * from temp_index;

spool off

drop table temp_index;
--------------------------------------------------------------

How does my application became the culprit? What must I do/change to prevent that? Thanks! =)

Hi Steeve! thanks for the scripts.. my question is what's the difference between "a script to find them and one to create them" what does "them means"? does it mean one script is to find the error that results to deadlock? and the other one is to create on how th prevent the deadlock?

Please shed me some light on this..thank you very much.

A deadlock means that process A has a lock on resource R1 and is waiting for resource R2 while process B has a lock on resource R2 and is waiting to acquire a lock on resource R1.

In order to resolve it, you must change your code.

To find which resources are involved in the deadlock:

Quote:

---

TX-000500bc-000003e3 13 20 X 18 19 S

---

means session 20 in process 13 has an exclusive lock (x) on resource 995 (decimal value of 000003e3)

Quote:

---

TX-0003009a-000003e8 18 19 X 13 20 S

---

means session 19 in process 18 has an exclusive lock (x) on resource 1000 (decimal value of 000003e8

Simply logon using DBA account and issue:
SELECT owner, object_id, object_type, object_name
FROM dba_objects
WHERE object_id in (995,1000)
/

Cheers.

Hi rotem_fo!

How am i be able to change my code? How am i be able to know which code should i change? and what changes shoul i made to my code? thanks.

Quote:

---

Originally posted by sparco
Hi rotem_fo!

How am i be able to change my code? How am i be able to know which code should i change? and what changes shoul i made to my code? thanks.

---

Always commit after update/delete statement especially statement in the loop.

Quote:

---

Originally posted by zainalj
Always commit after update/delete statement especially statement in the loop.

---

Every update or delete?

Quote:

---

Originally posted by zainalj
Always commit after update/delete statement especially statement in the loop.

---

Ha, ha. You're kidding right? :rolleyes:

Ever heard of a transaction?

Just run the script that find unindexe foreign keys and the problem should be solve

Quote:

---

Originally posted by sparco
Hi rotem_fo!

How am i be able to change my code? How am i be able to know which code should i change? and what changes shoul i made to my code? thanks.

---

Find the piece of code that executes the statment (INSERT INTO P_WORK_ITEM_VERSION...) and try to figure out when and why this statement is performed.

Note that the oracle error stated that It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL.

Maybe issue your own lock on the table prior to issuing DML (not recommended - will cause serialization), maybe use primary key on the table.

Cheers.

Quote:

---

Originally posted by zainalj
Always commit after update/delete statement especially statement in the loop.

---

Whatever you do...DON'T do this. Commit at the last moment possible...ie, when you *need* to, and never inside a loop.

Thanks for all the help guys. Anyways, i have another question... Is there a localized Korean Version of Oracle 9i Release 2? Thanks!

Thanks for all the help.. Am having a very hard time on how to do prevent or solve this deadlock issue, cuz am not a DBA like you guys, im just a technical support solving the problem of our client regarding our application on their database. THANKS!

CHEERS!