Demonstrate Jonathan Lewis's RI lock

Demonstrate Jonathan Lewis's RI lock.

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:01:11 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_
-----
64

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

Table created.

64>
64> 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.

64>
64> @ insert.parent.sql
64> -- session 1
64> 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:02:10 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.child.sql
34> -- session 2
34> insert into child values(1,1);

----- Session 1 -----

64> @ vlock.sql
64> select      sid, type, id1, id2, lmode, request, ctime, block
  2    from       V$lock
  3    where   sid in (select sid from V$session where username = 'U')
  4    and        type != 'AE'
  5    order by
  6          sid, type desc
  7  /

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
        34 TX     262156      21994          0          4         25          0
        34 TX     196630      23351          6          0         25          0
        34 TM      96098          0          3          0         25          0
        34 TM      96096          0          3          0         25          0
        64 TX     262156      21994          6          0         75          1
        64 TM      96096          0          3          0         75          0
        64 TM      96098          0          2          0         75          0

7 rows selected.

64> rollback;

Rollback complete.

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

insert into child values(1,1)
*
ERROR at line 1:
ORA-02291: integrity constraint (U.SYS_C0011029) violated - parent key not
found