SQL*Plus: Release 10.2.0.1.0 - Production on Ćar Eyl 26 09:22:22 2007

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

-- Step A, 3 sessions
--
-- FROM SESSION - 1
SQL> conn hr/hr
Connected.
SQL> create table dept1 (
  2  deptno number constraint pk_dept primary key,
  3  dname varchar2(10) ) ;

Table created.

SQL> create table emp1 (
  2  deptno number(2) constraint fk_deptno references dept1(deptno),
  3  ename varchar2(20) ) ;

Table created.

SQL> insert into dept1 values (1, 'Research') ;

1 row created.

SQL> insert into emp1 values (1, 'Hasan') ;

1 row created.

SQL> select distinct sid from v$mystat ;

       SID
----------
        37

-- FROM SESSION - 2
SQL> conn / as sysdba
Connected.
SQL> set linesize 20000
SQL> @D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utllockt.sql
..

no rows selected

..

SQL> select type, id1, id2, lmode, request, ctime from v$lock where sid = 37;

TY        ID1        ID2      LMODE    REQUEST      CTIME
-- ---------- ---------- ---------- ---------- ----------
TM      14296          0          3          0         12
TM      14298          0          3          0          9
TX     262172        243          6          0         12

-- Step B, 3 sessions
--
-- FROM SESSION - 3
SQL> conn hr/hr
Connected.
SQL> insert into dept1 values (2, 'Developmnt') ;

1 row created.

SQL> commit ;

Commit complete.

SQL> insert into emp1 values (2, 'Tonguc') ;

1 row created.

SQL> select distinct sid from v$mystat ;

       SID
----------
        33
		
-- FROM SESSION - 2
SQL> @D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utllockt.sql
..

no rows selected

..

SQL> select sid, type, id1, id2, lmode, request, ctime from v$lock where sid in(
33, 37) order by sid ;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME
---------- -- ---------- ---------- ---------- ---------- ----------
        33 TM      14296          0          2          0         39
        33 TM      14298          0          3          0         39
        33 TX     393232        245          6          0         39
        37 TX     262172        243          6          0        147
        37 TM      14298          0          3          0        144
        37 TM      14296          0          3          0        147

6 rows selected.

-- Step C, 3 sessions
--
-- FROM SESSION - 1
SQL> update dept1 set deptno = 1 ;

waiting..

-- FROM SESSION - 2
SQL> @D:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN\utllockt.sql
..

WAITING_SESSION   LOCK_TYPE         MODE_REQUESTED MODE_HELD      LOCK_ID1    LOCK_ID2
----------------- ----------------- -------------- -------------- --------    -----------------
33                None
   37             DML               S/Row-X (SSX)  Row-X (SX)     14298    0

..

SQL> select sid, type, id1, id2, lmode, request, ctime from v$lock where sid in(
33, 37) order by sid ;

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME
---------- -- ---------- ---------- ---------- ---------- ----------
        33 TM      14296          0          2          0      22649
        33 TM      14298          0          3          0      22649
        33 TX     393232        245          6          0      22649
        37 TX     262172        243          6          0      22757
        37 TM      14298          0          3          5      22754
        37 TM      14296          0          3          0      22757

6 rows selected.