session1:
SQL> create table test(x number,y number);
Table created
SQL> create unique index idx_x on test(x);
Index created
SQL> insert into test values(1,2);
1 row inserted
--这里不提交
SQL> SELECT DISTINCT sid FROM v$mystat;
SID
----------
94
SQL>
session 2:
SQL> SELECT DISTINCT sid FROM v$mystat;
SID
----------
114
SQL> insert into test values(1,3);
--这里一直等待,说明session 114等待持有锁
--检查阻塞
--session3
SQL> select * from dba_waiters;
WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2
--------------- --------------- -------------------------- ---------------------------------------- ---------------------------------------- ---------- ----------
94 114 Transaction Exclusive Share 393243 396842
SQL>