当两个会话同时插入(主键相同)同一行,产生阻塞等待 会话1上操作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); 这里被阻塞了 会话3上操作: 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 这里确实有个请求4号锁: 把TX上的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# ---------- ---------- 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 有两个事务:找到undo段号为1的事物 会话5上操作: 对3号文件的第3539号undo块做dump *-----------------------------* Rec #0x2b slt: 0x0c objn: 77368(0x00012e38) objd: 77368 tblspc: 7(0x00000007) * Layer: 10 (Index) opc: 22 rci 0x2a Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *----------------------------- index undo for leaf key operations KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0002.01f.000009dd uba: 0x00c00457.013d.18 flg: C--- lkc: 0 scn: 0x0000.0055eecd Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1800172 block=0x01800173 (kdxlpu): purge leaf row key :(3): 02 c1 02 看出objn: 77368(0x00012e38) SQL> select object_name,object_type from dba_objects where object_id=77368; OBJECT_NAME OBJECT_TYPE --------------- ------------------- SYS_C0011441 INDEX SQL> select index_name from dba_indexes where table_name='TT'; INDEX_NAME ------------------------------ SYS_C0011441 可以看出请求的4号锁是索引上的,索引的根到枝叶是共享锁。
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!********** QQ: 252803295 Email: oracledba_cn@hotmail.com Blog: http://blog.csdn.net/guoyJoe ITPUB: http://www.itpub.net/space-uid-28460966.html OCM: http://education.oracle.com/education/otn/YGuo.HTM 答案在:http://blog.csdn.net/guoyjoe/article/details/8624392 Oracle@Paradise 总群:127149411 Oracle@Paradise No.1群:177089463(已满) Oracle@Paradise No.2群:121341761 Oracle@Paradise No.3群:140856036 |
转载于:https://blog.51cto.com/guoyjoe/1429079