[20150304]唯一索引与阻塞.txt

[20150304]唯一索引与阻塞.txt

--昨天帮别人定位一个唯一索引导致出现ora-00001的问题，实际上很简单，程序使用max(id)取得最大号，然后插入，这样的结果在业务
--高峰，出现阻塞或者ora-00001错误。我仅仅简单做一下跟踪很容易定位这个问题。

--换一个角度，使用别的方法是否可行呢，自己做一个例子来验证看看。

1.建立测试环境：

SCOTT@test> create table t as select rownum id,cast ( 'test' as varchar2(10)) name from dual connect by levelTable created.

SCOTT@test> create unique index  i_t_id on t(id);
Index created.

2.测试：
--session 1,插入不提交:
SCOTT@test> insert into t values (11,'a');
1 row created.

--session 2:
SCOTT@test> insert into t values (11,'b');

--出现阻塞情况，如果会话1提交，会报ora-00001错误。

3.分析：

$ cat viewlock.sql
column sid format 99999
column username format a10
column osuser format a10
column machine format a10
column lock_type format a12
column mode_held format a10
column mode_requested format a10
column lock_id1 format a10
column lock_id2 format a10
column owner format a6
column object_type format a10
column object_name format a20
column block format a5
column lockwait format a20

SELECT se.SID, se.serial#,se.username, se.osuser, se.machine,
       DECODE (lk.TYPE, 'TX', 'Transaction', 'TM', 'DML(TM)', 'UL', 'PL/SQL User Lock', lk.TYPE) lock_type,
       DECODE (lk.lmode,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.lmode)
              ) mode_held,
       DECODE (lk.request,
               0, 'None',
               1, 'Null',
               2, 'Row-S (SS)',
               3, 'Row-X (SX)',
               4, 'Share',
               5, 'S/Row-X (SSX)',
               6, 'Exclusive',
               TO_CHAR (lk.request)
              ) mode_requested,
       TO_CHAR (lk.id1) lock_id1, TO_CHAR (lk.id2) lock_id2, ob.owner, ob.object_type, ob.object_name,
       DECODE (lk.BLOCK, 0, 'No', 1, 'Yes', 2, 'Global') BLOCK, se.lockwait
  FROM v$lock lk, dba_objects ob, v$session se
WHERE lk.TYPE IN ('TX','TM', 'UL') AND lk.SID = se.SID AND lk.id1 = ob.object_id(+);

SCOTT@test> @viewlock ;

   SID    SERIAL# USERNAME   OSUSER     MACHINE    LOCK_TYPE    MODE_HELD  MODE_REQUE LOCK_ID1   LOCK_ID2   OWNER  OBJECT_TYP OBJECT_NAME          BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
   401         15 SCOTT      oracle11g  hisdg      DML(TM)      Row-X (SX) None       300095     0          SCOTT  TABLE      T                    No    00000000BDC92778
   597         65 SCOTT      oracle11g  hisdg      DML(TM)      Row-X (SX) None       300095     0          SCOTT  TABLE      T                    No
   597         65 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       1835029    71                                                Yes
   401         15 SCOTT      oracle11g  hisdg      Transaction  None       Share      1835029    71                                                No    00000000BDC92778
   401         15 SCOTT      oracle11g  hisdg      Transaction  Exclusive  None       851975     1740                                              No    00000000BDC92778

--注意看阻塞的LOCK_ID1=1835029,LOCK_ID2=71. 这个LOCK_ID1 前16位表示事务的 XIDUSN，后16位表示XIDSLOT。可以做一个简单计算。

SCOTT@test> select trunc(1835029/power(2,16)),mod(1835029,power(2,16)) from dual;
TRUNC(1835029/POWER(2,16)) MOD(1835029,POWER(2,16))
-------------------------- ------------------------
                        28                       21

SCOTT@test> host cat xid.sql
select dbms_transaction.local_transaction_id()  x from dual ;
select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBAREC, UBASQN,STATUS,used_ublk,USED_UREC,xid,ADDR  from v$transaction;

SCOTT@test> @xid
X
------------------------------

    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBAREC     UBASQN STATUS            USED_UBLK  USED_UREC XID              ADDR
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------------- ----------------
        28         21         71          3      11125         10         83 ACTIVE                    1          2 1C00150047000000 00000000BBF2E4A0
        13          7       1740          3      13079         15        523 ACTIVE                    1          1 0D000700CC060000 00000000B81C13B0

--可以发现正好对上。

SCOTT@test> select sid,serial# from v$session where taddr='00000000BBF2E4A0';
   SID    SERIAL#
------ ----------
   597         65

--这些信息都正好对上。

4.然后做一个转储看看：

SCOTT@test> alter system dump datafile 3 block 11125;
System altered.

..
*-----------------------------
* Rec #0xa  slt: 0x15  objn: 300096(0x00049440)  objd: 300096  tblspc: 4(0x00000004)
*       Layer:  10 (Index)   opc: 22   rci 0x09
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:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x0002.cb8eb5e2
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=42 indexid=0x1000722 block=0x01000723
(kdxlpu): purge leaf row
key :(3):  02 c1 0c

SCOTT@test> select name from sys.undo$ where us#=28;
NAME
---------------------
_SYSSMU28_3223209608$

SCOTT@test> ALTER SYSTEM DUMP UNDO BLOCK '_SYSSMU28_3223209608$' XID 28  21 71;
System altered.

********************************************************************************
Undo Segment:  _SYSSMU28_3223209608$ (28)
xid: 0x001c.015.00000047
Low Blk   :   (0, 0)
High Blk  :   (2, 127)
Object Id :   ALL
Layer     :   ALL
Opcode    :   ALL
Level     :   2

********************************************************************************
UNDO BLK:  Extent: 2   Block: 117   dba (file#, block#): 3,0x00002b75
xid: 0x001c.015.00000047  seq: 0x53  cnt: 0xa   irb: 0xa   icl: 0x0   flg: 0x0000

Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f34     0x02 0x1e8c     0x03 0x1d90     0x04 0x1d28     0x05 0x1cd0
0x06 0x1be4     0x07 0x1b3c     0x08 0x1a5c     0x09 0x19d4     0x0a 0x1978

*-----------------------------
* Rec #0xa  slt: 0x15  objn: 300096(0x00049440)  objd: 300096  tblspc: 4(0x00000004)
*       Layer:  10 (Index)   opc: 22   rci 0x09
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:  0xffff.000.00000000 uba: 0x00000000.0000.00
                      flg: C---    lkc:  0     scn: 0x0002.cb8eb5e2
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=42 indexid=0x1000722 block=0x01000723
(kdxlpu): purge leaf row
key :(3):  02 c1 0c

*-----------------------------
* Rec #0x9  slt: 0x15  objn: 300095(0x0004943f)  objd: 300095  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x00
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c02b75.0053.08 ctl max scn: 0x0002.cb8e8911 prv tx scn: 0x0002.cb8e8914
txn start scn: scn: 0x0002.cb8eb610 logon user: 84
prev brb: 12594031 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Z
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x0100071f  hdba: 0x0100071a
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 0(0x0)

+++++++++++ Next block not in extent map - rollback segment has been shrunk.
+ WARNING + Block dba (file#, block#): 0,0x00000000
+++++++++++

*************************************
Total undo blocks scanned  = 1
Total undo records scanned = 2
Total undo blocks dumped   = 1
Total undo records dumped  = 2

##Total warnings issued = 1
*************************************

--插入索引的objd: 300096，indexid=0x1000722 block=0x01000723

SCOTT@test> select object_name from dba_objects where owner=user and data_object_id=300096;
OBJECT_NAME
--------------------
I_T_ID

SCOTT@test> set verify off
SCOTT@test> @dfb 1000723
    RFILE#     BLOCK#
---------- ----------
         4       1827

TEXT
-----------------------------------------
alter system dump datafile 4 block 1827 ;

SCOTT@test> select * from dba_extents where owner=user and segment_name='I_T_ID';
OWNER  SEGMENT_NAME   SEGMENT_TYPE  TABLESPACE_NAME    EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------ -------------  ------------- ----------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT  I_T_ID         INDEX         USERS                      0          4       1824      65536          8            4

SCOTT@test> select HEADER_FILE,HEADER_BLOCK,BYTES,BLOCKS,EXTENTS,INITIAL_EXTENT,NEXT_EXTENT from dba_segments where owner=user and segment_name='I_T_ID';
HEADER_FILE HEADER_BLOCK      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT
----------- ------------ ---------- ---------- ---------- -------------- -----------
          4         1826      65536          8          1          65536     1048576

--正好是块头的下1块，1827.当然这样定位有点麻烦，了解许多知识点还是很用帮助的。

时间： 2024-08-20 15:31:05

[20150304]唯一索引与阻塞.txt的相关文章

[20160908]唯一索引与非唯一索引.txt

[20160908]唯一索引与非唯一索引.txt --唯一索引与非唯一索引的区别在于rowid信息在索引的位置,唯一索引rowid在row header(数据部分).而非唯一索引在最后. --但是具体的内部结构oracle如何识别呢?做一个简单探究,通过例子来说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ------------------------------ --

[20170427]唯一索引与约束注意的地方.txt

[20170427]唯一索引与约束注意的地方.txt --//昨天看书Apress.Expert.Oracle.Indexing.and.Access.Paths.Maximum.Performance.for.Your.Database.2nd.Edition.148421983X.pdf --//Creating Only a Unique Index P60,提到建立唯一索引与约束需要注意的地方,做一个例子说明: 1.环境: SCOTT@book> @ &r/ver1 PORT_STR

[20140210]主外键和阻塞.txt

[20140210]主外键和阻塞.txt 许多人都知道如果几个表之间存在主外键关系的情况下,许多情况下会出现阻塞情况. 具体的例子还很多,当然如果我觉得最常见如果你不修改主外键值,外键的索引多数情况下可以不建. 而且有些外键的索引建立有点多余的. 今天我看了一篇blog,链接如下: http://blog.yavor.info/?p=564&lang=en 给出的例子很奇特,就是这个问题在11g下会出现阻塞,而10g下不会.12c下我也做了测试,也不会, 自己重复测试做一个记录: 1.12c的情

用ADox获得表的唯一索引

ado|索引 adox的功能据说很强大,但是我没感觉出来,因为我在asp下编写了很多程序,都运行不了.不知道什么原因,这里有一个程序可以获得唯一索引.可能有的朋友会做一些通用程序,也就是无论数据结构如何变化,程序不变,从数据录入,修改,删除,到数据检索.所有模块都是完全独立于数据库表的结构的.这样就需要获得该表的唯一索引.因为要通过唯一索引来标示当前要删改的是那条记录.如果一个表是正规的表的话,应该至少有一个唯一索引,因为表应该有主键,而主键就是唯一索引.我尝试用adox.key获得表的主键,出

SQL Server已分区索引的特殊指导原则（2）- 唯一索引分区

一.前言 在MSDN上看到一篇关于SQL Server 表分区的文档:已分区索引的特殊指导原则,如果你对表分区没有实战经验的话是比较难理解文档里面描述的意思.这里我就里面的一些概念进行讲解,方便大家的交流. 二.解读 [对唯一索引进行分区] "对唯一索引(聚集或非聚集)进行分区时,必须从唯一索引键使用的分区依据列中选择分区依据列.此限制将使 SQL Server 只调查单个分区,以确保表中不存在重复的新键值.如果分区依据列不可能包含在唯一键中,则必须使用 DML 触发器,而不是强制实现唯一性.&

OCP1z0-047 :主键与唯一索引

主键包括非空和唯一约束,它会自动创建唯一索引(注:唯一约束也会自动创建唯一索引),测试如下: 1. 创建一个表products gyj@OCM> Create table products( 2  product_id number(6) constraintprod_id_pk PRIMARY KEY, 3  product_name varchar2(15) 4  ); Table created. 2. 查表products的索引 gyj@OCM> select INDEX_NAME,I

mysql中如何查看和删除唯一索引

  mysql中如何查看和删除唯一索引. 查看唯一索引: show index from mytable;//mytable 是表名 查询结果如下: 查询到唯一索引后,如何删除唯一索引呢,使用如下命令: alter table mytable drop index mdl_tag_use_ix;//mdl_tag_use_ix是上表查出的索引名,key_name

Oracle中唯一约束和唯一索引的区别

在使用TOAD来操作Oracle数据库时,会注意到创建约束时有Primary Key.Check.Unique和Foreign Key四种类型的约束,这与SQL Server中的约束没有什么区别,这里的Check约束除了用于一般的Check约束外,在Oracle中也用于非空约束的实现.也就是说如果一个字段不允许为空,则系统将会创建一个系统的Check约束,该约束定了某字段不能为空. 除了约束,还有另外一个概念是索引,在TOAD中创建索引的界面如下: 我们可以注意到在唯一性组中有三个选项:不唯一.

MySQL 唯一索引和插入重复自动更新

有时我们在往数据库插入数据的时候,需要判断某个字段是否存在,如果存在则执行更新操作,如果不存在则执行插入操作,如果每次首先查询一次判断是否存在,再执行插入或者更新操作,就十分不方便. ON DUPLICATE KEY UPDATE 这个时候可以给这个字段(或者几个字段)建立唯一索引,同时使用以下 sql 语句进行插入或更新操作: INSERT INTO table (id, user_id, token) VALUES (NULL, '2479031', '232') ON DUPLICATE