Demonstrate Jonathan Lewis's RI deadlock.

Demonstrate Jonathan Lewis's RI deadlock.

https://jonathanlewis.wordpress.com/2016/05/09/ri-locks/

----- Session 1 -----
[oracle@stormking cdb12102 deadlock]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:21:04 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> @ conn.u.sql
Connected.
SQL> set echo on
SQL> @ sidprompt.sql
SQL> column sid_ new_value sid_ format a5
SQL> select sys_context('userenv','sid' ) sid_ from dual;

SID_
-----
81

SQL> set sqlprompt '&&sid_> '
81>
81> @ create.tables.sql
81>
81> create table parent (
  2          id        number(8,0),
  3          constraint par_pk primary key(id)
  4  );

Table created.

81>
81> create table child  (
  2          id_p      number(8,0) not null references parent,
  3          id_c      number(8,0) not null,
  4          constraint child_pk primary key(id_p, id_c)
  5  )
  6  ;

Table created.

81>
81> @ insert.parent.1.sql
81> -- (1) session 1
81> insert into parent values(1);

1 row created.

----- Session 2 -----
[oracle@stormking cdb12102 deadlock]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:22:29 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> @ conn.u.sql
Connected.
SQL> set echo on
SQL> @ sidprompt.sql
SQL> column sid_ new_value sid_ format a5
SQL> select sys_context('userenv','sid' ) sid_ from dual;

SID_
-----
34

SQL> set sqlprompt '&&sid_> '
34>
34> @ insert.parent.2.sql
34> -- (2) session 2
34> insert into parent values(2);

1 row created.



----- Session 1 -----
81> @ insert.child.2.2.sql
81> -- (3) session 1
81> insert into child values(2,2);

----- Session 2 -----

34> @ insert.child.1.1.sql
34> -- (4)session 2
34> insert into child values(1,1);


----- Session 1 -----
insert into child values(2,2)
            *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


Trace file /u01/app/oracle/diag/rdbms/cdb12102/cdb12102/trace/cdb12102_ora_478.trc
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1
System name:    Linux
Node name:      stormking
Release:        2.6.39-400.17.1.el6uek.x86_64
Version:        #1 SMP Fri Feb 22 18:16:18 PST 2013
Machine:        x86_64
Instance name: cdb12102
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 478, image: oracle@stormking


*** 2016-05-10 23:24:23.568
*** SESSION ID:(81.58350) 2016-05-10 23:24:23.568
*** CLIENT ID:() 2016-05-10 23:24:23.568
*** SERVICE NAME:(pdbm) 2016-05-10 23:24:23.568
*** MODULE NAME:(SQL*Plus) 2016-05-10 23:24:23.568
*** CLIENT DRIVER:(SQL*PLUS) 2016-05-10 23:24:23.568
*** ACTION NAME:() 2016-05-10 23:24:23.568
*** CONTAINER ID:(4) 2016-05-10 23:24:23.568



*** 2016-05-10 23:24:23.568

*** 2016-05-10 23:24:23.611
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors
[Transaction Deadlock]

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-00070000-0000569D-00000000-00000000         24      81     X             29      34           S
TX-00050006-00005882-00000000-00000000         29      34     X             24      81           S

session 81: DID 0001-0018-0000D951      session 34: DID 0001-001D-000077DF
session 34: DID 0001-001D-000077DF      session 81: DID 0001-0018-0000D951

Rows waited on:
  Session 81: no row
  Session 34: no row

----- Information for the OTHER waiting sessions -----
Session 34:
  sid: 34 ser: 19294 audsid: 14014101 user: 104/U
  pdb: 4/PDBM
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x40009) -/-/INC
  pid: 29 O/S info: user: grid, term: UNKNOWN, ospid: 495
    image: oracle@stormking
  client details:
    O/S info: user: oracle, term: pts/3, ospid: 493
    machine: stormking program: sqlplus@stormking (TNS V1-V3)
    application name: SQL*Plus, hash value=3669949024
  current SQL:
  insert into child values(1,1)

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=1vk0n5d4z5rya) -----
insert into child values(2,2)