唯一索引上产生4号锁

来源：互联网发布：淘宝退货填写快递单号编辑：程序博客网时间：2024/05/21 10:02

当两个会话同时插入（主键相同）同一行，产生阻塞等待

会话１上操作
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#
---------- ----------

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段号为１的事物

会话５上操作：

对３号文件的第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

可以看出请求的４号锁是索引上的，索引的根到枝叶是共享锁。

**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe

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
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!

答案在：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