From Jonathan Lewis’ Oracle Scratchpad blog.

Here’s a deadlock graph that appeared on Oracle-L and OTN a couple of days ago:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-001a0002-0002a0fe       196     197     X            166    1835           S
TM-0000c800-00000000       166    1835    SX            196     197   SX    SSX

It’s a little unusual because instead of the common TX mode 6 (eXclusive) crossover we have one TX and one TM lock, the TX wait is for mode 4 (S) and the TM wait is for a conversion from 3 (SX) to 5 (SSX).

The modes and types give us some clues about what’s going on: TX/4 is typically about indexes involved in referential integrity (though there are a couple of more exotic reasons such as wait for ITLs, Freelists or tablespace status change); conversion of a TM lock from mode 3 to mode 5 is only possible (as far as I know) in the context of missing foreign key indexes when you delete a parent row.

Here’s a simple data set to help demonstrate the type of thing that could have caused this deadlock:

drop table child;
drop table parent;

create table parent (
    id      number(4),
    name        varchar2(10),
    constraint par_pk
         primary key (id)
)
;

create table child(
    id_p number(4),
    id number(4),
    name varchar2(10),
    constraint chi_pk
       primary key (id, id_p),
constraint chi_fk_par
       foreign key(id_p)
       references parent
       on delete cascade
)
;
insert into parent values (1,'Smith');
insert into parent values (2,'Jones');

insert into child values(1, 1, 'Simon');
insert into child values(2, 1, 'Janet');

commit;

Note that I have defined the primary key on the child the “wrong way round”, so that the foreign key doesn’t have a supporting index. Note also that the foreign key constraint is defined as ‘on delete cascade’ – this isn’t a necessity, but it means I won’t have to delete child rows explicitly in my demo.

To find out Jonathan’s next steps, read the full post at: http://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/