DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: ORA-000060: Deadlock detected.

Hybrid View

  1. #1
    Join Date
    Feb 2004
    Location
    Philippines
    Posts
    9

    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.

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Nov 2000
    Posts
    440
    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;
    --------------------------------------------------------------

  4. #4
    Join Date
    Feb 2004
    Location
    Philippines
    Posts
    9
    How does my application became the culprit? What must I do/change to prevent that? Thanks! =)

  5. #5
    Join Date
    Feb 2004
    Location
    Philippines
    Posts
    9
    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.

  6. #6
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268

    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

  7. #7
    Join Date
    Feb 2004
    Location
    Philippines
    Posts
    9
    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.

  8. #8
    Join Date
    Nov 2001
    Location
    Planet Earth
    Posts
    116
    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

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by zainalj
    Always commit after update/delete statement especially statement in the loop.
    Every update or delete?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
  •  


Click Here to Expand Forum to Full Width