当两个会话同时插入（主键相同）同一行，产生阻塞等待
会话１上操作
SQL> select sid from v$mystat where rownum=1;
SID
----------
1
SQL> create table tt(id int primary key);
Table created.
SQL> insert into tt values(1);
1 row created.
会话２上操作
SQL> select sid from v$mystat where rownum=1;
SID
----------
49
SQL> insert into tt values(1);
这里被阻塞了
会话３上操作：
SQL> select * from v$lock where sid in(1,49);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
000000007A512E60 000000007A512EB8 49 TX 65548 2336 0 4 5 0
00002AF62EBFF408 00002AF62EBFF468 49 TM 77367 0 3 0 83 0
00002AF62EBFF408 00002AF62EBFF468 1 TM 77367 0 3 0 8 0
0000000078DEA600 0000000078DEA678 49 TX 655373 2357 6 0 5 0
0000000078E67490 0000000078E67508 1 TX 65548 2336 6 0 8 1
这里确实有个请求４号锁:
把ＴＸ上的id1=65548转化成undo
SQL> select trunc(65548/power(2,16)) as undo_blk#,bitand(65548,to_number('ffff','xxxx')) + 0 as slot# from dual;
UNDO_BLK# SLOT#
---------- ----------
查看本栏目更多精彩内容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/
1 12
会话４上操作：
SQL> select * from v$transaction;
ADDR XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS START_TIME START_SCNB START_SCNW START_UEXT START_UBAFIL START_UBABLK START_UBASQN START_UBAREC SES_ADDR FLAG SPA REC NOU PTX NAME PRV_XIDUSN PRV_XIDSLT PRV_XIDSQN PTX_XIDUSN PTX_XIDSLT PTX_XIDSQN DSCN-B DSCN-W USED_UBLK USED_URELOG_IO PHY_IO CR_GET CR_CHANGE START_DAT DSCN_BASE DSCN_WRAP START_SCN DEPENDENT_SCN XID PRV_XID PTX_XID
---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- -------------------- ---------- ---------- ---------- ------------ ------------ ------------ ------------ ---------------- ---------- --- --- --- --- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ------------- ---------------- ---------------- ----------------
0000000078DEA600 10 13 2357 3 2429 229 1 ACTIVE 12/19/12 20:44:58 5631705 0 3 3 2429 229 1 000000007AF97D68 3587 NO NO NO NO 0 0 0 0 0 0 0 0 1 1 18 8 2 19-DEC-12 0 0 5631705 0 0A000D0035090000 0000000000000000 0000000000000000
0000000078E67490 1 12 2336 3 3539 448 43 ACTIVE 12/19/12 20:46:13 5631693 0 2 3 3539 448 42 000000007A85DB50 3587 NO NO NO NO 0 0 0 0 0 0 0 0 1 2 7 1 0 19-DEC-12 0 0 5631693 0 01000C0020090000 0000000000000000 0000000000000000