-
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!
Last edited by sparco; 03-21-2004 at 10:06 PM.
-
Ur Application is the culprit..
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
-
Deadlock
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:
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)
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.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
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.
-
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.
The man called Zorro
-
Originally posted by zainalj
Always commit after update/delete statement especially statement in the loop.
Every update or delete?
-
Originally posted by zainalj
Always commit after update/delete statement especially statement in the loop.
Ha, ha. You're kidding right?
Ever heard of a transaction?
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|