Forum Stats

  • 3,728,036 Users
  • 2,245,522 Discussions
  • 7,853,257 Comments

Discussions

Ora-00060

Raj012345
Raj012345 Member Posts: 582 Blue Ribbon

Hi All,


We have db 19 on aix.

We get a dead lock error ( ORA-00060 ) in the alert log.

when analyzing trace file, dead lock occurs on two different tables in a schema ( For Ex emp and dep )

1,Usually Deadlock occurs on the same table only .Is my understanding right?

or

Will it occur different tables?

2, When oracle capture dead lock error, will it capture only corresponding sql statements or previous statements also?

Please share your suggestions. i couldn't share the trace files/contents due to company policy.


Thanks

Answers

  • EdStevens
    EdStevens Member Posts: 28,034 Gold Crown
  • EdStevens
    EdStevens Member Posts: 28,034 Gold Crown

    1,Usually Deadlock occurs on the same table only .Is my understanding right?

    or

    Will it occur different tables?

    It usually occurs on different tables.

    Step 1 - Process A acquires a lock on a row on table X.

    Step 2 - Process B acquires a lock on a row on table Y.

    Step 3 - Before continuing, Process A needs a lock on the row that process B has already locked at step 2

    Step 4 - Before continuing, Process B needs a lock on the row that process A locked at step 1

    Both processes need a lock on what the other has already locked. Commonly known as a 'deadly embrace'.

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,192 Bronze Trophy

    Yes, a session can deadlock itself when pragma autonomous transaction is used as shown in the following example

    create table t1 as select rownum n1, mod(rownum,5) b2 from dual connect by level <=100;

    alter table t1 add constraint t1_pk primary key(n1);

    create table t2 (id references t1, vc varchar2(10));

    insert into t2 values (98, 'test');


    SQL> declare

           pragma autonomous_transaction;

           begin

           delete from t1 where n1= 42;

           commit;

    end;

    / 2   3   4   5   6   7

    declare

    *

    ERROR at line 1:

    ORA-00060: deadlock detected while waiting for resource

    ORA-06512: at line 4



    Deadlock graph:

                                             ------------Blocker(s)----------- ------------Waiter(s)------------

    Resource Name                            process session holds waits serial process session holds waits serial

    TM-0001973D-00000000-00000000-00000000        78   1537   SX       44167     78   1537          S 44167

  • JohnWatson2
    JohnWatson2 Member Posts: 4,226 Bronze Crown

    Your problem could be indexing. For example, if you add this to Mohamed's test case,

     create index t2id on t2(id);

    the deadlock will no longer occur.

Sign In or Register to comment.