Deadlock SX -> SSX

from https://jonathanlewis.wordpress.com/2011/08/29/deadlock-2/

Here’s a deadlock graph the 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 define 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.

Now we take the following steps:

Session 1: delete from parent where id = 1;

This will delete the child row – temporarily taking a mode 4 (S) lock on the child table – then delete the parent row. Both tables will end up locked in mode 3.

Session 2: insert into child values (1,2,'Sally');

This will lock the parent table in mode 2, lock the child table in mode 3, then wait with a TX mode 4 for session 1 to commit or rollback. If session 1 commits it will raise Oracle error: “ORA-02291: integrity constraint (TEST_USER.CHI_FK_PAR) violated – parent key not found”; if session 1 rolls back the insert will succeed.

Session 1: delete from parent where id = 2;

This will attempt to lock the child table in mode 4, find that there it already has the child locked in mode three (which is incompatible with mode 4) and therefore attempt to convert to mode 5 (SSX). This will make it queue, waiting for session 2 to commit.
Three seconds later session 2 (the first to start waiting) will timeout and report a deadlock with the follow deadlock graph:

Deadlock graph:

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

Resource Name          process session holds waits  process session holds waits

TM-00015818-00000000        14     371    SX             17     368    SX   SSX

TX-0009000e-000054ae        17     368     X             14     371           S

session 371: DID 0001-000E-00000018 session 368: DID 0001-0011-00000005

session 368: DID 0001-0011-00000005 session 371: DID 0001-000E-00000018

Rows waited on:

Session 368: no row

Session 371: no row

Session 368:

  pid=17 serial=66 audsid=2251285 user: 52/TEST_USER

  O/S info: user: HP-LAPTOPV1\jonathan, term: HP-LAPTOPV1, ospid: 2300:3528, machine: WORKGROUP_JL\HP-LAPTOPV1

            program: sqlplus.exe

  application name: SQL*Plus, hash value=3669949024

  Current SQL Statement:

  delete from parent where id = 2

End of information on OTHER waiting sessions.

Current SQL statement for this session:

insert into child values(1,2,'new')

You’ll notice that there are no rows waited for – session 1 isn’t waiting for a row it’s waiting for a table and session 2 isn’t waiting for a table row it’s waiting for an index entry.

Footnote: There are several variations on the theme of one session inserting child rows when the other session has deleted (or inserted) the parent. The uncommitted parent change is an easy cause of the TX/4; the delete with unindexed foreign key is a necessary cause of the SX -> SSX.