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, same row update blocking lock, 4 sessions
--
-- FROM SESSION - 1
SQL> conn hr/hr
Connected.
SQL> create table tx ( num number, txt varchar2(10), sex varchar2(10))
  2  initrans 1 maxtrans 1 ;

Table created.

SQL> insert into tx values (1, 'First', 'FEMALE') ;

1 row created.

SQL> insert into tx values (2, 'Second', 'MALE') ;

1 row created.

SQL> insert into tx values (3, 'Third', 'MALE') ;

1 row created.

SQL> insert into tx values (4, 'Fourth', 'MALE') ;

1 row created.

SQL> insert into tx values (5, 'Fifth', 'MALE') ;

1 row created.

SQL> commit ;

Commit complete.

SQL> alter table tx add constraint pk_tx_num primary key(num) ;

Table altered.

-- FROM SESSION - 2
SQL> conn hr/hr
Connected.
SQL> select distinct sid from v$mystat ;

       SID
----------
        24

SQL> update tx set txt = 'Session 1' where num=1 ;

1 row updated.

-- FROM SESSION - 3
SQL> conn hr/hr
Connected.
SQL> select distinct sid from v$mystat ;

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

SQL> update tx set txt='Session 2' where num=1 ;

..waiting

-- FROM SESSION - 4
SQL> conn / as sysdba
Connected.
SQL> set linesize 20000
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
----------------- ----------------- -------------- -------------- --------    -----------------
24                None
   37             Transaction       Exclusive      Exclusive      458772    234

..

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

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME
---------- -- ---------- ---------- ---------- ---------- ----------
        24 TM      14305          0          3          0         48
        24 TX     393217        245          6          0         48
        37 TM      14305          0          3          0         45
        37 TX     393217        245          0          6         45

-- @ SESSION - first 2 and than 3
SQL> rollback ;
	
-- Step B, same row insert blocking lock, 3 sessions
--
-- FROM SESSION - 1
SQL> conn hr/hr
Connected.
SQL> select distinct sid from v$mystat ;

       SID
----------
        24

SQL> insert into tx values (10, 'Tenth A', 'MALE') ;

1 row created.

-- FROM SESSION - 2
SQL> conn hr/hr
Connected.
SQL> select distinct sid from v$mystat ;

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

SQL> insert into tx values (10, 'Tenth B', NULL) ;
..waiting

-- FROM SESSION - 3
SQL> conn / as sysdba
Connected.
SQL> r
  1  select sid, type, id1, id2, lmode, request, ctime from v$lock
  2* where sid in (24, 37) order by sid

       SID TY        ID1        ID2      LMODE    REQUEST      CTIME
---------- -- ---------- ---------- ---------- ---------- ----------
        24 TM      14305          0          3          0         42
        24 TX     196634        248          6          0         42
        37 TX     589864        238          6          0         15
        37 TX     196634        248          0          4         15
        37 TM      14305          0          3          0         15
		
-- FROM SESSION - 1
SQL> commit ;

Commit complete.

-- FROM SESSION - 2
..message falls to the prompt

insert into tx values (10, 'Tenth B', NULL)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.PK_TX_NUM) violated

SQL> rollback ;

Rollback complete.